# Automating Excel Chart Titles

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:

To access all of the content on our site, register (it's free!) or login to your existing account.

BONUS: If you register now you can opt to receive a digital copy of "Transform!" , Richard Francis' new book for growing firms [US/Canada ONLY].

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.

### Replies

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)
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)
By Josh
Jun 26th 2015 01:11

This is a great little tute. Thanks David!

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

this is a great tip.

Thanks (0)
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.

Costel

Thanks (0)
By David Ringstrom
to dshatkowski
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)
By Costel Sava
to stallworthhl
Jun 26th 2015 01:11

Thanks

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