Excel Charts with Dynamic Title and Legend Labels (with Steps)

Dynamism is everywhere! Then, why not with Microsoft Excel? Creating dynamic charts in Excel is possible in many ways. What I am going to cover in this article is how you can create Excel charts with dynamic title and legend labels.

See the dynamic image below. Like it? If the image was static, would you like it the same way?

Observe the above image carefully!

When I change the values of cells C5 and F5, the chart shows different titles and legends. In this article, I will show you how to create Excel charts with dynamic title and legend labels.

Download Practice Workbook

To follow along with me in the entire article, download the working file first.

Step-by-Step Procedures to Create Charts with Dynamic Title and Legend Labels in Excel

The worksheet shows the GDP forecasts for the US and China. You will see this type of forecasting in practical life.

• GDP for the US is set at 500 (see cell C5) with a growth rate of 3% (cell D5).
• GDP for China is set at 250 (see cell F5) with a growth rate of 10% (cell G5).

This forecast wants to know what the scenario will be if the above GDP and growth rate continues for the next 15 years. that we want to see on a chart.
By using the following data set, I will first create a chart using a step-by-step procedure. Then, I will show you how to add dynamic title and legend labels to the chart.

Step 1: Preparing Data Set

In the first step, I will modify the data set a little more and add some more rows for calculation. To do that,

• First of all, create four more data tables in rows 22-26 respectively, under the sample data set.
• Then, give appropriate headers to those new data tables, like in the following image.
• From cell B5 to B20, values from 0 to 15 are set. You can think of these values as years. 0 is the starting point (year), and 1 is regarded as after one year.

Step 2: Calculating GDP and Growth (%) of US for 15 Years

Secondly, I will show the calculation for the GDP and Growth (%) of the US in columns C and D. For that,

• Check out cell C6. It holds this formula.

So it just copies the value of cell C5 and it is 3%.

• Then, I just copied this formula for other cells to the bottom (up to cell C20) using AutoFill. So all the cells are holding the value of 3% in them.

• Thirdly, check out the formula in cell D6. It is:

• The values of D5 and C6 are 500 and 3% Consequently, the result is 515.
• Lastly, using the Fill Handle, I have copied this formula for other cells on the bottom (up to cell D20).

Step 3: Measuring GDP and Growth (%) of China for 15 Years

In the third step, I will show the measurement of the GDP and Growth (%) of China. To measure that,

• Firstly, in cell F6, type the following formula.

• Secondly, use the AutoFill feature to drag the formula to the lower cells of the column.

• Thirdly, measure the GDP of China for the first year in cell G6 by using the following formula.

• Lastly, drag the formula to the lower cells of the column using the Fill Handle to see all the values.

Step 4: Comparing GDP of Both Countries

In this step, I will show you the comparison of GDPs between these two countries. For that do the followings,

• First of all, write the following formula in cell I6.
• If the value of cell G6 is greater than or equal to the value of cell D6, it will return 1, otherwise 0.

• Then, I copied this formula for other cells to the bottom (up to cell I20) using AutoFill.

• Thirdly, check the following formula in the cell, F26.

• Here, look at the MATCH function It searches for the value 1 in cell range I6:I20, and, if it finds any, returns the position of 1 in the range. If it does not find any, it returns an error. The IFERROR function catches that error and returns “none.”

• Finally, the first time, 1 is returned to cell I16.
• Here, the value of B16 is 11. So after 11 years, China’s GDP (with 713.3) has crossed the GDP of the US (with 692.1).

Step 5: Setting Up Title and Labels for Chart

In this section, I will set up the title and labels for the chart using Excel functions. How I do it is described below.

• Firstly, we will set the title for the chart.
• For that, use the following formula in cell E22.

• Here, if F26 is none (it means there is no 1 in the range I6: I20), the IF function returns “US stays on top”, otherwise the IF function returns “Chinese GDP to pass US GDP in Year “&TEXT(F26,”0”)”. The TEXT function returns the value of cell F26 in general format.

• Secondly, to add the label for the US GDP to the chart, type the following formula in cell E23.

• Then, press Enter to see the annual growth of the US.
• Consequently, the value of cell E23 will be changed along with the value of cell C5.

• Thirdly, write the following formula in cell E24 to add the label for China’s GDP in the chart.

• Finally, you will get the desired result in cell E24 after pressing Enter.

Read More: How to Dynamically Change Excel Chart Data (3 Effective Methods)

Step 6: Inserting Data Series for Chart

Now, I will create an Excel chart to show the dynamic title and labels in it. To do that,

• Firstly, select an empty cell in the worksheet and create an XY chart (Scatter with Smooth Lines).

• Secondly, right-click on the empty chart and choose Select Data.

• Thirdly, click on the Add button to add Legend Entries (Series).

• Fourthly, to add the first series to the chart, go to the Edit Series dialog box.
• Then, as Series name, point to cell reference E23, as Series X values point to cell range B5:B2,0 and as Series Y values point to range D5:D20.
• Lastly, when done, click OK.

• Fifthly, the Select Data Source dialog box will appear again, and you will see the entry of the first series.
• To add the second series, click on Add.

• Again the Edit Series dialog box will be opened.
• This time, the Series name, points to cell reference E24, the Series X values point to cell range B5:B20, and the Series Y values, point to range G5:G20.
• Finally, when done, click OK.

Step 7: Showing Final Result

In this last step, we will create the chart with a dynamic title and label. See the following discussion to do the same.

• Firstly, you will see the two data series in the Select Data Source dialog box after the previous step.
• Here, press OK.

• Secondly, the chart will look like the following picture.

• Thirdly, click on the chart title and wait to see the solid border box around the title.

• Fourthly, in the formula bar, write the following formula to add the dynamic title to the chart.

• Finally, after pressing Enter, the final result will look like the following image.

Showing Dynamic Title and Legend Labels in Excel Chart

In my last discussion, I will show how to play with charts with dynamic titles and labels. By changing values within the chart’s data range, you can witness a significant change. The whole procedure is shown in the following steps.

• First of all, in cell D5, change the value of Growth (%) of the US from 3% to 10%.
• Then, after calculation, you will notice the changed cell value in E22, E23, and F26.

• Consequently, you will notice the change in the chart’s title and legend label for the US.

• Again, change the Growth (%) of China in cell G5 from 10% to 25%.
• Here, cell values of E22, E24 and F26 will change according to the value of the cell range F5:F20.

• Finally, you can see the changed title and data label in the graph.

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to create and visualize Excel charts with dynamic title and legend labels by following the above-mentioned discussion. Please share any further queries or recommendations with us in the comments section below.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.

Happy Excelling 🙂

Related Articles

• How to Create Dynamic Excel Charts with Drop-Down List (3 Ways)
• Create a Dynamic Chart Range in Excel (2 Methods)
• How to Use Dynamic Named Range in an Excel Chart (A Complete Guide)