Excel Howtos, Pivot Tables & Charts

Show monthly values & % changes in one pivot table

Last updated on November 6, 2012

Chandoo

Pivot tables are great help when analyzing lots of data. One of the common questions managers & analysts ask (when looking at monthly sales data for example) is,

How is the monthly performance of our teams (or regions, products etc.)?

A pivot report can answer this question in a snap.

But the answer is incomplete!

Why? Because, we don’t want sum of sales by month & sales person alone. We want to know their performance! Something like below:

Performance eh?!? How to measure it?

There are many ways to measure performance. For our monthly sales data, we can measure performance by comparing,

Sales with targets
This month value with previous month value
This month value with same month last year value
One person’s sale with rest of team etc.

One of the most common ways to measure performance in situations like this is to see how this months value has changed compared to previous month.

How to show monthly values & % changes in pivot?

Do you know that with just a few clicks, we can add % changes to our pivot? Follow these steps:

1. Create a pivot report with months & sales persons (or months & products, months & regions etc.)

2. Add Net sales (or any other metric) to value field area of pivot report

3. Now, add net sales once again to value field area

That is right. You can add same metric more than once to pivot table value field area.

At this point, our report looks like this:

4. Right click on 2nd value and choose value field settings.

5. Click on Show values as tab and follow below steps. (see image aside)

Choose “% Difference from” from the drop down
Select Month as base field
Select (previous) as base item
Click ok

This will show % changes with respect to previous month in the pivot report!

Extending this to make it even more awesome

1. Clean up the titles

Change the titles to Sales & % change. To do this, just select the first column title and type over. Repeat for 2nd column.

2. Add conditional formatting

Select any cell in the % change column. Go to Home > Conditional Formatting > New rule

[Resource: Introduction to Excel Conditional Formatting, more]

Specify the rule as mentioned in below illustration.

3. Show just icons

We can go one more step and show just icons. Since pivot tables show tool tips on hover, we can easily find % change for any month / sales person by just pointing on that cell.

Finalized monthly report

Our final report looks like this:

Download Example Pivot Report

Click here to download this example pivot report. Examine various settings & conditional formats to learn this better.

How do you use value field calculations in Pivot reports?

Although most of my pivots use simple sum or count type of summaries, often I use custom calculations like % difference from, running total , % of row etc. to understand the data better. These are very simple to setup yet give powerful insights.

What about you? Do you use value field settings to modify your pivot reports? What other summary techniques you use? Please share your tips, ideas using comments.