Excel 2007: Take Control of Changing Data with Dynamic Charts – Part 2

By David H. Ringstrom, CPA

In Part 1 of this series I used a simple formula to create a rolling six-month chart. I’ll close this two-part series by showing you how to use simple macros to make your charts expand or contract automatically as you add or remove source data. I’ll first explain how to do so in Excel 2007, and then provide instructions for Excel 2003 users that haven’t upgraded yet.

Try it now:  A free 60-day trial of Office 2007 is available for immediate download. Note that you can instruct the installation program to leave your present version of Office intact so that you’re free to work in either version. Or, try the online test drive.

Transition tools: Office Online offers an online command reference that shows where your favorite Excel 2003 commands reside in Excel 2007. You can also download and install the command reference tool on your computer. Yet another option is to add the Get Started tab to your Excel 2007 ribbon — this puts numerous training aids right at your fingertips.

Create a Self-Expanding Chart in Excel 2007

The first step is to create some sample data to work with. Carry out these steps in a blank workbook:

1.      Enter 1/31/2008 and 2/29/2008 in cells B1 and C1, respectively.

2.      Select cells B1 and C1, and then drag the fill handle across to column G to create a series of dates through 6/30/2008.

Fill handle: The fill handle is the little black notch in the lower right-hand corner of the current cell or selection. When you select cells B1 and C1, the fill handle is in cell C1.

3.      Enter the word Sales in cell A2.

4.      Enter this formula in cell B2, and then copy it into cells C2 through G2:

=RANDBETWEEN(1000,2000)

RANDBETWEEN: The RANDBETWEEN function generates random numbers within a range that you specify, in this case between 1000 and 2000.

5.      Copy cells B2 through G2 to the clipboard, and then right-click on cell B2, choose Paste Special, and then double-click on Values. This provides sample data for the chart that we’ll build in a moment.

Dynamic Range Names

Range names enable you to assign a name of your choice to a worksheet cell or block of cells. Typically range names refer to a static area, such as cell B2 or cells A1 through G2. However, we can use the OFFSET function to create a range that expands automatically, so that if we add July data in column H, the range will expand automatically.

Range name basics: The article Use Names to Clarify Formulas may be helpful if you’re not yet familiar with using range names in your spreadsheets.

The OFFSET function has five arguments:

·         Reference: This argument provides a starting point for the offset, or in this case, the top-left corner of our dynamic range.

·         Rows: The rows argument allows us to move up or down from the starting point, but in this case we’ll enter a zero since we want to use the reference as our starting point.

·         Cols: The cols argument works in the same fashion as rows, so we’ll again use zero here.

·         Height: The height argument allows us to specify the number of rows tall that we wish our dynamic range to be. In this case we’ll use 2.

·         Width: The width argument allows us to specify the number of columns wide that our range should be. Instead of providing a static number, like 7, we’ll use the COUNTA function to calculate how many columns of data we have.

COUNTA: The COUNTA function returns the number of non-blank cells within a specified range. For instance, this formula reports how many non-blank cells are in row 2 of our spreadsheet:

=COUNTA($2:$2)

Now that you understand the OFFSET function, let’s create the dynamic range name that we’ll use in our chart:

1.      Click the Define Name button in the Defined Names section of the Formulas tab of the Excel 2007 ribbon.

2.      Enter the word ChartRange in the Name field of the New Name dialog box.

Restrictions: Range names cannot contain spaces, and cannot begin with numbers.

3.      Leave the Scope field set to the default of Workbook, and enter a comment if you wish.

4.      Enter this formula in the Refers To field:

=OFFSET(Sheet1!$A$1,0,0,2,COUNTA(Sheet1!$2:$2))

Absolute references: Always be sure to include the dollar signs to specify an absolute reference for the reference and width arguments, otherwise your dynamic range name will become truly dynamic, and shift every time you click on a new cell.

We’re now ready to build our chart:

1.      Select cells A1 through G2

2.      Choose Column in the Charts section of the Insert tab of the Excel 2007 ribbon, and then select Clustered Column — this the first chart in the 2-D section. A chart should appear on your worksheet, as shown in Figure 1.

