For business users of Microsoft Excel Free guides and templates

Windows & Mac Excel

Charley's Swipe File #53

This Excel chart helps your readers to better evaluate performance by showing them the range in which past measures have trended in recent years.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Charley's Swipe File #53I first noticed a chart like this that showed daily stock prices within a range defined by opening and closing prices. But it can apply to any time-series data.

For example, this chart shows an unemployment rate for the past 13 months in the context of maximum and minimum rates by month since the data began as of January, 2000.

You could apply the same approach to sales data. The chart would be particularly interesting if your business varies by season, as this data does.

Setting Up the Data Support Sheet

The source data is in an Excel Table with two columns: Date and Value. But the Figure Data Support (FDS) sheet is more interesting.

In the FDS worksheet, I assigned the column titles as range names, and I used a green fill to show the columns that the chart references.

This sheet is more interesting because it uses array formulas to find the largest and smallest values by month prior to the actual dates shown in the chart.

The key to understanding array formulas is that they set up temporary arrays in memory, perform calculations against the arrays, and then return a result. So this array formula in cell C3 of the FDS sheet…

(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 $14.95

New Excel