how to make pivot table show all labels from drop down list in data source table

Question

quick question.

Data Source

Drop Down List in Data Source

Pivot Table

I want to get a count of each of the case letters that are in active status. The case numbers are inserted in the source data by a data validation list type ranging from A-G.

QUESTION: How do I get the pivot table to show all of the case letters from the drop down list even though not all of them have been used in the data source?

I would like the pivot table to show all the case letters and a 0 if none are entered in the data source. The drop down list is important in the actual project because it limits user inputs for less errors in the overall system.

Note: I am adding the pivot table to the data model when making it. I made up these data points to explain what my problem is.

Thank you anyone for any help given!

I tried to add the pivot table to the data model and then go to its options. From there I went to display and tried to get all data to show in rows. I also made sure any label that is empty would be valued at 0. It doesn't seem to read the data validation drop down list from the data source.

QUESTION: How do I get the pivot table to read all the labels from my drop down list in the data source and use them as labels and then their count in the pivot table?

Answer 1

Pivot Tables can only show values that exist in the data.

To achieve what you want, create dummy rows so that the values in the data validation exist in the data. You can filter the dummy rows out while still showing the values in Rows from the dummy data.

For example, with a table like the one below:

1. Create the pivot table.

2. Add the Status field to the Filters pane.

3. Filter out dummy from the Status field.

4. Add the Case field to the Rows pane.

5. With the Case field, go to Field Settings and select the Layout & Print tab

6. a. If the data was NOT added to the Data Model, select the checkbox Show items with no data. This will who show f and g in the Rows pane, even though it's filtered out.

   b. (Edited) If the data was added to the Data Model, right-click anywhere in the Pivot Table, and select PivotTable Options.... Under the Display tab, select the checkbox Show items with no data on rows. (see screenshot below table)

Status	Case
Active	a
Active	b
Active
Active	d
Active	e
dummy	f
dummy	g

(Edited) 6b screenshot:

This is what it will look like when you're done. From the OP, I believe you already know how to show 0's rather than a blank, but for the benefit of others, after adding the Case field to the Values pane, right-click anywhere under the Count of Case column and choose PivotTable Options.... From there, under the For empty cells show: field, type in 0.

Also, if you don't want the Status filter to show in the spreadsheet, you can set the filter "behind the scenes":