Author: Oscar Cronquist Article last updated on January 21, 2020

custom data labels in a chart1

You can easily change data labels in a chart. Select a single data label and enter a reference to a cell in the formula bar. You can also edit data labels, one by one, on the chart.

With many data labels, the task becomes quickly boring and time-consuming. But wait, there is a third option using a duplicate series on a secondary axis.

The animated image above shows you dynamic custom data labels. Here is how you build them.

Note! Before you continue reading. If you own Excel 2013 or a later version you don't have to do the work-around presented below this yellow box.

  1. Right-click on any data series displayed in the chart.
  2. Click on "Add Data Labels".
  3. Click on Add Data Labels".
    Add data labels
  4. Double click on any data label to expand the "Format Data Series" pane.
  5. Enable checkbox "Value from cells".
    Add data labels1
    A small dialog box prompts for a cell range containing the values you want to use a s data labels.
  6. Select the cell range and click OK button.

The chart shows the values you selected as data labels.

Create a chart

  1. Select a cell range
  2. Go to "Insert" tab
  3. Click "Column" button
  4. Select the first 2-D Column chart

custom data labels13

Add another series to the chart

  1. Right click on chart
  2. Click Select data
    custom data labels2
  3. Click "Add" button
  4. Select a series name, cell C2
  5. Select series values, C3:C8
    custom data labels14
  6. Click Ok
  7. Click Ok

Plot series on the secondary axis

  1. Click the second series on the chart
  2. Right click on a "second series" column
    custom data labels15
  3. Click "Format Data Series..."
    custom data labels5
  4. Select "Secondary Axis"
  5. Click Close

The following article shows you another trick using the secondary axis in a way it wasn't intended to do:

Highlight a bar in a chart

This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a [โ€ฆ]

Highlight a bar in a chart

Change the second series data source

  1. Right click on the chart
  2. Click "Select Data"
    custom data labels7
  3. Select the second series
    custom data labels8
  4. Click "Edit" button (Horizontal (Category) Axis Labels)
  5. Select cell range D3:D8
  6. Click OK
  7. Click OK

Add data labels

  1. Right click on a column
    custom data labels9
  2. Click "Add Data Labels"
  3. Double click a data label
    custom data labels10
  4. Deselect Value
  5. Select Category name
  6. Click Close

custom data labels16

Download Excel file


Custom-data-labels-in-a-chartv3.xlsx

Related articles