Automating Excel Chart Titles

Jun 21st 2012
Share this content
Spreadsheets and graphs on a desk
xfgiro/istock

Recently, I described how you can use Excel's Table feature to have charts expand automatically. In this article, I'll show how you can further automate your chart with a self-updating title.

To create a chart in Excel 2007 and later, create a data set like the one shown in cells A1 through E3. Next click on cell A1, choose Insert, Column, and then the first 2-D option.

The next step is to add a title to the chart. To do so, click once on the chart to select it, and then choose Layout, Chart Title, and then Above Chart. To manually change the title, you can click on the title field within the chart and edit the text as desired. However, we can use a formula to make the title dynamic instead.

To automate your chart title, use two worksheet functions together:

  • COUNTA - This worksheet function returns the number of non-blank cells in a given range.
  • INDEX - This worksheet function returns data from cell coordinates that you provide.

Use COUNTA to determine the number of non-blank cells in row 1. This assumes you won't have any data to the right of your source data. If you've entered four months of data, then COUNTA will return the number 5, because there will be five non-blank cells in the row. You'll then use the INDEX function to return the word "April." To do so, you'll tell the INDEX function to look across row 1 of your worksheet, and return data from the nth cell, as provided by COUNTA. The formula will take this form:

=INDEX(1:1,COUNTA(1:1))

You may wish to add additional narrative, such as the word "Sales." To do so, you can use a technique known as concatenation. Although Excel has a CONCATENATE worksheet function, I use this approach instead:

=INDEX(1:1,COUNTA(1:1))&" Sales"

In essence, I use an ampersand to join the additional text to my formula. Such text must be enclosed in double quotes.

The aforementioned formula can be entered in any cell in your worksheet. I've chosen to place it in cell A5, just below my source data.

You're now ready to automate your chart title. To do so, click on the title of your chart, and then click in the Formula bar. Click on the cell that contains your title formula, such as cell A5 in this case, and then press Enter.

To make the chart completely dynamic, click on cell A1, choose Insert, Table, and then press Enter. My previous article in this series describes this technique in more detail.

Your chart is now dynamic. If you've followed all of the steps in this article, you should be able to add a new column of data for May and see the new data and title automatically.

Note: There are a couple of caveats to be aware of with regard to automating charts. First, if you make the data into a Table before you create your chart, Excel may group the data. To change this, click the Switch Rows/Columns button on the Design tab. Secondly, when automating chart titles, you must reference a worksheet cell, and your formula reference must include a worksheet name. Thus, you can't click on the title and enter =A5 in the formula bar. You must instead use the form =Sheet1!A5.

Stay tuned, as my next article in this series will explain the technique I use to utilize clip art within charts.

Replies (9)

Please login or register to join the discussion.

avatar
By Kchildress
Jun 26th 2015 01:10

I like the title being dynamic.  I made it a little more descriptive by changing the formula in A5 to =CONCATENATE(B1&" - ",INDEX(1:1,COUNTA(1:1))&" SALES").  This way the title is January - April Sales

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:10

Great tip! You could shorten the formula to this

=B1&" - "&INDEX(1:1,COUNTA(1:1))&" SALES"

Simply omit the CONCATENATE function and use an & where you had a comma to separate the arguments.

Thanks (0)
avatar
By Josh
Jun 26th 2015 01:11

This is a great little tute. Thanks David!

Thanks (0)
avatar
By kenneth
Jun 26th 2015 01:11

this is a great tip.

Thanks (0)
avatar
By Costel Sava
Jun 26th 2015 01:11

HI.

I am relatively new to xls. I have a problem making multiple charts from one table.

In the image attached , i put a sample from my 1000 line table, an i make the graphic for the first line.

I need a trick to make 1000 charts .

So i need to make automated charts for each line from my table.

Can you please help me?

thanks in advance.

Costel

Thanks (0)
Replying to dshatkowski:
avatar
By David Ringstrom
Jun 26th 2015 01:11

I do not recommend that you attempt to build 1000 charts, as you'll end up with an unwieldy workbook that will probably be unstable. Instead, I'd create a separate worksheet where you create a single chart for one row of your data. You can then copy and paste another row of data into that spot to update the chart, or use either SUMIF or VLOOKUP to automatically look up the numbers for your chart when you change the ID. This probably doesn't give you nearly as much information as you need to get going, but if you post your question to a moderated user forum such as at www.mrexcel.com you'll most likely find a cadre of folks ready and willing to walk you through the details.

Thanks (0)
Replying to stallworthhl:
avatar
By Costel Sava
Jun 26th 2015 01:11

Thanks

Thanks (0)
avatar
By mxse
Jun 26th 2015 01:11

Great job! Really helpful.

Thanks (0)
Replying to Clinton Lee:
avatar
By David Ringstrom
Jun 26th 2015 01:11

Thank you for your kind feedback!

Thanks (0)