For business users of Microsoft Excel Free guides and templates

Management Charts

Introducing the Power of Year-Over-Year Performance Charts in Excel

You can uncover hidden trends by charting the ratio of each month's performance to the same month one year earlier.

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

The purpose of management reports should be to help readers find and track patterns of performance…quickly and easily.

That’s the attraction of charts, of course. But should we always plot the raw data? Or should we ever transform it somehow? To see one type of transformation that’s often revealing, take a look at these two figures from Charley’s Swipe File #44:

The left Excel chart plots the trend in the data. The right chart shows the trend in its year-over-year ratio.

In the left figure, the gray line 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 shows the effects of seasonal variations. True, we could seasonally adjust the data. But doing so necessarily distorts actual performance. And it adds more work.

Looking only at the gray line in the left figure, the trend appears to be downward, which is good news for unemployment claims. However, the split trend lines in the left figure hint at a problem because the orange line has a slightly flatter slope than the blue line. That is, jobless claims might not be falling as quickly this year as they did last year.

In the right figure, the gray line is based on the same underlying data. But this line plots the year-over-year ratio of performance. That is, it plots the ratio of each period divided by the equivalent value one year earlier.

Because the year-over-year ratio is less than 1, this gray line also shows that unemployment claims have been falling during the past year.

But by plotting the YOY ratio we reveal a trend that the left chart only hinted at: The rate of improvement has been slowing steadily for the past 12 months…at least until April.
(Remember that this is a bad-news chart, where an increase in unemployment claims is bad and a decrease is good. If this were a good-news chart—a chart of sales, for example—the rising trend would be a good thing.)

The two horizontal lines summarize the changing trends by showing the average change during each six-month period. Here, as you can see, there’s a significant difference between the averages. There’s no doubt that the number of continued jobless claims is falling at a slower rate.

Using Year-Over-Year Data

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, and for nearly any measure: sales, costs, web traffic, customer complaints, key ratios, and so on.

Give year-over-year charts a try. You might discover something interesting about your business.

Free Excel Dashboards

Click to see testimonials from readers

Charley's SwipeFile charts

Click to see who uses Excel dashboards.