Pivot Table returning "(blank)" instead of null

[JMH022]

I have a problem with a Pivot table returning the word “(blank)” instead of an actual blank cell. I found a post from 2009 with the same problem, but the only advice was to deselect the “(blank)” from the field’s dropdown.

While de-selecting the word "(blank)" may be an option that would work for some, it won’t solve my problem. I still need the other fields associated with the blank to be displayed.

The PT underlying data is in an Excel table, but I get the same result if I import the data into Access and link the PT to Access. The underlying data in this field does not hold any text or any value, so it should be a blank field in the PT. I just can't figure out why my PT is returning "(blank)" instead of an empty value.

Can anyone help on this?

 

[wigi]

If you right-click the PT and go to the PT options, you can set (Layout & Format) what you want to see in cells that are blank. Is that field filled in or so?

 

[JMH022]

wigi: Thank you for the suggestion; however, no. Both of the check boxes (blank fields and errors) are unchecked and empty. I had already tried changing the blank field property to return a hyphen or something, but it didnt do anything because technically, the word "(blank)" is being recognized by the PT as a text string.

This is baffling!

 

[wigi]

Can you upload the file (or a small representative part) on a filesharing website? Then helpers can have a look at the file.

 

[JMH022]

unfortunately not. My company blocks all those sites. It's fairly straightforward though. Sheet one has a table from, say (A1:J6000).

Column headers are
A> Country
B> City
C> Company Code
D> Participating (Y/N)
E> Status (Complete/Incomplete)
F> Code 1
G> Code 2
H> Code 3
I> Rule Definition
J> Notes

Columns G and J may or may not have a value on the record. All other columns always have a value.

On sheet 2, I have a pivot table to organize the data by country, company code and city. For the records where column G does not have a value, the text "(blank)" is showing up in the table. It makes the pivot table very messy to read.

HTH....

 

[wigi]

So in the PT, what is in the Report Filter, Columns, Rows, Values?

 

[JMH022]

Page:
Country
Company Code

Row:
City
Participating
Code 1
Code 2
Code 3
Rule Definition
Notes

Column:
Status

Value:
Count of Status

 

[wigi]

Ah OK, the Blanks will be in the rows, not in the value portion of the PT.

The only thing I can say, is that you should fill the empty cells in the data table (which is good practice anyway).

Empty cells are easily located with an Autofilter or the F5 command > Special > Blanks.

 

[JMH022]

In a perfect world, that is true. Unfortunately, I link into the file (read-only) and am not the owner. I would rather not have to "macro-up" to copy/paste values into a separate file, etc. if I dont have to. I just want the pivot table to recognize a blank as a blank and not "tell" me it is a blank.

 

[wigi]

I agree, but then you would prefer to see "nothing" (left blank) instead of the word "blank".

That does not make it any simpler to interpret the PT, I think.