Help with Excel Pivot Table Calc?
Discussion
I'm not a habitual Excel user, so forgive my lack of terminology.
I've set up a very simple table with some sales data
Year Month Sales
2025 Jan 1245
2025 Feb 3482 etc
This data goes back about 20 years.
I've set up a pivot table from that data with two "Slicers" that let me select by Year, and by Month, so i can compare different periods and years
Results look a bit like this (depending how many months and years i've selected in the slicers)
Jan Feb Grand Total
2025 1245 3482 4727
2024 1234 5678 6912
What I'm trying to do is to add another column to the right of grand total that will show me the difference in % from the previous year's figure.
So it would look a bit like this:
Jan Feb Grand Total Change
2025 1245 3482 4727 -25.4%
2024 1234 5678 6912 n/a
I've tried looking for instructions on how to do it on Youtube and stuff, but I don't know the terminology required to ask the right question, "How do I add a field in a pivot table to show the difference between two lines in the table" is far too wooly and useless.
Any help on how to do it, or where to look/what to ask would be gratefully received
I've set up a very simple table with some sales data
Year Month Sales
2025 Jan 1245
2025 Feb 3482 etc
This data goes back about 20 years.
I've set up a pivot table from that data with two "Slicers" that let me select by Year, and by Month, so i can compare different periods and years
Results look a bit like this (depending how many months and years i've selected in the slicers)
Jan Feb Grand Total
2025 1245 3482 4727
2024 1234 5678 6912
What I'm trying to do is to add another column to the right of grand total that will show me the difference in % from the previous year's figure.
So it would look a bit like this:
Jan Feb Grand Total Change
2025 1245 3482 4727 -25.4%
2024 1234 5678 6912 n/a
I've tried looking for instructions on how to do it on Youtube and stuff, but I don't know the terminology required to ask the right question, "How do I add a field in a pivot table to show the difference between two lines in the table" is far too wooly and useless.
Any help on how to do it, or where to look/what to ask would be gratefully received
boyse7en said:
Yes it can be helpful, but you need to know the right question to ask in the first place.
This is what you get if you ask your question:You’re super close! In a PivotTable this is done with Show Values As → % Difference From (Previous). Here’s a simple, reliable setup that gives you one “Change” column next to your total and still lets you slice by Year and Month.
Do this once
Put Year on Rows.
Use Month as a filter (not a column).
Drag Month from Columns into Filters (you can still use a Month slicer and select multiple months; the Pivot will total across the selected months).
Add Sales twice to Values.
You’ll see two value columns. Rename them to something like Grand Total and Change (right-click header → Value Field Settings → Custom Name).
Turn the second value into “% change vs previous year”
Right-click any number in the Change column → Show Values As → % Difference From…
Base field: Year
Base item: (previous)
Right-click the Change numbers → Value Field Settings → Number Format → Percentage (e.g., 1 decimal place).
That’s it. Your Pivot should now look like:
Year Grand Total Change
2025 4727 -25.4%
2024 6912 —
Notes & tips
The first year shown will have no “previous”, so it appears blank (you can leave it or add Conditional Formatting to display “n/a”).
Slicers still work:
Year slicer filters which years are shown.
Month slicer (with Month in Filters) controls which months are included in the totals.
If you select non-consecutive years, the “previous” means previous visible year in the Pivot’s sort order. For true year-on-year, include the immediately prior year.
Prefer Grand Totals: On for Rows (Design → Grand Totals) and turn off Column grand totals if you don’t need them.
If you really want months across the columns
Keep Month in Columns and still add Sales twice, set the second to % Difference From (previous year). You’ll get a %-change column for each month and one for Grand Total at the far right. If you only want a single “Change” column, the filter approach above is cleaner.
Good search terms for later
“Excel PivotTable Show Values As % Difference From previous year”
“Excel YOY percent change in PivotTable”
“PivotTable compare to previous item”
If you hit a snag, tell me how your Pivot is arranged (which fields are in Rows/Columns/Values/Filters) and I’ll tailor the clicks to match.
There are two ways to reference a column in a pivot table, within a formula
If you are entering a formula and you click on a cell in the pivot table, that will create a reference to the pivot table name and column name, and insert it into your formula
You can also refer from outside the pivot table to a cell using the usual “A1” reference style. That’s quick and dirty and won’t automatically grow with the pivot table, but if you doing ad-hoc analysis it is sometimes easier
If you are entering a formula and you click on a cell in the pivot table, that will create a reference to the pivot table name and column name, and insert it into your formula
You can also refer from outside the pivot table to a cell using the usual “A1” reference style. That’s quick and dirty and won’t automatically grow with the pivot table, but if you doing ad-hoc analysis it is sometimes easier
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff