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


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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.