# Keep Row Grand Total with Slicer

#### BloodyBill

##### New Member
I have a pivot table with many columns. Imagine 50 salespeople (not the 4 shown). I have row grand totals.

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.

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).

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

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

#### Oaktree

##### MrExcel MVP
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.

#### BloodyBill

##### New Member
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
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
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

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.

Replies
0
Views
135
Replies
0
Views
455
Replies
3
Views
186
Replies
0
Views
120
Replies
1
Views
284

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.

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.

### Which adblocker are you using?

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

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