For business users of Microsoft Excel Free guides and templates

Windows & Mac Excel

Charley's Swipe File #22

This professional-quality Excel chart, inspired by the Wall Street Journal, knows whether it's good or bad when performance has exceeded target.


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

Charley's Swipe File #22This figure was inspired by a Wall Street Journal figure titled, “Keeping Score, Some San Francisco Health Indicators.”

Unlike most charts, this one knows whether it's good or bad when performance has exceeded target.

For example, in the first measure, total revenues are greater than target, and the indicator is green. And in the second measure, expenses also are greater than target, but the indicator is red.

Usage Ideas

This figure does a great job of summarizing just about any type of performance against target. It uses the traffic-light colors as an alert.

Excel Issues

The most significant issue here is that the bars change between red and green depending on whether the target has been met. That’s significant because no version of Excel offers conditional formatting for charts. I’ll explain the simple trick that lets us get around this limitation, but let’s deal with some other issues first.

I created the legend using text and drawing objects, then copied and pasted it as an image. This gave me much better control over its positioning and appearance.

The text in the chart actually is in cells behind the chart. This lets me display the information in ways that a chart-only solution just wouldn’t allow.

The major challenge in displaying text with this method is that of positioning the bars in the chart so they line up with the borders of cells. One key is to...

(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 USD

New Excel