|
Dynamic Charting Formulas in Excel
Introduction
This article addresses how to use dynamic named formulas to automate the updating of an Excel chart. A dynamic named formula refers to a worksheet range that is variable rather than fixed in size. These topics are discussed:
- The OFFSET and COUNTA Worksheet Functions
- Creating a Named Range
- Dynamic Chart—Data in a Single Column
- Dynamic Chart—13 Month Trend
- Adding a Scroll Bar to the 13-Month Trend Chart for Interactivity
- Adding a Moving Average Series to the 13-Month Trend
- Dynamic Formulas—Actual vs. Budget
- Dynamic Formulas—Actual vs. Last Year
- References
The OFFSET and COUNTA Worksheet Functions
The key to creating dynamic named ranges is to use the combination of Excel’s COUNTA and OFFSET worksheet functions in a formula.
From Excel’s on-line help, the COUNTA function simply returns the number of cells that are not empty within a stated range. For example, the formula in cell A8 of the screenshot below refers to the range highlighted blue. The formula is:
=COUNTA(A1:A6)
Again from Excel’s on-line help, the OFFSET function returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference returned by the function can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned. The syntax is:
Offset(range, rows, columns, height, width)
- The range is the starting range from which the offset will be applied.
- The rows are the number of rows to apply as the offset to the range. This can be a positive or negative number.
- The columns are the number of columns to apply as the offset to the range. This can be a positive or negative number.
- The height is the number of rows that you want the returned range to be.
- The width is the number of columns that you want the returned range to be.
For example, in the screenshot below the formula in cell A8 is:
=OFFSET(A1,1,1,1,1)
The formula in cell A9 is:
=OFFSET(A1,4,2,1,1)
Creating a Named Range
When you create a named range, you’re actually creating a name for a formula. Unlike the formula that you see in a worksheet cell, the named formula exists in memory.
Named ranges are created and managed in Excel’s Define Name dialog box. The dialog box below shows a named range called MyName that refers to the formula:
=Sheet1!$A$1: $A$6
To create the named range, go to the standard toolbar and hit Insert > Name > Define. Type a name for the formula in the field labeled Names in workbook. The selected cell or range address appears in the Refers to field. Verify that the address is correct and then hit the OK button. At this point the named range is created. You can continue to add more names or close the dialog box.
A quick way to check to see if your named range is covering the correct area is to click inside of the formula in the Refers to box of the Define Name dialog box. Excel will outline the area containing data with a blinking frame.
In the example below, I’ve clicked on the formula inside of the Refers to box. Excel shows the range A16:A28 as containing data by outlining the range with a blinking frame. The formula looks correct because the height of the range is 13 cells.
Dynamic Charting of Data in a Single Column
Assume the screenshot of the spreadsheet below is of a file called DyChrt1.xls and that the sheet contained in the DyChrt1.xls file is called Sheet1.
Enter the formula below into the Define Name dialog box. Name the formula DataLabels.
=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A$2:$A$12),1)
The Define Name dialog box looks like this:
Enter the formula below into the Define Name dialog box. Name the formula Data.
=OFFSET(DataLabels,0,1)
The Define Name dialog box looks like this:
Create a Clustered Column chart based on the range A1:B7.
Click on the outer edge of the chart. Go to the Chart Toolbar and hit Chart > Source Data. In the Source Data dialog box, enter the following formula into the Values box:
=DyChrt1.xls!Data
In the Category (X) axis labels box, enter the following formula:
=DyChrt1.xls!DataLabels
When complete, the Source Data dialog box looks like this.
The chart will now automatically update as you enter data into the range A8:B12.
A Dynamic 13-Month Trend Chart
A 13-month trend chart is useful for financial applications because it shows the current month, the same month last year, and all months between. To build the chart we’ll follow the same process as previously described. The exception is that the formula named Data will need to be rewritten so that it only reflects the most current 13-month trend. In other words, as data is entered into the spreadsheet we only want the most current 13 months to appear in the chart.
Assume the screenshot of the spreadsheet below is in a file called DyChrt2.xls and that the sheet contained in the DyChrt2.xls file is called Sheet1.
Enter the formula below into the Define Name dialog box. Name the formula DataLabels.
=OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A$2:$A$40)-1,0,-13,1)
Note that although the data in the example only spans the range A2:A27, the formula spans the range A2:A40 to allow for new data that is entered into the spreadsheet.
Enter the formula below into the Define Name dialog box. Name the formula Data.
=OFFSET(DataLabels,0,1)
Create a clustered column chart based on the data in the range A15:B27.
Just like in the first example click on the outer edge of the chart. Go to the Chart Toolbar and hit Chart > Source Data. In the Source Data dialog box, enter the following formula into the Values box:
=DyChrt2.xls!Data
In the Category (X) axis labels box, enter the following formula:
=DyChrt2.xls!DataLabels
The chart will now automatically update as you enter data into the range A28:B40.
The chart below is the result of data entered into the range A28:B28. Note how the month of March 2005 was added while February 2004 dropped off of the chart.
Adding a Scroll Bar to the 13-Month Trend Chart for Interactivity
Excel allows you to build interactive charts by allowing the user to indirectly link controls to named formulas. In this example, we’ll link a scroll bar to the 13-month trend chart. The scroll bar will allow the viewer to quickly scroll across the range of 13-month periods with a click of the mouse.
A simple scroll bar can be accessed via the Forms Toolbar. To access the Forms Toolbar, go to the Standard toolbar and hit View > Toolbars > Forms. Find the scroll bar in the Forms Toolbar. Click on the scroll bar once and then drag it onto the spreadsheet to create the control. Once the control is visible on the spreadsheet, put the control in edit mode by pressing the CTRL key and clicking on the control. Right-click on you mouse and go to Format Control to bring up the Format Control dialog box.
In the Format Control dialog box, click on the Size tab to adjust the height and width of the control. Go to the Control tab and add references to the maximum, minimum, and cell link.
Finally, the named range needs to be changed to reflect the number that is entered in the cell link box of the scroll bar control. I’ve replaced the -1 in the OFFSET formula with the expression
+Sheet1!$D$21*-1
Note that the change is a direct reference to the cell link with one exception; the expression has been multiplied by -1 so that the dynamic referenced range crawls up the data range as the scroll bar is adjusted. The revised DataLabels formula is:
=OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A$2:$A$40)+Sheet1!$D$21*-1,0,-13,1)
The screenshot below shows the revised interactive chart with scroll bar included:
Adding a Moving Average Series to the 13-Month Trend
The addition of a series that reflects the average of the data in the chart can be created by adding the AVERAGE worksheet function to the front of the OFFSET formula:
=AVERAGE(OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A$2:$A$40)+Sheet1!$E$21*-1,1,-13,1))
The formula above is in cell E22 of the screenshot below. A second range of data that refers to cell E22 has been added to column C. An additional named range called MvgAvg has been created that reflects this formula:
=OFFSET(DataLabels,0,2)
Add a second series to the chart. Click on the outer edge of the chart. Go to the Chart Toolbar and hit Chart > Source Data. In the Source Data dialog box, enter the following formula into the Values box:
=DyChrt2.xls!MvgAvg
When complete, the Source Data dialog box looks like this:
When you add the new series, it will appear as a set of columns.
Click once on the new set of columns (in the case, the maroon columns above). Go to the Standard toolbar and click on Chart > Chart Type > Line. Hit the OK button.
After adjusting the minimum and maximum Y-axis values, the spreadsheet now looks similar like this:
Dynamic Formulas – Actual vs. Budget
Given the data above, add these named formulas to the Define Name dialog box and reference them to the chart source data:
The range called DataLabels is:
=OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A$2:$A$20)-1,0,-4,1)
The range called Actual is:
=OFFSET(DataLabels,0,1)
The range called Budget is:
=OFFSET(DataLabels,0,2)
Dynamic Formulas – Current Four Periods vs. Past Four Periods
Define the current four periods as the most current 12 months. For example, the current four periods in the data set above are Qtr 1 04, Qtr 2 04, Qtr 3 04, and Qtr 4 04.
Define the past four periods as periods five through eight. For example, the past four periods in the data set above are Qtr 1 03, Qtr 2 03, Qtr 3 03, and Qtr 4 03.
Given the data above, add these named formulas to the Define Name dialog box and reference them to the chart source data:
The range called DataLabels is:
=OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A$2:$A$20)-1,0,-4,1)
The range called CurrentPeriod is:
=OFFSET(DataLabels,0,1)
The range called LastPeriod is:
=OFFSET(DataLabels,-1,1,-4,1)
References
These sites provide detailed examples of dynamic charts:
John Mansfield is a Financial Analyst for a large clinic and hospital group located in Scottsdale, Arizona. His healthcare background includes Management Accounting, High-Level Planning and Analysis, and Project work.
John hosts an educational web log called The Planning Deskbook that focuses primarily on Microsoft Excel. The web log includes a minor emphasis on healthcare analysis and general web related topics.
|