Figure 1: A static six-month chart based the data in rows 1 and 2.

Create the Excel Macro

The following steps, which are exactly the same in Excel 2007 and earlier, make the chart dynamic:

1.      Right-click on the chart, and then choose Assign Macro.

2.      Click New in the Assign Macro dialog box. Do not change the default name that is assigned, which will typically be something like Chart1_Click.

3.      When the Visual Basic Editor appears on your screen, enter this line of code above the line that reads End Sub, as shown in Figure 2:

Worksheets("Sheet1").ChartObjects("Chart 1").Chart.SetSourceData Range("ChartRange")

Figure 2: This macro makes a chart that resides on a worksheet be dynamic.

Be sure to include a space before the word Range. There are three elements that you’ll need to change when you use this technique in the future:

·         Replace Sheet1 with the name of your worksheet

·         Replace Chart 1 with the name of your chart. Excel automatically assigns a name to a chart, such as Chart 1, Chart 2, and so on. Note that the macro name will not contain a space before the chart number, while the chart itself does. So, be sure to insert a space when you edit the name in the ChartObjects argument.

·         Replace ChartRange with the name that you assign to the dynamic range.

You can now close the Visual Basic Editor by choosing File, and then Close and Return to Microsoft Excel. Test your handiwork by adding data for 7/31/2008 in column H, and then click once on your chart. The chart data should expand automatically. Similarly, if you erase columns G and H, the chart will update automatically — you might not even have to click the chart — depending upon the current calculation mode that Excel is using.

The above macro works in cases where the chart resides within a worksheet. Follow these steps for a chart that resides on its own chartsheet:

1.      Select cells A1 through G2

2.      Press F11 to create a new chart on a separate chart worksheet.

3.      Right-click on the chart worksheet name, and then choose View Code, as shown in Figure 3.

Figure 3: Right-click on the  chart worksheet name, and then choose View Code.

4.      Choose Chart from the Declarations drop down list at the top of the Visual Basic Editor, as shown in Figure 4. A Chart_Activate macro will appear automatically onscreen.

Figure 4: Choose Chart from the General drop-down list in the Visual Basic Editor.

5.      Enter this line of code above the line that reads End Sub, as shown in Figure 5:

ActiveChart.SetSourceData Range(“ChartRange”)

Figure 5: This macro makes a chart that resides on its own sheet be dynamic.

6.      Close the Visual Basic Editor by choosing File, and then Close and Return to Microsoft Excel.

The chart will now automatically update when you add or remove data from the table and then click on the chart tab.

Create a Self-Expanding Chart in Excel 2003

The first step is to create some sample data to work with. Carry out these steps in a blank workbook:

1.      Enter 1/31/2008 and 2/29/2008 in cells B1 and C1, respectively.

2.      Select cells B1 and C1, and then drag the fill handle across to column G to create a series of dates through 6/30/2008.

Fill handle: The fill handle is the little black notch in the lower right-hand corner of the current cell or selection. When you select cells B1 and C1, the fill handle is in cell C1.

3.      Enter the word Sales in cell A2.

4.      Enter this formula in cell B2, and then copy it into cells C2 through G2:

=RAND (1000)*1000

RAND: The RAND function generates random numbers between 0 and 1.

5.      Copy cells B2 through G2 to the clipboard, and then right-click on cell B2, choose Paste Special, and then double-click on Values. This provides sample data for the chart that we’ll build in a moment.

5.      Choose Insert, Name, and then Define.

6.      Enter the word ChartRange in the Name field of the Define Name dialog box.

No spaces: Range names cannot contain spaces.

7.      Enter this formula in the Refers To field:

=OFFSET(Sheet1!$A$1,0,0,2,COUNTA(Sheet1!$2:$2))

8.      Select cells A1 through G2

9.      Choose Insert, Chart, and then click Finish. A chart should appear on your worksheet, as shown in Figure 1.

10.  Carry out the steps described in the Create the Excel Macro section above.

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.