Just as a picture is worth a
thousand words, so a chart is worth a thousand
numbers. But a chart that updates automatically as
its underlying data change is worth lots more. As
you’ll see, Excel can do that with a little
preparation. 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.
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 ).
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 ).
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 .
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 ).
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)
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.
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?
|
AICPA RESOURCES
TECH 2006: Information
Technology Conference June
11–14, 2006 Hilton,
Austin, Texas
| |
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
. |