B. Clay Shannon B. Clay Shannon - 1 year ago 65
C# Question

How is it (is it?) possible to prevent the PivotTablization of data to result in "#DIV/0!" and such when there is missing data?

I am generating a PivotTable which is comprised of monthly data for items (how many of this sold, how many of that sold, and related statistics). In some cases, though, there are no sales for a particular item in one or more months. In that case, there is no record that contains a "0" value, the record simply does not exist for the non-existent sale.

When such an item's data is displayed, the nonexistent data wreaks havoc. The screen shot below shows what happens when an item does have data for both months (APPLES, GALA 5#), and when it doesn't (APPLES, GALA 5#):

enter image description here

So as you can see, when the data is PivotTablized, rather than put a "0" in "Total Packages" and "Total Purchases" it leaves those cells blank and inserts a "#DIV/0!" in the "Sum of Average Price" cell - and then a "." in the "% of Total" cell.

This is not helpful or pleasing to the eye. I would like either a series of dashes in those cells or zeroes.

A workaround would be to create a row for each currently non-existent one in the source pivot data, but that seems unnecessary and kludgy in the extreme - I would have to determine which items had "missing" months and then supply them with those 0 values.

Is this really what I need to do? Please say it ain't so!


The proffered answer led me to experiment with what would happen by changing the PivotTable options Format vals. With this data to begin:

enter image description here

...I entered a question mark for err vals, and a dash for empty cells:

enter image description here

This caused the data to look like so:

enter image description here

Better, but still not quite what I want/expect. Since it's better, at least, I'd settle for a programmatic way to accomplish the same thing.

I wasn't expecting to see the "?" following some of the "good" values, though...

So what I may need is the programmatic equivalent of "for empty cells, show a dash" (which converts to 0 for int and $0 for decimal).

Answer Source

Pivot Tables can hide errors.

Right-click on your Pivot Table and select Pivot Table options. On the Layout and Format tab, under Format, look for For error values show.

Pivot Table error handling

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download