Keep Row Grand Total with Slicer

BloodyBill

New Member
Joined
Oct 5, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
I have a pivot table with many columns. Imagine 50 salespeople (not the 4 shown). I have row grand totals.

1625246003275.png


I'd like to compare Bill's sales (10 apples) against the row total (18 apples) side-by-side.

I could hide the other columns, which preserves the calculation of the grand total. The problem with that is that it is clunky and inconvenient with so many salespeople/columns.

1625246138974.png


I'd rather just click Bill on a slicer, but that removes everyone else's sales numbers from the grand total. Now it looks like Bill sales of 10 apples were the same as the total sold (instead of 18).

1625246200789.png


Is there a way I can...?
  • keep a grand total column with an actual grand total
  • and use a slicer to conveniently hide everyone but Bill

I'm wondering if there's a solution that uses a helper column separate from the pivot itself?

Thanks,
Bill
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,031
Office Version
  1. 365
This is a good time for a "Calculated Item" in the pivot table.

1) Click on cell B4 (really any name in the header row)
2) Go to PivotTable Analyze --> Fields, Items, and Sets --> Calculated Item
3) Name your new field "Total" or similar, and set the formula = Bill + Bob + John + Tom + ... (This is tedious, but you only have to set this up once)
4) Your new Total should appear in your pivot table, but note that the "Grand Total" should now be removed as it double counts this total
5) With your slicer, select both "Bob" and the new "Total" field you have set up.
 
Solution

BloodyBill

New Member
Joined
Oct 5, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
This is a good time for a "Calculated Item" in the pivot table.

1) Click on cell B4 (really any name in the header row)
2) Go to PivotTable Analyze --> Fields, Items, and Sets --> Calculated Item
3) Name your new field "Total" or similar, and set the formula = Bill + Bob + John + Tom + ... (This is tedious, but you only have to set this up once)
4) Your new Total should appear in your pivot table, but note that the "Grand Total" should now be removed as it double counts this total
5) With your slicer, select both "Bob" and the new "Total" field you have set up.
This is great...except I run into a character limit. Too many people with long names.
BTW, if I figure a way around this (and I might, by using last names) I will have to remember to amend the formula as people come and go. Right?
 

BloodyBill

New Member
Joined
Oct 5, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
This is great...except I run into a character limit. Too many people with long names.
BTW, if I figure a way around this (and I might, by using last names) I will have to remember to amend the formula as people come and go. Right?
EDIT: No, still too many characters with just last names.
 

BloodyBill

New Member
Joined
Oct 5, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
EDIT: No, still too many characters with just last names.
Ok, I switched the names to initials. That solved the 255 problem. Otherwise, Oaktree solved it with the Calculated Item.
THANKS!
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,031
Office Version
  1. 365
Glad that worked for you.

You're correct about having to amend the formula as new people are added. One check you could do there is to (presumably in a separate "check" pivot table that is set up where it's source is "another pivot table" so they share the same cache) compare the sum of the pieces against the grand total/2. If the sum of the pieces (including the "Total" calculated item) doesn't equal the Grand Total/2 (because of the "Total" double count), you know you need to change the "Total" calculation because it's missing a newly added person.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,127
Messages
5,768,275
Members
425,460
Latest member
Astros1243

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top