Simplify your future with rolling charts

You have prepared a presentation using 12 months of sales data for your client, Dynamo Co. After everyone is gathered, the client’s CEO says she has more recent sales figures available and asks whether they can be included in the chart while eliminating some of the old data. How do you make this transition quickly and easily?

It’s well known that Excel charts automatically reflect changes to existing data. For example, if your data set consists of 12 rows and you change the value of one of those rows, the Excel chart instantaneously alters the charts referring to that data set. But what happens if you add a 13th row of data? Nothing. Including that new data in an existing chart requires manually revising the chart references—a tedious process and one prone to error. However, with a little work upfront, you can create charts that simplify the process of adding new data, allowing you, in the example given, to update charts “on the fly” without delaying your presentation.

Excel has accommodated real-time chart creation for years. An April 2006 JofA article (“Charts in Real Time,” page 65) by Patricia McCarthy showed how to configure dynamic charts that automatically update as new data is added to the data set. It’s an effective procedure, but it comes with a significant side effect. Each time you add new data, Excel adds another column to the chart. While additional columns might be helpful in some circumstances, charts increasingly become cluttered and difficult to read with each added column. This article shows how to extend that procedure to configure a rolling dynamic chart that automatically includes the most recent “x” number of observations in the data set, for example, a rolling chart showing monthly revenue for the previous 12 months. To follow along with this article’s example, and to see completed charts, download the Excel file.

Static Charts

Exhibit 1 illustrates Dynamo Co.’s 2011 revenue in a column chart. You can view this chart on the “Static” worksheet in the accompanying Excel workbook. The chart was created in the following manner: From the Insert tab, select Column in the Charts Group, then select 3-D Column, 3-D Clustered Column. In the chart shown in Exhibit 1, on the Home tab, the formula bar shows that this chart was built using a SERIES function consisting of four references: (a) the chart title (Static!$B$1), (b) the horizontal axis (Static!$A$2:$A$13), (c) the vertical axis (Static!$B$2:$B$13), and (d) the plot order (1) when multiple series are being charted. The plot order element needs to be included here although it has no effect in this case. Plot order comes into play when there is more than one series of data (e.g., Revenue and Gross Profit as columns on the same chart). The chart aesthetics also have been changed from the default characteristics.

Exhibit 1: Chart Built With SERIES Function

In the example below, if you add sales of $140 for Jan-12 in row 14, the chart will not change. The SERIES references are static, or “absolute” in Excel terminology. The chart cannot recognize additional data, only changes to existing columns and rows. The only way to revise the chart is to manually alter the SERIES references. Given the number of references that would have to be changed in the SERIES formula, such an approach would be inefficient and a breeding ground for mistakes.

Fortunately, you can create a chart that uses the most recent “x” number of rows in a named range. For example, if you configure the chart to show only the most recent 12 months, Jan-11 would “roll off” the chart when Jan-12 is added. To create rolling charts, use a combination of three functions: RANGE NAME, OFFSET, and COUNTA. Also, add data in a single worksheet cell to tell Excel how many rows to include in the chart.

Roll With It

A four-step process can create charts that automatically reflect additions to the data set. You can replicate the steps outlined below on the “Exercise” worksheet in the Excel workbook available here. To avoid confusion with the “Static” and “Live” worksheets in the same workbook, Month has been replaced with Date in cell A1 and Revenue has been replaced with Sales in cell B1.

Here’s a brief explanation of the three functions that create the rolling chart:

RANGE NAME creates meaningful shorthand to make it easier to understand a cell or range reference. Properly used, for example, RANGE NAME allows users to use Revenue or Sales as the reference, instead of Static!B2:B13, for the information in row B, as was done in the SERIES reference shown in Exhibit 1.

The OFFSET function returns a reference to a range that is offset a number of rows and columns from another range or cell. This essentially tells Excel where to begin to refer to a range of data. For example, you can use the OFFSET function to instruct Excel to build a chart using only the previous 12 months’ data.

COUNTA is a useful yet little known function that counts the number of cells containing data—either numbers or labels—in a selected range. This function is essential to making the OFFSET function work in building rolling charts.

Let’s work through the example on the “Exercise” worksheet in the Excel workbook. To save time, the Date and Sales information already has been entered in columns A and B.

Creating Rolling Charts: The Four Steps

The first step in creating rolling charts is to select an empty cell somewhere in the worksheet to identify the number of columns to be used. In this case, 12 periods need to be charted. As a result, Number of Periods has been entered in cell C1, with 12 going into C2.

The next step is to create the first range name. Go to the Formulas tab and select Define Name, Define Name in the Defined Names group. That opens the New Name dialog box. Following the example shown in Exhibit 2, in the Name: field, type Date. On the Scope: dropdown menu, keep the default choice, Workbook. In the Refers to: field, type =OFFSET($A$2,COUNTA($A:$A)-($C$2+1),0,$C$2,1), then select OK. As a whole, this OFFSET formula directs Excel to name the range comprising the last “x” rows of data in column A with “x” being the value located in cell $C$2. Let’s take a look at each element of the formula.

Exhibit 2: Defined Date

The syntax for OFFSET is OFFSET (reference,rows,cols,height,width). The reference criterion in the exercise tells Excel to begin its reference in cell $A$2. This is followed by the rows criteria, where the COUNTA($A:$A) function directs Excel to count the rows in column A that contain data. By default, COUNTA stops when it finds the first cell without data. The other part of the rows criteria, -($C$2+1), tells Excel to back up “x+1” rows to define the first row of data to include in the series. The column criterion is zero (0) because you do not want the named range to shift left or right from column A. The height criterion uses $C$2 to tell Excel how many rows of data to include. Finally, the width criterion is 1, which tells Excel that the named range is one column wide. The result is a named range that is dynamic—that is, it changes based on the most recent “x” number of cells containing data rather than being static and referring to a fixed range of cells.

