Format Data Labels dialog

If you format the data series of a chart to enable labels, you can double-click a label to display the Format Data Labels dialog. Or, right-click a label and select Format Data Labels.

The Format Data Labels dialog has the Pattern tab and Font, Number and Alignment tabs.

The options of the Font and Number tabs are the same as in the Format Cells dialog.

But the options of the Alignment tab are different.

You can specify orientation of the label text and its horizontal and vertical alignment within a data series, relative to its edges.

You can also specify the position of labels. The options depend on the type of chart. For example, these are the options for a column chart:

  • Outside end: The label is displayed outside the area occupied by the data series. For example, above the top of a column in a Column chart.
  • Inside end: The label is displayed within the data series, near its end. For example, near the top of a column in a Column chart.
  • Inside base: The label is displayed within the data series, near its base. Fore example, near the bottom of a column in a Column chart.
  • Center: The label is displayed within the data series, at its mid point.

If data labels are long they can overlap with other labels. To minimize this, use the Trim long labels and Wrap long labels options. If you select both options, the sizes of labels are limited horizontally and vertically and depend on the type of chart and the sizes of the data series. For example, the size limits for pie charts are different from the limits for bar charts.

Wrapping breaks labels at the end of lines, between words or after a hyphen. Numbers can be wrapped, but not after a minus sign.

Text and numeric labels may be wrapped.

Trimming replaces part of a label with an ellipsis (...). If a label contains both a category name and a value, the category name is trimmed first, so that the full value remains visible. But, if the value is long and wrapping is off, both category name and value might be trimmed.

Cell formats that are applied to chart data are not automatically applied to chart axes or labels. For example, the X axis of a column chart displays unformatted values even if the source values are formatted as dates. To apply the format of the source values to the axis, select User-defined on the Number tab and select ~ as the format.
Note: If the format of the source values include color, such as _$#,##0;[Red]-_$#,##0s, the color formatting is not applied to axes and labels.