How can you get missing data to show up in your Excel pivot table, showing a count of zero? AlexJ encountered this problem recently, and sent me his solution, to share with you.
One problem with pivot tables is that you can't use them to report on data that is not there. What if you need to report on a category of data when that has not been reported to your data set?
Health and Safety Report
For example, in this Health and Safety incidents data, we record the date, department and type of report for each incident.
This data is from the first quarter, and when we create a pivot table from the data, two of the departments are missing, because no incidents occurred there. That's good news, of course, and you'd like to highlight that, by including the departments in the pivot table.
Add the Missing Data
The departments will only appear in the pivot table if they're in the source data, so the first step is to add some dummy records to the data. You could add one dummy record for each department, or just the missing departments, as shown below.
Now, when the pivot table is refreshed, the missing departments appear, but with blank cells in the Count of ID column. That's close to what we'd like, but it would be preferable to show a zero there.
We don't want to create an ID number for these dummy items, but the pivot table can't count the blank cells. To give the pivot table something to count, AlexJ entered a letter in the ID column, instead of a number.
With that change, a number shows up in the Count of ID column, but we want the number to be 0, not 1. Close, but those departments, don't want dummy records besmirching their incident-free safety records.
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 letters in the ID column won't be counted.
To change the summary function:
- Select a cell in the Count of ID column of the pivot table.
- On the Ribbon, under PivotTable Tools, click the Options tab
- 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 departments with missing data, instead of a blank cell or an incorrect count.
Thanks AlexJ, for sharing this solution to the problem of missing pivot table data.
______________
