For business users of Microsoft Excel Free guides and templates

Windows & Mac Excel

Charley's Swipe File #65

This type of figure is known as a small-multiples (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, 2005-2014
The Father of Spreadsheet Dashboard Reports

This type of figure is known as a small-multiples (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.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 small-multiples (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 more-common line plot.)

Note that the X-axis 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:

Credit Cards
 

Only $17.95

New Excel