Microsoft Office Tutorials and References
In Depth Information
Problems and limitations with data labels
A Data Label Trick
One way to get around Excel’s data label limitation is to use a custom number format
for each of your x values. The trick is to use a number format that consists only of
text. The cell will still be treated as a number, but only the text will be displayed. Also,
the formatted value is what appears in the chart data label!
The accompanying figure shows a chart that uses the data in columns C and D.
Column C contains actual values, but the cells are formatted to display as text. Each
value uses a (different) custom number format. For example, the value in cell C2 uses
this custom number format: “Abigail.” The x values are duplicated in column B but
formatted as numbers. The data labels in the chart use the X Value option, and the
custom formats (not the actual values) are displayed. These custom labels also display
along the x axis, so you’ll need to change the number formatting for the axis.
As you work with data labels, you’ll find that this feature works best for series
that contain a relatively small number of data points. The chart in Figure 4-37, for
example, contains 24 data points. There is really no way to display data labels on
this chart and keep the chart legible.
One option is to delete some of the individual data labels. For example, you
might want to delete all the data labels except those at the high and low points of
the series. Deleting only certain data labels is, however, a manual process. To delete
an individual data label, select it and press Del. Yet another alternative is to use a
data table, which is described in the next section.