Re: Differences instead of sums in pivot table

  • Thread starter Thread starter Debra Dalgleish
  • Start date Start date
D

Debra Dalgleish

You can remove the Grand Total for rows, if you don't want to see it:
Right-click on a cell in the pivot table, and click on Table Options
Remove the check mark from Grand Totals for Rows, click OK

If you want to focus on the years, remove the Quarters and Months from
the pivot table layout.
Then, add another copy of the quantities field to the data area
Set it to show Difference From, based on (previous)
 
Well, I like the way the Grand Totals column summarizes the data, I would
just like it to be "Grand Differences" :)

I need to keep the data grouped by Years/Quarters/Months so that the users
can expand and contract (or select/deselect) those columns to be able to
change the data they're viewing.

Any way to accomplish that?

The Quarters and Months
 
Hi Shawn

The only way you could do it, would be to change the layout of your PT
slightly.
Make Type a Row field, followed by Quarters followed by Date.
Keep Years as a Column Field.
Turn off Grand Total by Row.
In column F, in row 4 type Difference.
In F5 enter
=IF(AND($A5="",$B5="",$C5=""),"",$E5-$D5)
Copy down as far as required.

You will see the totals for each year and the difference for each Product.
As you drill down, Quarters will open up down the page, as will Months.
Providing you extend your formulae down far enough in column F, the
differences will show as you expand, and will hide as you contract.

Use Conditional Formatting on Column F to achieve the same appearance as in
the Pivot Table itself.
 
Back
Top