For business users of Microsoft Excel Free guides and templates

Windows & Mac Excel

Charley's Swipe File #44

These two charts show the same data. But the chart of year-over-year performance reveals a hidden trend that's highlighted by split trend lines.


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

Charley's Swipe File #44The purpose of management reports should be to help readers find and track patterns of performance…quickly and easily.

So which figure does a better job of this?

The gray line in the top figure takes the traditional approach: It plots total unemployment claims in the US over the past two years.

This line is somewhat difficult to follow because it necessarily shows the effects of seasonal variations.

Looking only at the gray line, the trend appears to be downward, which is good news for unemployment claims. However, the split trend lines in the top figure hint at a problem because the orange line has a slightly flatter slope than the blue line.

The gray line in the bottom figure is based on the same underlying data. But this line, which plots the year-over-year ratio of performance, reveals a hidden trend.

Because the year-over-year ratio is less than 1, the gray line also shows that claims have fallen. But additionally, the trend in the line shows that the rate of improvement has been slowing for the past 12 months…at least until April.

And the two horizontal lines summarize the trends by showing the average change during each six-month period.

Usage Ideas

Year-over-year (YOY) ratios offer at least three significant benefits:

  1. They automatically remove seasonality from trend lines.
  2. They reveal changes in growth rates that the traditional method doesn’t.
  3. Plotting YOY ratios allows us to compare the performance of dissimilar measures in the same chart.

You can plot year-over-year trends to uncover hidden trends for nearly any periodicity for nearly any measure: sales, costs, web traffic, customer complaints, key ratios, and so on.

Excel Issues

Because this is weekly data, it’s difficult to display evenly spaced periods. This is particularly true for trends longer than one year, when we need to mark the beginning of each new year.

To solve this problem...

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

New Excel