Step 3 (shown in Exhibit 3) uses the same function but for Sales rather than for Date. From the Formulas tab, select Define Name, Define Name. In the New Name dialog box: type Sales for Name:; leave Scope: as Workbook, and in Refers to: type =OFFSET($B$2,COUNTA($B:$B)-($C$2+1),0,$C$2,1) as illustrated. Select OK.

Exhibit 3: Defined Sales

Finally, Step 4 invokes the Chart Wizard to create the chart as usual, except that the series refers to the named range Sales and the category axis refers to the named range Date. As with many tasks in Excel, there are multiple ways to create charts and graphs. Typically, with the cursor located in a cell containing data, invoking the Chart Wizard causes Excel to try to guess which data should be selected. In this example, you do not want to use the standard configuration, which forces you to then modify the chart. It is far more efficient to start with a blank chart, then add the appropriate references.

To begin, click on a blank cell in the worksheet (otherwise, Excel will try to guess which data should be selected for the chart) and from the Insert tab’s Charts group, select Column, 3-D Column, 3-D Clustered Column. This creates a blank chart. Click on the Chart Tools’ Design tab. Click on Select Data in the Data Group, opening the Select Data Source dialog box. In Legend Entries (Series), select Add. In Series Name: type =Exercise!$B$1 and in Series Values: type =Exercise!Sales (see Exhibit 4). Select OK. In Horizontal (Category) Axis Label, select Edit. In Axis label range: type =Exercise!Date (see Exhibit 5). Select OK. Then select OK again to close the Select Data Source dialog box. This is where the RANGE NAME comes into play—rather than referring to static rows and columns such as $A$2:$A$13, you are using the RANGE NAMEs of Date and Sales.

Exhibit 4: Edit Series

Exhibit 5: Axis Labels

You can now add new data in rows 14 and 15 of the Exercise worksheet tab. Your chart automatically shows the most recent 12 months of sales. The resulting chart from the completed steps, on the Live worksheet tab, with January and February 2012 revenue added to the data set, is shown in Exhibit 6. The chart now includes the first two months of 2012, while the first two months of 2011 have “rolled off.” Because the OFFSET function refers to cell $C$2, the value in that cell can be changed to whatever number of months you wish to include on the chart (e.g., the most recent six months rather than the most recent 12 months). Note: The aesthetics of the chart in Exhibit 6 have been modified to remove the legend and maximize the area for the columns. To remove the legend, click on it and press the Delete key.

Exhibit 6: The “Rolling” Chart

There are a couple of limitations to bear in mind. Note that if you delete data, both columns of a row must be eliminated to avoid distorting the graph. Also, there must be no blank rows in a column of data. That’s because, as mentioned earlier, the COUNTA function stops when it encounters a blank row.

Conclusion

Play with the chart you created in the Exercise worksheet. Try creating charts for different numbers of months. In the process, you should discover that you can update these charts in virtually no time at all, making it easy to adjust to last-second changes such as the scenario presented at the beginning of this article.

The RANGE NAME, OFFSET, and COUNTA functions combine to create dynamic range names that can be used in a variety of situations—not just for charts and graphs. This approach works for PivotTables and other analytical tools as well. Whenever rows or columns of data are likely to be added, using dynamic range names can help simplify your future.

To follow the technical walkthrough in the article and to see completed charts, download the Excel file “Rolling_Charts_Excel_Workbook.xlsx” here.

James A. Weisel (jweisel@ggc.edu) is a professor of accountancy at Georgia Gwinnett College in Lawrenceville, Ga., and president of SCM Consulting Ltd.

To comment on this article or to suggest an idea for another article, contact Jeff Drew, senior editor, at jdrew@aicpa.org or 919-402-4056.

AICPA RESOURCES

JofA article

“Pivotal Advance Boosts Excel’s Power,” Sept. 2011, page 40

Publications

• Building Financial Models With Microsoft Excel: A Guide for Business Professionals, 2nd edition (#WI0481745)
• Financial Analysis and Modeling Using Excel and VBA, 2nd edition (#WI275603)
• Financial Simulation Modeling in Excel: A Step-by-Step Guide + Website (#WI931226)
• Practical Financial Decision Making: Essential Tools (#091091, paperback; #091092PDF, on-demand)

CPE self-study

• Accounting and Auditing With Excel: Practical Applications for Maximum Performance (#745742)
• Advanced Excel: Practical Applications for the Accounting Professional (#745752)
• Tools of the Trade: Excel and Access Applications for Accountants in Industry (#757110)

For more information or to make a purchase, go to cpa2biz.com or call the Institute at 888-777-7077.

IT Division and CITP credential

The AICPA Information Technology (IT) Division serves members of the IT Membership Section (ITMS), CPAs who hold the Certified Information Technology Professional (CITP) credential, other AICPA members, and others who want to maximize information technology to provide risk, fraud, internal control, audit, and/or information management services within their firms or for their employers. The division aims to support members and credential holders who leverage technology to provide assurance or business insight about financial-related information (direct and indirect financial data, processes, or reporting) to support their clients and/or employers. To learn about the IT Division, visit aicpa.org/infotech.