Excel - How to filter to show subset of questions for selected criteria

New Contributor

I have a set of  500 questions for different interest groups in a spreadsheet. The questions are tagged "Y" if it is applicable to a specific interest group. See table below. 

Bee_2021_1-1649234927878.png

Is there a way to filter these questions such that if I will see questions tagged "Y" for  "All Interests", Interest 1, Interest 2 or Interest 8. That is to say if "All Interests" has 10 questions, Interest 1 has 5 questions, Interest 2 has 6 questions and Interest 8 has 9 questions, I should see 10+5+6+9 = 30 questions on the spreadsheet. Is this achievable via filter and if yes, what are the steps and if no, any suggestions how this can be achieved? Any help appreciated?

 

Thanks in advance. 

@

4 Replies

@Bee_2021 

You can use Advanced Filter for this.

Elsewhere on the same sheet, create a range like this:

 

S1273.png

The headers in the top row should be exactly as in your question set.

Click anywhere in your set of questions.

On the Data tab of the ribbon, in the Sort & Filter group, click Advanced.

Excel should automatically select the entire set in the List range box.

Click in the Criteria range box, then select the range that you created as in the screenshot.

Click OK.

 

@Hans Vogelaar 

Thanks!! However, I noticed that the filter works if there is "Y" indicated in every column but fail to work if there is say a "blank" or "N" in the appropriate cell in the respective column.  Is there something that I am missing or is that how Advanced Filter works? How do I get the filtered questions without having to delete the columns not needed and reapplying the filter? The reason why I am asking this is because the filter is to be used by end-user and I am hoping for a more elegant solution. 

 

Many thanks in advance!!

 

Bee_2021_0-1649303746959.png

 

 

@Bee_2021

Sorry, I don't get your question. What exactly do you mean?

@Bee_2021 

Assuming Excel > 2019, 365 or Online/Web

 

_Screenshot.png