Excel Logo

Make your chart labels in Microsoft Excel dynamic by linking them to cell values. When the data changes, the chart labels automatically update. In this article, we explore how to make both your chart title and the chart data labels dynamic.

We have the sample data below with product sales and the difference in last month’s sales.

Sample data of product sales

We want to chart the sales values and use the change values for data labels.

Use Cell Values for Chart Data Labels

Select range A1:B6 and click Insert > Insert Column or Bar Chart > Clustered Column.

Insert Column Chart in Excel

The column chart will appear. We want to add data labels to show the change in value for each product compared to last month.

Column chart in Excel

Select the chart, choose the “Chart Elements” option, click the “Data Labels” arrow, and then “More Options.”

Add data labels to a chart

Uncheck the “Value” box and check the “Value From Cells” box.

Show data label values from cells

Select cells C2:C6 to use for the data label range and then click the “OK” button.

Select the cell range to show in data labels

The values from these cells are now used for the chart data labels. If these cell values change, then the chart labels will automatically update.

Cell values used for data labels

Link a Chart Title to a Cell Value

In addition to the data labels, we want to link the chart title to a cell value to get something more creative and dynamic. We will begin by creating a useful chart title in a cell. We want to show the total sales in the chart title.

In cell E2, enter the following formula:

="Monthly Sales Total - "&TEXT(SUM(B2:B6),"0,###")

This formula creates a useful title that combines the text “Monthly Sales Total – ” to the sum of values B2:B6.

The TEXT function is used to format the number with a thousand separator.

Create a useful chart title

We now need to link the chart title to cell E2 to use this text we’ve created.

Click the chart title, enter = into the Formula Bar, and then click cell E2. From there, press the Enter key.

Link the chart title to a cell value

The value from cell E2 is used for the chart title.

A creative chart title

If the values in the data range were to change, our data labels and chart title would update to reflect that on the chart.

Using creative and dynamic labels for your charts, by basing them on cell values, will take your charts beyond the standard charts others create in Excel.

Alan Murray Alan Murray
Alan Murray has worked as an Excel trainer and consultant for twenty years. On most days, he can be found teaching Excel in a classroom or seminar. Alan gets a buzz from helping people improve their productivity and working lives with Excel.
Read Full Bio »

The above article may contain affiliate links, which help support How-To Geek.