Locked lesson.
About this lesson
Create chart titles that dynamically update when the chart changes.
Exercise files
Download this lesson’s related exercise files.
5.03 dynamic-chart-titles - Exercise.docx65.2 KB 5.03 dynamic-chart-titles - Exercise solution.docx
5.7 MB Exercise - Dynamic Chart Titles.xlsx
9.7 KB Exercise - Dynamic Chart Titles - Solution.xlsx
17.3 KB Workbook - Dynamic Chart Titles.xlsx
10.7 KB Workbook - Dynamic Chart Titles - Complete.xlsx
18.3 KB
Quick reference
Create Dynamic Chart Titles
Use formulas to create a dynamic chart title.
When to use
We create a dynamic chart title whenever we are displaying different types of data in a chart and we want the chart title to match the data displayed.
Instructions
In this example, we are going to create a chart to show the grades for different students. However, we want the chart title to reflect the relevant student's name depending on which student has been selected from the drop-down list.
Create a Table
To ensure our data is dynamic, we can put our dataset into a table.
- Click in the data.
- Press CTRL+T.
- From the Table Design tab, in the Properties group, name the table 'Results'.
Create a Data Validation Drop-down List
- Click in cell C4.
- From the Data tab, in the Data Tools group, click Data Validation.
- From the Settings tab, in the Allow drop-down, choose List.
- In Source, select the student names in column N.
- Click OK.
Add the FILTER formula
-
In cell B8 type, =FILTER(Results,Results[Student]=$C$4,"No Records")
This formula will filter the results in the table and only show the results for the student selected in the drop-down list.
Now, if we change the student name the results will update.
Create a Chart
Let's create a chart based on our filtered data.
- Select the data.
- From the Insert tab, in the Charts group, click 2-D Column Chart.
The chart doesn't look quite right. We don't want the student's name to appear underneath every exam in the horizontal axis.
- Right-click on the chart and choose Select Data.
- On the right-hand side, click Edit.
- Change the Axis label range to C8:C15 and click OK.
Create a Dynamic Chart Title
Let's create a dynamic chart title that displays the student's name according to the selection in the drop-down list.
- Click in a blank cell anywhere in the worksheet.
- Type ="Exam Results - "&C4
- Press Enter.
This formula will output the text 'Exam Results -' and whatever name has been selected in cell C4.
Now we can link the formula to the chart title.
- Click on the chart title text box.
- Click in the formula bar and choose cell B22.
Finally, because our data is in a table, if new student data gets added to the bottom the data validation list and the formulas will update automatically.
Hints & tips
- The FILTER function is available in Excel 365 and Excel 2021.
Sorry, we don’t have a transcript for this lesson yet.
Lesson notes are only available for subscribers.