How to calculate YOY growth in Excel Pivot Table

Estimated reading time 3 min. Works with ALL versions of Excel.
The data is a list of sales transactions, two columns – amount and date.
We have 5000 transactions over many years. We want to know how the business grew year on year. Here are the steps…

SNAGHTML48223f                  image

Download the sample file to follow the steps.

Go to the raw data and create a pivot table. Date in row area and Amount in value area. Drag the Amount column twice. This will help you understand how the calculation works.

Now we need to show the data at yearly level rather than at daily level. Right click inside any date and choose Group… option. Choose Year (and unselect Month). Click Ok.

Grouping by date, month, quarter or year     image

This is how you get yearly report. Now we want to compare each year result with its previous year and see the percentage growth. Subtract it and convert it to percentage.

The natural thought at this stage would be to write a formula in the next column. That is a very bad idea. As you may know already, The first thought which comes to our mind while working on Office is usually inefficient.

We must assume that Excel is capable of doing what you want, rather than trying to do it yourself. Let us find out …

Right click in one of the amount columns and look at all the options. Which one seems to be useful? Now, I want YOU to find the answer. Trust me, you will love the discovery process. And you will feel a sense of achievement.

I am intentionally keeping some area below blank.
Scroll down to know the answer.

Scroll down…

Scroll down…

Scroll down…

Scroll down…

YoY growth

Go to Show Values As and choose % Difference from… option. This opens a dialog which asks you the obvious question: % difference from what?

Show values as menu options         Percent Difference from Previous item

By default the currently selected year will be shown. But this is where the real power is hidden. Open that dropdown and choose Previous.

It means we want to take the value in each row (which is the value for a specific year), then compare it with the previous value, calculate the difference and show it as a percentage. Wow, the job is done.

YOY growth column using Show Value as

So simple. So powerful. So intelligent. This feature has been waiting for 25 years for someone to click on it!

More refinements

You will notice that the year 2016 shows a reduction of 85%. But there is no need to panic. We have data only till Feb in 2016. It is a good idea to filter out the 2016 year to avoid confusion.

Now we can use Conditional Formatting – Data Bar to visually represent the growth or decline in a more appealing manner. And finally, there is no need for the original amount column. This leads us to the final, more refined pivot.

Pivot with YOY growth and Data Bar conditional formatting

Here is another variation: a combo chart to show the actual revenue and the YOY growth.

Chart showing yearly revenue as bar and YOY growth as line chart on secondary axis

Further thoughts

Learn all features available in Show values as. Read these articles:
Adding formulas OUTSIDE Pivot Tables = Inefficiency
Pivot Table – Show Values As – Part 2
Pivot : Show Values As – Part 3

Apply this principle to existing reports and enhance them.

Right Click to learn more!

Share on linkedin
Share on facebook
Share on twitter

2 Responses

Queries | Comments | Suggestions | Wish list