Automating Excel Chart Titles

Excel expert David Ringstrom is sharing his tips for optimizing the way you use Excel in our new Excel Tips series.

By David Ringstrom
 
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, as shown in Figure 1.
 
Figure 1: Creating a chart from a Table in Excel 2007 and later.
 
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, as shown in Figure 2. 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.
 
Figure 2: Adding a title to the chart.
 
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, shown in Figure 1. 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, as shown in Figure 3.
 
Figure 3: A simple formula can return the month of the latest data in your chart.
 
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, as shown in Figure 4. 
 
Figure 4: Chart titles can reference a formula in a worksheet cell.
 
To make the chart completely dynamic, click on cell A1, choose Insert, Table, and then press Enter, as shown in Figure 5. My previous article in this series describes this technique in more detail.
 
Figure 5: Excel's Table feature automates charts such that new data appears automatically.
 
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, as shown in Figure 6. 
 
Figure 6: New data appears on the finished chart, along with a self-updating title.
 
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.
 
Related articles:
 
 
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

 

You may like these other stories...

Event Date: May 29, 2014 In this presentation Excel expert David Ringstrom, CPA brings you up to speed on the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both...
No field likes its buzzwords more than technology, and one of today's leading terms is "the cloud." But it's not just a matter of knowing what's fashionable. Accounting professionals who know how to use...
There is a growing trend of accountants moving away from traditional compliance work to more advisory work. Client demand is there, but it is up to the accountants to capitalize on that. What should accountants' roles be...

Upcoming CPE Webinars

Apr 22
Is everyone at your organization meeting your client service expectations? Let client service expert, Kristen Rampe, CPA help you establish a reputation of top-tier service in every facet of your firm during this one hour webinar.
Apr 24
In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel.
Apr 25
This material focuses on the principles of accounting for non-profit organizations' revenues. It will include discussions of revenue recognition for cash and non-cash contributions as well as other revenues commonly received by non-profit organizations.
Apr 30
During the second session of a four-part series on Individual Leadership, the focus will be on time management- a critical success factor for effective leadership. Each person has 24 hours of time to spend each day; the key is making wise investments and knowing what investments yield the greatest return.