Pivot Table, Display Subtotals but not values

[Greg Stough]

Hello, and thank you for reading this post. I'm creating an org chart outline to account for employees during an emergency. The goal is to associate each supervisor (Reports To) with subordinate staff(Name) that reports directly to that supervisor. From this table check lists will be created to insure everyone is accounted for.

The pivot table structure is simple, but I'm unable to display the subtotal of how many employees report to each supervisor, without also displaying a "1" in the subtotal column after each employees name. I want this to be simple and uncluttered, and the 1 are unneeded. I've tried a bunch of different setting combinations, but are unable to create what I want. My question is this...How do I display subtotals without displaying the value (each value is 1). Below is an example.

Pivot table fields are is set as follows

• Rows - "Reports To", then "Name"
• Count - Name

What I currently have is...
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Reports To[/TD]
[TD]Name[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Susie Jones[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Bob Johnson[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Fred King[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Alice Brown[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Tina Turner[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jill Hoffman[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Terry May[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Anthony McGee[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


What I want is....
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Reports To[/TD]
[TD]Name[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Susie Jones[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Bob Johnson[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Fred King[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alice Brown[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Tina Turner[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jill Hoffman[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Terry May[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Anthony McGee[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any help you can provide is greatly appreciated.

Thanks!

 

[fhqwgads]

on your pivot table field list, click your count value and select value field settings, then go to number field settings in the bottom left. select custom number format and enter

[>1]General;;;

hope that worked

 

[Greg Stough]

Thank you very much for your suggestion. That worked nicely, and as suggested. I had thought about conditional formatting, but decided against it because there are several Supervisors with a single person reporting to them. So those the subtotal for those supervisors is not displayed. It's not a huge huge issue, since they only have one person reporting to them, but not ideal. If I'm unable to come up with a better solution I'll use your idea. Thanks!

 

[fhqwgads]

oh right sorry i hadn't thought of that.

maybe something using the pivot table style? when you have your pivot table selected, on the design tab select your style and rightclick duplicate it so you can modify it. in the modify style window select whole table and change the font colour to white so they appear invisible. then select first column and make the font black, then click ok. i'm really bad with table elements so the column with your names may not be the first

 

[Greg Stough]

No problem. I forgot to mention that some Reports To has only one direct report. I can use conditional formatting, but was hoping there was a setting I could change instead. Perhaps not. The formula I use is
1. Select all cells of the Total column.
2. Conditional Formatting>New Rule
3. Apply Rule to [cells I selected above]
4. Select Rule type - "Use a formula to determine which cells to format"
5. In the formula field (Format values where this formula is true) I entered "=ISBLANK(C5)", which is the first Reports To cell
6. Then I set the font color to match the background, and apply the formula
7. The 1 beside each name cell then disappear

The table design is "outline form" with "subtotals at the top of the group". The Reports To (col C) is listed at the top of the group, with all direct reports starting one row down in the column to the right (col D). Since the Reports To cells (col C) to the left of the Names (col D) are blank, the formula resolves to TRUE, which changes the font color, and the 1 in the Total column disappear. As soon as the next supervisor name is listed in the Reports To column, that row is no longer blank so the total number in that row displays normally. But as the conditional formatting formula is applied to the following row it's empty and the formula is once again true. That works well enough, but it's not quite as easy as changing a setting or something.

If I can figure out a combination of settings I'll use those, if not I'll use conditional formatting. Hopefully that remains in place as the source data is replaced regularly with updated data.

Thanks again for your help.

 

[fhqwgads]

sorry when i said pivot style i meant this bit
i hope this image link works
https://exceljet.net/sites/default/.../How to create a new pivot table style_SS.png

this should also work with outline form table design. modify the whole table font to be white and then the first column font to be black

 

[fhqwgads]

i should clarify, "whole table" and "first column" aren't referring to a specific range on your sheet, they're sections on the modify pivot table style window
http://ptgmedia.pearsoncmg.com/images/chap3_9780789748751/elementLinks/03fig21_alt.jpg