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
    2013-02-26T21:31:03+00:00

    Raise your hand if you wanted a cryptic solution. What is Herbert trying to communicate? I don't understand this.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-03-01T10:41:58+00:00

    Hi Herbert and All, 

    Can you please specify how to Show blank cell instead of (blank) ?

    Even with your answer i'm enable to find the option ?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-03-01T10:54:03+00:00

    ok, got it... 

    In the Table all blanks were replaced by an apostrophe.

    (Now i'm not sure how did you make them invisible ?)

    Nevertheless this a workaround more than a solution in my opinion.

    Now after a lot of time spent/wasted to find for a solution i can say there is none.

    Thanks anywhere.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-03-20T02:21:00+00:00

    Hi,

     

    If you do not wish to change the source data, then try this

     

    1. Select the pivot table (Ctrl+A while on any cell in the pivot table)
    2. Press Ctrl+H
    3. In the Find box, type (blank)
    4. In the Replace with box, type '
    5. Click on Replace All

     

    Hope this helps.

    Is there a way to do this against a PowerPivot Table?  When I tried the method above, the  ' did replace the (blank) but displayed as is rather than with a blank field.

    Thanks,

    Amy

    Was this answer helpful?

    0 comments