Display #N/A instead of empty cells in Pivot Table

[thborng]

Hi everybody!

Is it possible to display the error #N/A instead of nothing (empty cell) when there's no data? The reason is that I'm trying to generate several plots directly from the Pivot Table and I want them to ignore the empty cells, and as far as I know the only way to do this automatically is by filling those cells with #N/A error value.

By the way, the Pivot Table is calculating Averages, so when there's no data it shows by default the error #DIV/0 which is NOT ignored by charts...

Please help!!

Cheers

 

[Slizer6893]

Umm I would use =IF(ISBLANK(A1),"#N/A", " ") Of course this is only useful on blank cells if you use it on cells with data it will display a blank.

 

[thborng]

I can't use formulas on cells that are in the Pivot Table because when I refresh it, all formulas are lost and get replaced by the new values

 

[Slizer6893]

I'm sorry I ignored the pivot table thing. Can you simply de-select blank data? I know this can be tedious but there is no fast solution to removing blanks, unless you can physically change the source data with formulas. So just deselect the blanks? I'll make some test data and see if I can re-create the problem.

 

[thborng]

Thanks for your answer, Slizer!

Yes, I could do that (as a matter of fact, I did) but it's not a long-term solution because when, in the future, new data comes into the spreadsheet, the previously de-selected cells (because they were empty) could get filled with information, and I can't be looking around for cells with new information each time I refresh the table... what can I do?

 

[Slizer6893]

Any chance you can give me an example of your source data. And I assume what you mean by new data being filled is an updated list of information. Such as a new output that goes over all the data. In which case I would have all Blanks filled with a zero. Which I could write a little VBA to do. Then the option to hide zeroes can constantly exist even with updated data.

Perhaps also we could try conditional formatting? I use it to block negatives on my one pivot table. Something like this

I select all of the sheet, table, whatever contains the data being used
New formatting rules, Select "Format Only cells with: Select cell value, I would use the equal to and set it to "blank"
Press Format
Select fonts and use a white color for fonts (Just makes it blank)
I wonder if it works in the graph...not sure honestly I never used it for a graph.

Ooh just read another post that they used custom format to ;;; (No need to change font) Try that!

 

[thborng]

Unfortunately, I can't give you the source data, but I can provide a screenshot of the (modified for confidentiality reasons) resulting Pivot Table.

What I need is that the empty cells (marked in yellow), instead of having no information in them, have #N/A (the actual error value, not text (as in "#N/A").

 

[Slizer6893]

My only solution will have to involve changing blanks into the text "#N/A" or a formula that gives out an error for blanks but like you said the document gets refreshed. I am a tad stumped here. I will mess around with it a bit more see if I can't come up with something.

 

[thborng]

Thanks! So will I. If I come up with anything, I'll let you know!

 

[Slizer6893]

Just curious why doesn't text work in this context? Graphs don't pick up #N/A value even if displayed as text. Since #N/A is the same value an error would output. However if you put N/A it will display N/A