Showing the percentage change in a pivot table tells you at a glance whether this month is better or worse than the last one. Excel can do it without a single formula: the built-in % Difference From option compares each month with the previous month. In this tutorial you group daily sales by month, add Sales Change% and Revenue Change% columns, mark every change with green and red triangles, and add a Location slicer so you can switch between India, US and UK in one click. It works in Excel 2010 and every later version.
⬇️ Download the Practice File
Data sheet (543 rows) plus the finished pivot and slicer · .xlsx (no macros) · Excel 2010 and later
Watch the Video Tutorial
In the video PK builds the pivot from the raw data, adds the change columns with % Difference From, sets up the icon-set rules, shows the plain Difference From option and finishes with a three-column Location slicer.
What You Will Build
- A monthly pivot that groups 181 days of sales into January to June.
- Sales Change% and Revenue Change% columns that compare each month with the previous one.
- Green up and red down triangles that update when the data changes.
- A Location slicer to switch between India, US and UK.
The Sample Data
The Data sheet has four columns: Date, Location, Sales and Revenue. There is one row per day from 1 January to 30 June 2018 for each of three locations, 543 rows in total.
In the practice file the pivot reads from a named range, Dynamic_Range, so new rows are picked up on refresh:
=OFFSET(Data!$A$1,,,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))COUNTA counts the filled cells in column A (rows) and row 1 (columns), and OFFSET returns a range of that size starting at A1.
Part 1 – Build the Monthly Pivot Table
Step 1: Insert the pivot table
Click inside the data and go to Insert > PivotTable (PK uses the old shortcut Alt, D, P). Put it on a new worksheet. Drag Date to Rows and Sales and Revenue to Values.
Step 2: Group the dates by month
Right-click any date in the pivot, choose Group, select Months and click OK. The 181 daily rows collapse into Jan to Jun.
Your data covers one year only. If it spans several years, select Months and Years together, otherwise January of every year is added into one row.
Step 3: Tidy the layout
PK removes the gridlines (View tab), applies a pivot style from the Design tab, sets the font to 10 and makes the header bold. He also renames Sum of Sales to Sales with a space before the word. Without the space Excel says the PivotTable field name already exists, because the source data already has a field called Sales.
Part 2 – Add Percentage Change in the Pivot Table
Step 4: Add Sales and Revenue a second time
Drag Sales into Values again, under the first one, and do the same for Revenue. You now have two identical columns for each: the first keeps the totals, the second becomes the change column.
Step 5: Show values as % Difference From
Right-click any number in Sum of Sales2 and choose Show Values As > % Difference From. (You can also open Value Field Settings and use the Show Values As tab.)
Step 6: Choose Date and (previous)
Set Base Field to Date and Base Item to (previous), then click OK.
Repeat steps 5 and 6 for Sum of Revenue2, then rename the two columns to Sales Change% and Revenue Change%.
How the calculation works
% Difference From with (previous) calculates, for each month:
(This month - Previous month) / Previous month- For India, February sales were 3,048 and January sales 3,102: (3048 – 3102) / 3102 = -1.74%.
- February revenue was 82,346 against 74,361 in January: (82346 – 74361) / 74361 = +10.74%.
- January has no previous month, so its cell stays blank. The Grand Total row is blank too, because a total has no previous item.
Because the comparison follows the order of the row items, keep the months in date order. Grouped months are sorted correctly by default.
Part 3 – Up and Down Triangles with Conditional Formatting
Step 7: Create the icon-set rule
Click any cell in Sales Change% and go to Home > Conditional Formatting > New Rule. Set it up like this:
- Apply Rule To: All cells showing “Sales Change%” values. This keeps the rule on the whole column when months are added or filtered.
- Rule type: Format all cells based on their values.
- Format Style: Icon Sets, with the three triangles icon style.
- Green triangle: when value is
>0, Type Number. - Yellow dash: when
>=0, Type Number, so it appears only for exactly 0%. - Red triangle: everything below 0.
Changing Type from Percent to Number matters. With the default Percent type, Excel splits the icons by where each value sits between the column’s minimum and maximum, not by whether the change is positive or negative.
Step 8: Repeat for Revenue Change%
Create the same rule for Revenue Change%. PK opens the rules manager with the shortcut Alt, O, D and clicks New Rule from there.
Part 4 – Add a Location Slicer
Step 9: Insert and style the slicer
Select the pivot and go to PivotTable Analyze > Insert Slicer (the tab is called Analyze or Options in older versions), tick Location and click OK. In the Slicer tab set Columns to 3 so the buttons sit in one row, pick a style, and if you want a cleaner look right-click > Slicer Settings and untick Display header.
Bonus: Show the Change as a Number
If you want the difference in units rather than percent, use Show Values As > Difference From with the same Base Field (Date) and Base Item (previous). In the video, with all locations, February shows -929, which is 8,580 minus January’s 9,509. You can show both columns side by side.
Common Errors and Fixes
PivotTable field name already exists
You tried to rename a value field to exactly the name of a source column, such as Sales. Add a leading space (” Sales”) or use a different name such as Total Sales.
#N/A or blank values in the change column
The first item has no previous item, so it is always blank. If you see #N/A in other rows, check that Base Field is the same field you have in Rows (Date) and not a field that is missing from the layout.
The change compares the wrong months
(previous) means the previous row in the pivot, not the previous calendar month. If you sorted the months by value, the comparison follows that new order. Sort the rows by date again.
The icons do not match the sign of the change
For example, a small drop shows a green or yellow icon. The icon thresholds are still set to Percent, so Excel ranks values within the column instead of checking whether they are above or below zero. Change both Type boxes to Number with a value of 0.
Tips
- Use Base Item: (next) to compare with the following month instead.
- Choose % Of with Base Item set to a fixed month (for example Jan) to index every month against a starting month.
- Add Years to the grouping and put Years in Columns to compare the same month across years.
- Tick Show Icon Only in the rule if you want a compact trend column next to the numbers.
- Microsoft lists every Show Values As option in Show different calculations in PivotTable value fields.
Want It Ready-Made?
For a styled pivot report that is ready to use, see the Stylish Pivot Chart with Slicer in Excel on NextGenTemplates.com, or browse the full range of Excel dashboards. To master grouping, Show Values As, slicers and dashboards from the ground up, join the video course Excel Pivot Tables & Dashboards: Basic to Advanced.
Related Tutorials
- How to Insert a Pivot Table in Excel (Step by Step)
- Group Dates, Numbers and Text in a Pivot Table
- Pivot Table Value Field Settings: Sum, Average and %
- Icon Sets in Excel: Traffic Lights and Thresholds
- Slicers in Pivot Table: Insert, Connect and Columns
Frequently Asked Questions
How do I show percentage change in a pivot table?
Add the value field a second time, right-click it and choose Show Values As, % Difference From. Set Base Field to your date field and Base Item to (previous). Each row then shows the change from the row above.
Why is the first month blank?
The first month has no previous month to compare with, so Excel leaves it empty. The Grand Total row is also blank for the same reason.
Do I need a formula or a helper column?
No. % Difference From is a built-in pivot calculation. It updates when you refresh the pivot or change the slicer, with no formulas outside the pivot.
How do I show the change as a number instead of a percentage?
Use Show Values As, Difference From with the same Base Field and Base Item. It returns the plain difference, for example -929 units.
Can I compare with the same month last year?
Yes. Group the dates by Months and Years, put Years in Columns and set the Base Field to Years with Base Item (previous). Each month is then compared with the same month a year earlier.
About the Author
PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, Power Query and Power BI since 2016 on his YouTube channel.
Conclusion
Showing percentage change in a pivot table takes three moves: add the measure twice, set the copy to % Difference From with Date and (previous), and add an icon-set rule with Number thresholds. Add a slicer and you have a month-over-month report that recalculates for any location in one click. Download the practice file and try it with your own data.
