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:
- I see a row for "John Smith" in the table, with Sum of Tokens = 0, or a missing Sum of Tokens.
- 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.)