Creating a chart with dynamic labels

Excel 2016 2013
This tip shows how to create dynamically updated chart labels that depend on the value of the cells.
The Chart with labels Excel 365

The trick of this chart is to show data from specific cells in the chart labels. For example, if you have to show in one chart two different data bar:

  • To compare two different teams, you should create a chart using percent of task completion (in this example, cells C14:D14).
  • But it will be more informative to see the real volume for every team (in this example, cells C12:D12). Or the simple formulas:

    Team A: = CONCATENATE (IF (C14 > D14, "Winner - ", "Loooser - "), TEXT (C12, "#0"))

    Team B: = CONCATENATE (IF (C14 < D14, "Winner - ", "Loooser - "), TEXT (D12, "#0"))

Data for Chart with labels Excel 365

For the existing chart, do the following:

   1.   Right-click on the chart and in the popup menu, select Add Data Labels and again Add Data Labels:

Add Data Label in Excel 365

   2.   Do one of the following:

  • For all labels:
    • Right-click on any data label and select Format Data Labels... in the popup menu:
      Format Data Label in popup menu Excel 365
    • On the Format Data Labels pane, on the Label Options section, in the Label Contains group, check the Value From Cells option and then choose cells in the Data Label Range dialog box:
      Value from Cells for Label in Excel 365
  • For the specific label: double-click on the label value, then select Choose Cell in the popup menu, and specify the value in the Data Label Reference dialog box:
    Choose cell for Label in Excel 365

Make any other adjustments to get the look you desire:

The Chart with labels Excel 365

See also this tip in French: Créer un graphique avec des étiquettes dynamiques.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.