How to Customize Your Excel Pivot Chart Data Labels

By Stephen L. Nelson, E. C. Nelson

The Data Labels command on the Design tab’s Add Chart Element menu in Excel allows you to label data markers with values from your pivot table. When you click the command button, Excel displays a menu with commands corresponding to locations for the data labels: None, Center, Left, Right, Above, and Below.

None signifies that no data labels should be added to the chart and Show signifies heck yes, add data labels. The menu also displays a More Data Label Options command. To add data labels, just select the command that corresponds to the location you want. To remove the labels, select the None command.

image0.jpg

If you want to specify what Excel should use for the data label, choose the More Data Labels Options command from the Data Labels menu. Excel displays the Format Data Labels pane. Check the box that corresponds to the bit of pivot table or Excel table information that you want to use as the label.

For example, if you want to label data markers with a pivot table chart using data series names, select the Series Name check box. If you want to label data markers with a category name, select the Category Name check box. To label the data markers with the underlying value, select the Value check box.

In Excel 2007 and Excel 2010, the Data Labels command appears on the Layout tab. Also, the More Data Labels Options command displays a dialog box rather than a pane.

image1.jpg

Different chart types supply different data label options. Your best bet, therefore, is to experiment with data labels by selecting and deselecting the check boxes in the Label Contains area of the Data Labels tab.

Note: The Label Options tab also provides a Separator drop-down list box, from which you can select the character or symbol (a space, comma, colon, and so on) that you want Excel to use to separate data labeling information.

Selecting the Legend Key check box tells Excel to display a small legend key next to data markers to visually connect the data marker to the legend. This sounds complicated, but it’s not. Just select the check box to see what it does. (You have to select one of the Label Contains check boxes before this check box is active.)