Creating a chart with dynamic labels

Excel 2016
Often actualizing the graph data takes a long time. Suppose you need to present frequently changing, updating, or extending data. In that case, it's easier to create automatically updated charts, such as automatically extended charts, charts with dynamically updated chart titles, or 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:

The Chart with labels 2 Excel 365

For example, if you have to show in one chart two different data bars:

  • To compare two different teams, you should create a chart using the 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 (for example, see how to create a simple competition chart), do the following:


Add the data series labels

   1.   Select the data series, then do one of the following:

  • Click on the Chart Elements button, select the Data Labels list, then select the position of the labels (the options depend on the data series chart type):
    Chart elements - Data Labels in Excel 365
  • On the Chart Design tab, in the Chart Layouts group, click the Add Chart Element button:
    Add Chart Element button in Excel 365

    From the Add Chart Element dropdown list, choose Data Labels, then select the place for the labels (the options depend on the data series chart type):

    Add Chart Element - Data Labels in Excel 365
  • Right-click on the data series and select Add Data Labels -> Add Data Labels in the popup menu:
    Add Data Label in Excel 365

Add the labels values from the cells

   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. See more examples of 5 Excel features to create data-dependent visualizations.

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

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.