How To: Dynamic Chart Titles in Excel

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

Download the accompanying file(s) for this article.

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

Chart with a standard title
Chart with a standard title

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
Create a 2d column chart
Create a 2d column chart
  • You should now have a chart as per below:
Standard 2d column chart
Standard 2d column chart
  • Click on the title (Sales) so that a box is displayed around the title
Selected chart title
Selected chart 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
Formula for dynamic chart title
Formula for dynamic chart title
  • 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.
Column Chart with Dynamic Title
Column Chart with Dynamic Title

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. 

If you’ve found this article interesting and or useful please share it on social media using the handy share buttons

Subscribe to our mailing list to be the first to know when new articles are published

At Happy Duck we build bespoke business and financial management tools for small, medium and large companies that help them achieve their goals. This makes us happy. Find out more about us and about our services.

More in How To

0 Comments

Leave a comment

Your e-mail address will not be published. Required fields are marked *