Google Sheets pivot table add column for value with no data

Question

I have survey data on teacher quality with possible responses being 1-4. No one reported 1, so it won't automatically show in the pivot table I am trying to create.

This is what I'm working with. Again, I want to add a "1" column, and it should display 0% or a blank cell.

I have looked into calculated fields and messed around with functions and also dummy variables (an additional column listing 1-4 to insert into the pivot table), but I haven't been able to crack the code myself.

Answer 1

In order to include zero values in the pivot table, create results that can be included in the pivot table.

DATA

• Column A & B are unchanged
• Column C Cell C2 :
  • =countif($A$2:$A$16,"="&B2)/COUNT($A$2:$A$16)
  • copy down to cell C3, C4 and C5

PIVOT TABLE

• Select B1:C5
• Format as per image below, including manual edits

---

SAMPLE DATA

PIVOT TABLE EDITOR

Answer 2

Alternatively you could try a formula-based approach for your ideal output scenario:

=let(Σ,sequence(1,4), Λ,A2:A,
     arrayformula({Σ;to_percent(countif(Λ,Σ)/counta(Λ))}))