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: Newest
Most helpful Newest Oldest
  1. Anonymous
    2015-10-01T22:44:14+00:00

    Well done sir!!

    Very Creative Work around that does not require editing the data; Perfect for those of us parsing data on the fly.

    Was this answer helpful?

    0 comments
  2. 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
  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
    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