Count Missing Pivot Table Data as Zero

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.

pivotcount01

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.

pivotcount02

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.

pivotcount03

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.

pivotcount04

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.

pivotcount06

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.

pivotcount07

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

pivotcount08

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

pivotcount09

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

pivotcount10         

Thanks AlexJ, for sharing this solution to the problem of missing pivot table data.

______________

You may also like...

3 Responses

  1. Caroline Baillard says:

    Hi
    Thank you so much for your tutorials. They are really well done.

    In Pivot tables, I would like to show blank and not zeros. I am interested in Sums and not Counts.
    Do you see a way?

    Thank you in advance

    Caroline
    Geneva, Switzerland

    • scott says:

      Caroline, i’m in the same boat.

      If you’re trying to calculate an average based in pivot data, then you won’t ever get a proper calculation because the blank cells don’t count–if those cells said zero the true average would appear and it would be lower.

      In my situation, the pivot table was just functioning as a table for me to make averages based on so the way it looks or how big it is isn’t important.

      PIVOT TABLE
      |A | B |C |D |E |F
      1 |Date | Day of week | Is a Week day | Hour 8 | Hour 9 |=A1
      2 |1/13 | 5 | YES | 20 | 30 |
      3 |1/14 | 6 | YES | | 27 |

      then, right next to the table, I made a new table. Every cell in the pivot table was just repeated. so if “date” is in A1, in the new table the formula would be =A1.

      Now all of the blank cells are zeros. The new cell for D3, would be =D3, and the value displayed would be 0.

      TO deal with the fact that the pivot table might grow over time as you use data, since I want an average that meets 3 conditions (only average if there is a match on Hour, Date and if “Is a Week day”=”YES”. So I make my second table really big to account for the fact that the first table will grow, but i’m not averaging zeros that don’t exist because there’s no match on the yes column.

      I hope this helps.

  2. Israel says:

    Hi,

    Is this still not possible using vba code instead?
    In other words, is it possible to set up a macro that manually sets the pivot table values to zeros if a record is not found?

Leave a Reply

Your email address will not be published. Required fields are marked *