Pivot tables are great at summarizing data, but sometimes your data has gaps, and the pivot tables can’t report on numbers that don’t exist. In today’s example, we have health and safety data for the first quarter. Two departments did not have data for that period, and are not listed in the data, but we’d like the report to show zero incidents. Keep reading, to see how to count missing pivot table data as zero.
Health and Safety Data
Here is a screen shot of the first few rows of the health and safety data for the first quarter report.
If you click the sort/filter arrow on the Department heading, only 4 departments are listed. There is no data for the Administration or Shipping departments.
If you create a pivot table from the source data, with Department in the Row area, and Count of ID in the Values area, only the four departments in the source data are included in the report.
Add the Missing Departments
In the pivot table report, we’d like to show the missing departments, with a count of zero incidents. It’s good for company morale if we feature the departments that are doing well!
To make that happen, add a dummy record for each missing department in the source data.
- Insert 2 rows at the top of the source data table
- Put the department names in Department column
- Add an X in the ID column, for each dummy record
Update the Pivot Table
After adding the dummy records, update the pivot table, to see the missing departments.
If the ID field used the default Count function, the report will show a 1 for Administration and Shipping, instead of zero. That happens because the function is counting text entries, as well as numeric entries.
Instead, use the Count Numbers summary function, to show those values as Zeros. To change that setting:
- Right-click one of the numbers, and click Summarize Values By, and click More Options.
- Then, in the list of functions, select Count Numbers.
With the Count Numbers function selected, the pivot table shows the missing departments, with zeros as the count of incidents.
Other Type of Missing Data
There is another type of missing data in pivot tables – items that are in the source data, but not showing when specific filters are applied. For example, a product might have sales to some customers, but not all customers.
To show all products, no matter which customers are selected, change the field setting for the Customer field, to show items with no data.
The video below shows the steps, and there are written instructions on my Contextures website.
Download the Sample File
Go to the Pivot Table Field Settings page on my Contextures website, to download the sample file with health and safety data. The zipped file is in xlsx format, and does not contain any macros.
______________________
Count Missing Pivot Table Data as Zero
________________