Pivot table doesnt recognise blanks as blanks

[AgentSilverfern]

I have four columns of data in sheet. The first and second columns contain numbers that were entered manually. Each cell in the third column contains a formula that looks like this...

=IF(OR(AB4="",AB4=0)=TRUE,"",IF(AB4>=AA4,AB4-AA4,"ERR"))

Thus returning a "" value whenever a "0" or a "" is contained in column AB.

I then generated a pivot table from the sheet these columns are contained in and specified that all blanks should be displayed as dashes in the pivot table options.

This worked for all the columns in this sheet except for the aforementioned third column that contains formulas, which instead displayed zeroes. Somewhat perplexed I decided to copy and paste special the column as values only into a new column (my fourth column). I inserted the new column into the pivot table data area and hit refresh, but still the pivot table displayed zeroes and not dashes.

I have since tried using a macro to clear the contents of all blanks cells in the column or by simply using the goto special blanks option and clearing contents - all to no avail. The pivot table still doesnt see blanks. The only way in which it has recognised that the cell is a blank is if I manually select each cell one at a time and hit delete on the keyboard.

I don't understand it? Can anyone help me?

 

[Andrew Poulsom]

A null value "" returned by a formula is not the same as a blank cell, because the cell contains something (a formula). Try changing your formula so that it returns zero instead of "" and format the cells in the pivot table as eg "#,##0_);(#,##0);-" (no quotes).