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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2012-08-05T15:16:41+00:00

    All--when I use the method outlined above my pivot table displays the literal ' instead of an empty cell.

    Also, this is a dynamic sheet that is connected to outside data. When there is a data refresh what will happen?

    Thanks!

    I'm using Version 10.

    Was this answer helpful?

    0 comments
  2. Ashish Mathur 102.4K Reputation points Volunteer Moderator
    2012-08-06T02:09:00+00:00

    If there is an external data connection, my method will not work.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-08-24T15:16:27+00:00

    This works super well and saves the need to do a find/replace each time more data gets added to the pivot table. I do have an external data connection to a SharePoint List and it's still working fine.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2012-08-27T07:11:45+00:00

    Instead of replacing (blank) you can actually filtering (blank) using label filter - Does Not Contains (Blank) anytime you refresh the PT will be updated.

    tks,

    harry

    Was this answer helpful?

    8 people found this answer helpful.
    0 comments