How to prevent all-filtered-out row label values from appearing in a pivot table?

Question

I have a pivot table in MS Excel. For the same of example, suppose the basic data has 3 columns:

• Name: A string.
• CountMe valued either 0 or 1.
• Year an integral number.
• Tokens the data we're aggregating

I use my pivot table to sum people's tokens over all years. So

• Name is a 'Row Label'.
• CountMe is used in a 'Report Filter' (requiring CountMe=1)
• Year is not used.
• Tokens is a 'Values' field, with a 'Sum' aggregation.

Now, suppose person "John Smith" has a few rows in the data table, but all of them have CountMe=0. There are two possibilities:

1. I see a row for "John Smith" in the table, with Sum of Tokens = 0, or a missing Sum of Tokens.
2. There is no row for "John Smith" in the table..

MS Excel 2010 opts for option 1, but I want to have option 2. How can I achieve this? (I already have an ugly workaround, I'd like something relatively elegant.)

Answer 1

In the field settings for "name", under Layout & Print, make sure that "Show Items with no data" is NOT checked (this should also be the default).