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#):
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!