Quick Tip: Excel 2013 offers flexible data labels

Data labels can display more than values now. Use them to display a message or even the result of a formula not in the chart's underlying data set.

Excel 2013 offers more formatting and shape options for displaying data labels in a chart. It also supports a dynamic data label option. We'll explore those options and then put them to use. 

How to add data labels

First, you'll need some data labels. You can work with most any simple chart. (We'll work through a simple example with an emphasis on the features rather than sophisticated results.) To add data labels to an existing chart, select the chart. Then, click the Chart Elements icon (the cross icon). In the resulting dialog, check Data Labels. That's it!

The data labels aren't particularly effective in this chart. In fact, they are intrusive and messy. For the purposes of this example, we want to keep only one. That means we'll need to delete a lot of data labels. To delete them for a series, select one and press Delete. Selecting one selects all in the same series. Go ahead and delete all of the data labels for Smith, Jones, and Hancock.

That leaves Michaels, and we'll keep only the Michaels data label for the West region. Slowly click each label that you want to delete twice (don't use a quick double-click) and then press Delete. The first click selects the series. The second click deletes all but the selected label from the selection. You must delete them individually; you can't create a multi-object selection.

The lone data label should be effective just by virtue of being the only one. Obviously, that value has a story to tell or perhaps you have a question to pose. But right now, it's almost invisible. Let's move it so readers can see it. To move the label, select it and drag it - that part's easy. You can also format the value itself using the options in the Font group. I increased the font size and changed the color to make the value stand out among the chart's other elements.

The value by itself will often be enough. This one could use a bit of clarification, so let's add some descriptive text. Position the cursor inside the data label either before or after the actual label. Then, simply type the text you want to display with the value and press Enter. You might want to tweak the results a bit by forcing the text to wrap, centering the text, and so on. Although adding text can feel a bit awkward at first, you'll catch on quickly. Remember, you can press [Ctrl]+z to undo changes.

That's effective, as is, but it isn't new - you've always been able to do that. Now, let's suppose you want to drive your point home by listing the dismally low commission on Michaels West by displaying the commission instead of the actual value. To return a high and low commission value, enter the following formulas in the sheet: 

=MAX(B3:E6)*0.001
=MIN(B3:E6)*0.001

Now you're ready to display the results of those formulas in the appropriate data label. Delete (or not, it doesn't matter) the contents of the Michaels West data label. With the cursor inside that data label, right-click and choose Insert Data Label Field. In the next dialog, select [Cell] Choose Cell. When Excel displays the source dialog, click the cell that contains the MIN() function, and click OK. The data label now displays the results of that formula.

Below, you can see where I've added a data label field to also display the maximum commission above the appropriate column. To quickly select a single column use the slow two-clicks selection method described above.

The labels are fine, but 2013 has one more effect you might want to know about. You can use shapes to draw attention to labels - Excel calls them callouts. Right-click the label, choose Change Data Label Shapes, and then choose a shape. That's all there is to it!

The data label fields will update as you change values in the underlying sheet. However, they'll still be attached to the same column. In other words, if the low value for Michaels West is a typo and you correct it, both formulas might return a different result. The data labels will remain in their relative positions to their columns, but they will update to display the new results, which in this case, might not reflect the right columns.