Windows & Mac Excel
Charley's Swipe File #65
This type of figure is known as a smallmultiples (or panel) chart. And the black lines are called reference lines. You could use it to compare the performance of any number of similar entities to the performance of a single relevant measure.
by
Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

Each
chart in this figure compares the annual growth
rate of a country’s GDP to the growth rate of
the GDP for the United States.
You could use the same approach to compare the annual growth
rates of sales by product to the growth rate of a key product,
or to the growth rate of some other influential measure, like
the traffic on your web site.
In fact, you could use it to compare the performance of any
number of similar entities to the performance of a single
relevant measure.
More formally, this type of figure is known as a
smallmultiples (or panel) chart. And the black lines are called
reference lines.
Excel Issues
The key Excel challenge in this figure is that Excel doesn’t
provide an easy way to generate those black reference lines.
Before you read on, take a moment to think about how you might
create them.
The first time I thought about this challenge, I could think
of only one answer: use XY plots. This approach seemed like it
would be a lot of work to do. But after I set up the range
titled Black Reference Line in the workbook’s Figure Data
Support (FDS) sheet, it turned out that it wasn’t so difficult
after all.
Lines created by XY (or “scatter”) plots require that you
specify both the X and Y positions for each point in the line.
And that’s what I’ve done with the Black Reference Line range
that you’ll find in the FDS sheet. And the formulas in that
range return the required data from the original data range.
(For future reference, if you ever need to display a vertical
line in a chart, you must use an XY plot, not the morecommon
line plot.)
Note that the Xaxis labels for each chart actually are data
labels associated with a hidden line at the bottom of each plot
area. To see the formula, click on one of the labels then click
on the edge of the text box that contains the label. After you
do so, you’ll see a formula like =FDS!Label1 in your formula
bar. This formula returns the text in the range named Label1 in
the sheet named FDS.
(Continued in the documentation.)
Order Details

Excel Versions:
Excel 2007 and after, for either Windows or Mac
Availability:
Instant download of zipped Excel and
PDF files. If you ever need
another download, you can get it at any time.
Guarantee:
One full year, unconditional.
Currency:
All prices are in US Dollar currency.
Licensing:
Two copies: One for work plus one
for home.
Credit Cards:


Only $17.95

