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
    2014-08-19T15:59:01+00:00

    Click on the drop down arrow in your pivot table.

    Then select label filters.

    select equals and then use the drop down to reselect notequals.

    then type in (blank).

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2014-05-29T07:43:40+00:00

    The BEST way is just to type Alt+255 on one of the containing (blank) cell on pivot area to eliminate this word and to make it empty cell.

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments
  3. Anonymous
    2015-03-19T11:06:06+00:00

    None of these suggestions worked for me and its all quite fiddly! Actually the easiest way to do it if you have a chart with (blank) is to click on the filter. Somebody said that unchecking the blank box is annoying because when you add new data you have to go in and check the box - correct! However in filter, go to:

    Label Filters

    Does not equal...

    type in the box (blank)

    this will remove the blank, and will allow other data to be added without having to play with the filters. NB: make sure the All box is clicked to make sure it will automatically update.

    As a general rule, if you are playing around to look at figures one off, use the tick boxes, if you want to only show a couple, or remove a couple from future reporting, use the "filter by label"

    Hope this helps!

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  4. 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