pivot-chart | fast implementation of web pivot table | Business library

This is a good question and to answer it correvtly will need access to your original data (where the pivot takes them). However, I will give you a general case and I think you could change it a little to fit your needs.

The problem with pivot tables is that you can’t use them to report on data that is not there. So what you can do to fix that is to add the Missing Data.

So the first step is to add some dummy records to the data. You could add one dummy record for each category that is missing.

Now, when the pivot table is refreshed, the missing categories should appear, but with blank cells in the Count. That’s close to what we’d like, but it would be preferable to show a zero there.

We should give the pivot table something to count so you could add "X" or "-".

With that change, a number shows up in the Count of ID column, but we want the number to be 0, not 1.

Now you should change change the Summary Function

The worksheet functions include COUNTA, which counts all non-blank cells in a range, and COUNT, which only counts cells with numbers. There are similar functions in a pivot table, but it’s confusing, because the pivot table COUNT is like the worksheet COUNTA, and counts text too.

Instead of using Count, you can change the summary function to one that only counts numbers. Then, those characters in the column you count will not be counted.

To change the summary function:

1. Select a cell in the Count column of the pivot table.
2. On the Ribbon, under PivotTable Tools, click the Options tab
3. In the Active Field group, click Field Settings

In the Value Field Settings dialog box, in the list of functions, click on Count Numbers, then click OK

The pivot table now shows a zero for the categories with missing data, instead of a blank cell or an incorrect count.