PivotTables can make quick work of summarising and analysing data and they have some handy built in percentage calculations available via the Show Values As menu.
The Excel PivotTable Percentage Change calculation is achieved with the % Difference From option and is useful for quickly identifying if this month/quarter/year is better or worse than last month/quarter/year.
Add in some Conditional Formatting to your PivotTable, and throw in a Slicer and we’ve got a super quick, visually appealing, interactive report at the click of a few buttons.
Let’s look at how to build the month on month percentage change PivotTable above and watch the video to learn what NOT to do when calculating the percentage change from zero.
Download the Workbook
Enter your email address below to download the sample workbook.
Watch the Video
Excel PivotTable Percentage Change
Step 1: Start with a regular PivotTable, and add the field you want the percentage change calculation based on, to the values area twice:
Step 2: Right-click any values cell in the Sum of Sales2 column > select Show Values As > % Difference From…:
Note to Excel 2007 users: The Show Values As options are in the Value Field Settings dialog box:
Tip: You don’t need the Sales field in the Values area twice to show the % Difference From. If you only want to show the percentage change and not the actual Sales amounts, then you can simply add the ‘Sales’ field to the Values area once and then set that field to % Difference From.. via the ‘Show values as…’ menu.
Step 3: In the Show Values As dialog box set the Base field to Month and the Base item to (previous):
Your PivotTable should now look like this:
Tip: Give the ‘Sum of Sales2’ field a better name. Simply type a new name in cell C3, making sure it’s not the same as any of the field names in your PivotTable source data. I’ll just call mine %. You’ll see why in a moment:
Add some Conditional Formatting
We can make the % change percentages easier to read with some Conditional Formatting visual indicators. I like to place these in a separate column, but if you’re happy for them to share column C then you can skip steps 4 and 5.
Step 4: For this we’ll need to add the ‘Sales’ field to the Values area again:
Step 5: Right-click the Sum of Sales2 column > Show Values As > % Difference From, and then same as before; Base field is Month and Base item is Previous.
Also give the column a new name. I’ll call mine ‘Change’, as you can see below:
Step 6: With any cell in the ‘Change’ column values area selected, go to the Home tab > Conditional Formatting > Icon sets. Here you can choose from different icons, but I’ll stick with the directional triangles:
This will apply the formatting to the selected cell.
Step 7: To apply the formatting to the whole ‘Change’ column, click the drop down beside the cell and select ‘All cells showing “Change” values for “Month”, as shown below:
It should now look like this:
Notice that there are some neutral/yellow icons. We want to change the formatting to simply show green up triangles for positive change and red down triangles for negative change.
Step 8: With any cell in the ‘Change’ column selected, go to the Home tab > Conditional Formatting > Manage Rules. This opens the Conditional Formatting Rule Manager dialog box (shown below). Select the icon set rule and click ‘Edit Rule’.
Tip: You can also double click the rule to open the rule editor window, shown below:
Edit the settings as shown in the image below. Note: I’m choosing to only show the icon in this column because column C already has the percentages displayed.
Your PivotTable should now look like this:
Tip: I’ve centered the Conditional Formatting icons using the cell alignment on the Home tab.
For the icing on top, add a Slicer and allow your user to interact with the PivotTable, as I’ve done for the Category field:
Tip: Just in case some months don’t have any data, I’ve set my Month Field settings to ‘show values with no data’ to ensure all months are listed:
Note: Months with no data will result in a #NULL! error for the % Difference From calculation. It’s not an issue here, but something to be aware of if you see #NULL! errors in your PivotTables.
Handling Errors in PivotTables
Now, obviously we don’t want our PivotTables littered with errors, especially if we’re presenting them in a report, that would just create unnecessary questions and we’re busy enough.
Thankfully we can supress errors in the PivotTable options; right-click the PivotTable > PivotTable Options > on the Layout & Format tab check the ‘For error values show’:
Excel 4.0 Macro Functions
Justine T
Hi! I’m trying to figure out how to calculate the percent difference when the previous year is a negative number and the current year is positive / Last year is negative & current year is negative.
If I were manually calculate it, I would just add some IF statement conditions, but I would love a way to do it in a pivot table – do you have any solutions?
Mynda Treacy
Hi Justine,
The calculation/formula is no different whether the prior year is a different sign to the current year. Please see this video: https://www.youtube.com/watch?v=FOBQHNzQmGA
Mynda
Henry May
Hi, when I change it from percentage to percentage difference from previous, it doesn’t create a new column it just replaces the old one. How do I have both on the table?
Mynda Treacy
Hi Henry, add the field to the Values area again, so there are two of the same fields in the PivotTable. Then change one of them to show the percentage difference from. Mynda
Juan
Showing the percentage change is great Thank you. What if I want to show the percentage point difference from previous month. I mean what is the percentage points gain or loose each month. Is it possible in a Pivot Table?
Mynda Treacy
Hi Juan, glad you found it helpful. There’s no built in way to calculate the difference from one percentage to the next, sorry. Mynda
Siddeeque
dear thank you so much for the grateful info
i want 100% in stood of Null value
eg : Jan sale =0
Feb Sales = 50
so while comparing the sales in feb its 100% sales increased
can you help on this?
Mynda Treacy
Hi Siddeeque,
You can’t force a PivotTable to calculate the percentage change incorrectly. I understand you want to represent that a change from 0 to 50 is positive, but mathematically it’s not 100%, therefore you can’t force a PiovtTable to do it, sorry.
Mynda
Adam
Why is the January change row blank?
Mynda Treacy
Hi Adam,
January’s change is blank because there is no month prior to January in order to calculate a change value.
Mynda
kritesh
Thanks for sharing for valuable feedback. This blog helps to get more information on excel where we can get information how to calculate mathematical function.
Mynda Treacy
Hi Kritesh,
Glad we can help. The different Excel Functions are covered here.
Mynda
Jonathan Moles
This is great. For some reason I can’t get it to work when the months span two different years as the first month of the year fails to work.
For example, if the data went from April 2018 to March 2019, then the cell for January 2019 change would be blank as December 2018 is in a different year.
Can you overcome this within the pivot table format?
Mynda Treacy
Hi Jonathan,
Looks like you also posted this question on our forum, which is the best place for help as you can also upload an Excel file, which I see you’ve done. We’ll help you there.
Mynda
Andrew King
Is there a chance that you could share the answer here as well? This is the same question that I have
Mynda Treacy
Hi Andrew,
Here is the forum question Jonathan posted.
Mynda
Bindu
Hi Mynda,
this is super helpful!! but i am stuck with one thing, i want to get a 100 % in the jn row instead of a blank, can be this be done?
Mynda Treacy
Hi Bindu,
In this example it wouldn’t make sense to have 100% in the total row because it’s calculating the change from one row to the next.
If you can share your example on our Excel forum we can take a look at what you’re trying to do and help from there.
Mynda
Toba
This is awesome. Especially the last tip, I did a report like this but because of the #NULLs, I had to answer soo many questions. Thanks so much for these tips.
Can you please do more on the different “show value as” options?
Mynda Treacy
Glad it was useful, Toba 🙂