Can I show a count of 0 in a pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-2289442%22%20slang%3D%22en-US%22%3ECan%20I%20show%20a%20count%20of%200%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2289442%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20assume%20my%20data%20is%20like%20this%3A%3C%2FP%3E%3CP%3ECategory%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BDetails%3C%2FP%3E%3CP%3EPens%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSuper%3C%2FP%3E%3CP%3EPens%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BMediocre%3C%2FP%3E%3CP%3EPencils%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPivot%20will%20show%3C%2FP%3E%3CP%3EPens%26nbsp%3B%20%26nbsp%3B%202%3C%2FP%3E%3CP%3EPencils%26nbsp%3B%201%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20show%20Pencils%20as%200%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2289442%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2289776%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20show%20a%20count%20of%200%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2289776%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1036454%22%20target%3D%22_blank%22%3E%40nilanprospecta%3C%2FA%3E%26nbsp%3BBe%20sure%20that%20you%20are%20counting%20%3CEM%3EDetails%3C%2FEM%3E%26nbsp%3Bin%20the%20value%20field%20%3CSTRONG%3Eand%3C%2FSTRONG%3E%20that%20the%20cells%20that%20appear%20to%20be%20empty%20really%20are%20empty.%20A%20cell%20with%20a%20single%20space%20may%20look%20like%20a%20blank%2C%20but%20it%20will%20be%20included%20in%20a%20count%20as%201%2C%20just%20like%20you%20experienced%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2290986%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20show%20a%20count%20of%200%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2290986%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1036454%22%20target%3D%22_blank%22%3E%40nilanprospecta%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERight%20click%20on%20PivotTable%20and%20open%20PivotTable%20Options.%20Here%20check%20this%20setting%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20349px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275535i57172BC403D6FC1D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20show%20empty%20cells%20as%20zero%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20371px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275536iDFBE96655A686A02%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWith%20that%20PivotTable%20looks%20like%20one%20at%20the%20bottom%20here%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20247px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275537i826C442E6BCD09D8%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

 

Let's assume my data is like this:

Category     Details

Pens             Super

Pens             Mediocre

Pencils         

 

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.

@nilanprospecta 

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

image.png

and show empty cells as zero

image.png

With that PivotTable looks like one at the bottom here:

image.png

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

 

nilanprospecta_0-1619480632538.png

 

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

@nilanprospecta 

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.