Charts in Real Time

The principal function that transforms a static chart into a dynamic visual aid is OFFSET, a deceptively powerful tool. Simply put, all OFFSET does is return a cell reference. That doesn’t sound particularly useful or powerful, but link it to another, even simpler function, such as COUNTA, and its underlying power is realized. Before we show you how to apply OFFSET and COUNTA to create a dynamic chart, let’s look more closely at the two functions.

Technically speaking, OFFSET returns a reference to a range or a single cell from a range or single cell. Its syntax is OFFSET (reference, rows, cols) , where

Reference refers to a cell or range.

Rows is the number of rows you want the upper-left cell of the range to refer to. For example, using 10 as the rows argument specifies that the upper-left cell in the reference is 10 rows below the reference point; likewise, negative 10 (-10) specifies the number of rows above the starting reference.

Cols is the number of columns, to the left or right, you want the upper-left cell of the result to refer to. A 10 specifies that the upper-left cell is 10 columns to the right of the starting reference; a negative 10 (-10) specifies 10 columns to the left of the starting reference.

The COUNT function counts the number of values in referenced cells. The more advanced COUNTA function counts both values and text, which we’ll need in this exercise.

MAKING A DYNAMIC CHANGE
Let’s create a worksheet called Dynamic to demonstrate how to set up such a chart. In exhibit 1 , we’ve entered data for the chart: months in column A under Date and Pounds of coffee beans purchased in column B.

Exhibit 1

To streamline the formulas, name the data in the A column Date and the data in the B column Beans . To do that, click on Insert, Name, Define . Under Names in the workbook , enter the title of the first column— Date . In the Refers to field, type:

=OFFSET(Dynamic!$A$2,0,0,counta(Dynamic!$A:$A)-1)

The formula tells Excel that the data for the first column (the X-axis of the eventual chart) of the Dynamic worksheet start at A2. The 0,0 tells Excel not to add additional rows or columns. The second part of the equation (after COUNTA) counts the data in the entire column. Because column A has a heading row called Date in row 1, the formula subtracts 1 from COUNTA’s total, since we don’t want A1 as a data point in the graph ( exhibit 2 ).

Exhibit 2

Finally, click on Add to create the Date range name.

To enter the name for the second series, which in this case will be Beans , follow the above steps, except in the Refers to box, type:

=OFFSET (dynamic!$b$2,0,0,Counta(dynamic!$b:$b)-1)

The equation is similar to the first, except it references column B instead of column A. Click on Add and then on OK to save and close the Define Name dialog box ( exhibit 3 ).

Exhibit 3

To create the chart, highlight cells A1 through B13 and then click on Insert , Chart to open the Chart Wizard . Select the default Column chart type as shown in exhibit 4 .

Exhibit 4

Click through the wizard’s Next buttons and at Step 3 add the appropriate documentation. In this case we added a title and some descriptive information on both the X and the Y axis ( exhibit 5 ).

Exhibit 5

Then click on Finish and that places the chart in your current worksheet.

Now we need to evoke the chart’s underlying formula and adjust it a bit to make the chart dynamic. To evoke the formula, click once on any of the chart’s columns. Small black squares should appear in each of the bars. If a small black square appears in only one bar, you have selected only a single data point, so try again.

Once you’ve selected the entire series, Excel generates this formula in the formula bar ( exhibit 6 ):

=SERIES(dynamic!$B$1,dynamic!$A$2:$A$13,dynamic!$B$2:$B$13,1)

Exhibit 6

Now replace the absolute references in $A$2:$A$13 with the range name Date and the $B$2:$B$13 with the range name Beans . Without those changes the Excel chart would continue to draw data from only the first 13 rows.

After the changes the formula should look like this:

=SERIES(dynamic!$B$1,dynamic!Date,dynamic!Beans,1)

Check that the worksheet does indeed update when more data are added. To do that, type Jan in cell A14 and any number in B14 and watch the chart automatically update.

MULTIPLE DATA RANGES
What if you have a graph with multiple data ranges? The steps are essentially the same. For example, let’s add another column, Pounds of coffee beans grown , to our chart. We’ll name the column Grown and reference it in the Define Name dialog box as:

=Offset(dynamic!C$2,0,0,CountA(dynamic!$C:$C)-1)

Exhibit 7 is a screenshot of our chart with the additional series of grown coffee beans added.

Exhibit 7

As you can see, this technique can produce some very powerful real-time presentations. It also can generate what-if presentations in real time.

Did you think charting could be so much fun?

Patricia McCarthy owns McCarthy Helstrom & Associates, a software training and consulting company in Noblesville, Ind. She is a frequent discussion leader for the Indiana CPA Society. Her e-mail address is mccarthy_patricia@yahoo.com .