How to disable to remove individual count under su

问题:

I'm quite new to programming, so I don't know if I am using the right words, BUT I'm creating a pivot table for my list of library books, and I want to hide all those 1s.

As you can see, I have those lines of 1s next each of the book titles under the subtotal, and I was wondering if there was a way to disable them because they look quite distracting. Thanks so much!!!

回答1:

Pivot table values will always show. Filtering, as suggested in a comment, is probably not what you want.

You could use conditional formatting to hide the 1's for the individual values. In the screenshot, I'm counting the "Title" column of the data. Select one of the "1" values, then click Home ribbon > Conditional formatting > New Rule

Click the option highlighted with the arrow. It will show your column name instead of "title".

Then click "Use a formula to determine ...." and enter the formula like below:

My selected cell is F6 and whatever cell you have selected needs to go into the formula box WITHOUT the dollar signs!

Next click the Format button and use either white font on white background, or a custom number format ;;;.

Confirm all the dialogs and enjoy the result. Note how the subtotal "1" for 30-Jun did not disappear, because the conditional format acts only on cells showing "Count of title" values for "Title", as ticked in my first screenshot.