Create charts in Excel with dynamic titles
Save time changing the title of your charts when adding new data to them by using a dynamic title
It is incredibly frustrating spending a lot of time inputting data and creating a nice chart to display that data, only to find out after printing/sending the chart that the previous title was still displayed.
By using a dynamic chart title you can link the title of the chart to a cell within your spreadsheet. When the cell updates so do your chart title(s). This is useful in situations where you have several charts in the same workbook; update one cell and in turn update multiple charts.
Work through the tutorial below to learn how to create dynamic a dynamic chart title
The below image shows sales data for January – June and a chart plotting this data, along with a standard title. When August’s sales data is entered, you will need to change the chart title in the data table and the chart. This chart can be found on the ‘Standard Chart Title’ tab of the accompanying workbook
Let’s make the same chart more professional and create a dynamic title.
- Create the data table and a standard clustered column chart (or look at the ‘Dynamic Chart Title’ tab in the workbook) by clicking Insert -> Charts -> Clustered 2d column
- You should now have a chart as per below:
- Click on the title (Sales) so that a box is displayed around the title
- Click into the formula bar at type ‘=’ and click the cell which will hold your chart title, then press ‘enter’. In the accompanying workbook, I have used the range B2:C2 as the cells are merged
- You have now created a dynamic chart title. All that remains is to tidy up the chart and change the title in the linked cell each month when new data is added.
Try changing the title to see how the chart updates.
Why not try creating additional charts on the same tab and giving them a dynamic title? Look at how quickly all the charts update when the title of the data table is changed.
If you are working with a large workbook that has multiple charts, data headings etc… that all share the same title consider creating a control page that can hold key variables. Change the title cell once in your control sheet and have your whole workbook instantly update
*Dynamic chart titles do not currently work with the new chart types in Excel 2016.