Add or remove data labels in a chart

To quickly identify a data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) in a chart, you can add data labels (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell.) to the data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) of the chart. By default, the data labels are linked to values on the worksheet, and they update automatically when changes are made to these values.

Formatted pie chart

This pie chart displays percentages in data labels

You can also display series names, category names, or custom text in data labels. For bubble charts, you can even display the size of the bubbles. For optimal detail, you can display more than one data label entry in each data label, separated by a comma or other separator that you specify.

To prevent data labels from overlapping and to make them easier to read, you can adjust their positions in the chart. And if you no longer want to display data labels, you can remove them.

What do you want to do?


Add data labels to a chart

  1. On a chart, do one of the following:
    • To add a data label to all data points of all data series, click the chart area (chart area: The entire chart and all its elements.).
    • To add a data label to all data points of a data series, click one time to select the data series that you want to label.
    • To add a data label to a single data point in a data series, click the data series that contains the data point that you want to label, and then click the data point again.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

  1. On the Layout tab, in the Labels group, click Data Labels, and then click the display option that you want.

Excel Ribbon Image

 Note   Depending on the chart type that you used, different data label options will be available.

Top of Page Top of Page

Change the data label entries that are displayed

By default, label entries show values when you add data labels. You can change this predefined label entry to display series names, category names, or a combination of predefined data label entries (series names, category names, and values). For a custom label entry, you can change the data label entries manually.

Apply a different predefined label entry

  1. On a chart, do one of the following:
    • To display additional label entries for all data points of a series, click a data label one time to select all data labels of the data series.
    • To display additional label entries for a single data point, click the data label in the data point that you want to change, and then click the data label again.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

  1. On the Format tab, in the Current Selection group, click Format Selection.

Excel Ribbon Image

 Tip   You can also right-click the selected label or labels on the chart, and then click Format Data Label or Format Data Labels.

  1. Click Label Options if it's not selected, and then under Label Contains, select the check box for the label entries that you want to add.

 Tip   The label options that are available depend on the chart type of your chart. For example, in a pie chart, data labels can contain percentages and leader lines.

  1. To change the separator between the data label entries, select the separator that you want to use or type a custom separator in the Separator box.
  2. To adjust the label position to better present the additional text, select the option that you want under Label Position.

 Tip   If you have entered custom label text but want to display the data label entries that are linked to worksheet values again, you can click Reset Label Text.

Top of Page Top of Page

Create a custom label entry

  1. On a chart, click the data label in the data point that you want to change, and then click the data label again to select just that label.
  2. Click inside the data label box to start edit mode.
  3. Do one of the following:
    • To enter new text, drag to select the text that you want to change, and then type the text that you want.
    • To link a data label to text or values on the worksheet, drag to select the text that you want to change, and then do the following:
  1. On the worksheet, click in the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.), and then type an equal sign (=).
  2. Select the worksheet cell that contains the data or text that you want to display in your chart.

 Tip   You can also type the reference to the worksheet cell in the formula bar. Include an equal sign, the sheet name, followed by an exclamation point; for example, =Sheet1!F2

  1. Press ENTER.

 Tip   You can use either method to enter percentages — manually if you know what they are, or by linking to percentages on the worksheet. Percentages are not calculated in the chart, but you can calculate percentages on the worksheet by using the equation amount / total = percentage. For example, if you calculate 10 / 100 = 0.1, and then format 0.1 as a percentage, the number will be correctly displayed as 10%. For more information about how to calculate percentages, see Calculate percentages.

 Note   The size of the data label box adjusts to the size of the text. You cannot resize the data label box, and the text may become truncated if it does not fit in the maximum size. To accommodate more text, you may want to use a text box instead. For more information, see Add a text box to a chart.

Top of Page Top of Page

Change the position of data labels

You can change the position of a single data label by dragging it. You can also place data labels in a standard position relative to their data markers. Depending on the chart type, you can choose from a variety of positioning options.

  1. On a chart, do one of the following:
    • To reposition all data labels for a whole data series, click a data label one time to select the data series.
    • To reposition a specific data label, click that data label two times to select it.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

  1. On the Layout tab, in the Labels group, click Data Labels, and then click the option that you want.

Excel Ribbon Image

 Tip   For additional data label options, click More Data Label Options, click Label Options if it's not selected, and then select the options that you want.

Top of Page Top of Page

Remove data labels from a chart

  1. Click the chart from which you want to remove data labels.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

  1. Do one of the following:
  • On the Layout tab, in the Labels group, click Data Labels, and then click None.

Excel Ribbon Image

  • Click a data label one time to select all data labels in a data series or two times to select just one data label that you want to delete, and then press DELETE.
  • Right-click a data label, and then click Delete.

 Note   This removes all data labels from a data series.

  1.  Tip   You can also remove data labels immediately after you add them by clicking Undo Button image on the Quick Access Toolbar, or by pressing CTRL+Z.

Top of Page Top of Page

 
 
Applies to:
Excel 2007, Outlook 2007, PowerPoint 2007, Word 2007