Can I show a count of 0 in a pivot table

New Contributor


Let's assume my data is like this:

Category     Details

Pens             Super

Pens             Mediocre



Pivot will show

Pens    2

Pencils  1


I need to show Pencils as 0

5 Replies

@nilanprospecta Be sure that you are counting Details in the value field and that the cells that appear to be empty really are empty. A cell with a single space may look like a blank, but it will be included in a count as 1, just like you experienced it.


Right click on PivotTable and open PivotTable Options. Here check this setting


and show empty cells as zero


With that PivotTable looks like one at the bottom here:


@Sergei Baklan thanks for that. Unfortunately, the item, 'Show items with no data in rows' is grey in my case.




When I create the pivot, I checked, 'Add this data to the data model'. This enables the option mentioned.


Yes, but without adding to data to data model it shall show rows with data in any case, that only to select show blanks as zero.


Anyway, glad to know you sorted this out.