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

 

[thborng]

If I use the built-in "Show empty cells as:" functionality of Pivot Tables, and in the field I type #N/A, it displays it as text, not the error value. Therefore, when plotting, it takes it as value of zero instead of ignoring it. I just tried it

I actually used the ISNA() function to test it and it returns FALSE because the referenced cell contains the text #N/A, not the error...

 

[Slizer6893]

Dang you are right. Its odd that pivot charts handle that differently than normal graphs.

 

[Slizer6893]

Alright lets try another route here. Can you give me an example of how source data would be presented. Obviously not using any data that is sensitive. But just test data that you can create. I'm wondering if we can change the way this data is handled and then inputted into the pivot table. I ran a few different tests that all worked but since I am unsure how the source data is being fed into the pivot table I can't do much.

We will solve this!

 

[Masten]

Hi guys, my first post here. I also had this issue with blank cells in a PivotTable being plotted as 0s in my graphs. I solved it by adding a calculated field to the pivot table, that is =if(value data=0;na();value data). It works great!