How to filter one column in Excel Pivot table

Question

I am trying to create a tunnel chart using data from Pivot table. However, I can't find a way to set a condition for one column ("Response Received Column). I want the table to count 5 people, and show 3 people responded. If I do the regular filter, I will only show 3 people on the "Name" column.

why not create an additional column having either 0/1 or TRUE/FALSE as values against existing `response received column`? — AnilGoyal, Dec 31 '20 at 07:50

score 0 · Answer 1 · answered Dec 31 '20 at 05:34

You can use array formulas - like this. In the result cell for Name enter formula:

=SUM(IF(ISBLANK(B2:B9),0,1))

the result cell for Response Received enter formula:

=SUM(IF((E2:E9="YES"), 1, 0))

(adjust the row range numbers as you need to match the range of the data)

IMPORTANT!

After you enter the formula you MUST press: (CTRL+SHIFT+ENTER) That activated the array formula. You can confirm that by selecting the formula cell and you will see curly brackets around, like so:

{=SUM(IF((E2:E9="YES"), 1, 0))}

Try it.

I can do that, but I want to use Pivot Table to create a tunnel chart and use slicer to build an interactive dashboard. — ghostabx, Dec 31 '20 at 05:43

score 0 · Accepted Answer · answered Dec 31 '20 at 08:34

Power-pivot solution. can be replicated directly in excel too.

Adding data to model, create an additional column say positive response with calculation as

=IF(Table1[response_received]="Yes", 1,0)

Click pivot table in first menu tab, and get your desired view like this

Note that I have used sum instead of count here.

How to filter one column in Excel Pivot table

2 Answers2