Remove (blank) from pivot table

Anonymous
2011-02-25T12:16:56+00:00

Hi,

My source data has empty cells.  When I PT this data - the PT shows "(blank)" for the empty cells.  How can I keep the cells "empty" in the PT.  I do not want to manually filter and uncheck (blank) as this removes other data which is needed.

TIA,

PS

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2011-02-25T14:15:06+00:00

Excel 2007/2010 PivotTable

Show blank cell instead of (blank)

in RowField/ColumnField item labels.

http://c3017412.r12.cf0.rackcdn.com/01_23_11.xlsx

If you get *.zip, don't unzip, just rename *.xlsx

Was this answer helpful?

200+ people found this answer helpful.
0 comments

46 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-07-24T15:47:03+00:00

    I had the same problem but I figured out how to remove "(blank)" without having to filter it out every time.

    1. Select the data for your pivot then format it as a table
    2. Change the data source of your pivot to be only the table. (either type in the name of your table or when you hover your mouse at the top of a column by the name there should be a small black arrow pointing down; click and select all the columns you want to include)
    3. Click Ok

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-02-05T19:36:56+00:00

    This is, in fact, the BEST solution I've seen so far!

    Thanks so much mHeL!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-12-17T16:17:11+00:00

    This doesn't work so well if you have any color in your table. I found that instead of formatting the font color use Format Number, Select Custom, In the field Type: General, delete General and enter ;;;

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-12-17T07:04:27+00:00

    thanks, it helped me...spent many hours searching for this.

    Was this answer helpful?

    0 comments