Excel: filter a column by more than two values

I have a worksheet with a column, user_ID, that contains a large number of user IDs

I'd like to filter it down to about a dozen IDs, but using Filter -> Custom Filter only allows me to filter by 2 IDs total.

Is there a better way to filter this column by this dozen or so IDs?

fox

Posted 2013-07-23T23:00:19.273

Reputation: 727

custom sort or custom filter? If you go to filter - filter you can specify all the values with checkboxes – Raystafarian – 2013-07-23T23:37:32.297

1I believe that the sentence only allows me to filter by 2 IDs total isn't clear at all. Can you please describe this better? Can you upload a photo maybe? – Math – 2013-07-24T01:34:01.993

Answers

Filter -> Custom Filter only allows me to filter by 2 IDs total.

Excel's Advanced Filter can filter for as many values as you want

enter image description here

Set up your criteria range. The header must be named exactly like the column where Excel should apply your filter to (data table in example)
Select your whole table (A1:A11 in example)
Go to: Menu Bar » Data » Filter » Advanced
Select your whole criteria range including the header under Criteria range (C1:C4 in example)

enter image description here

nixda

Posted 2013-07-23T23:00:19.273

Reputation: 23 233

To exclude things, I had to populate the data table with =if(not([condition I want to exclude]),value from cell, ""). Basically allowing every value except the ones I wanted to exclude. – Noumenon – 2017-09-07T20:28:33.433

how do i filter out any value with numbers, so that only the alphabetical values remain? – oldboy – 2019-07-20T08:12:35.767

1awesome, this worked. "advanced filter" in excel 2010. – fox – 2013-07-24T20:14:51.073

I discovered that if you want to do an advanced filter on "contains text", you can put a wildcard (*) at the beginning and end of the criteria text. This allows a bit of a fuzzier match. For example *Bob Smith* in C2 above could pull "Bob Smith" OR "Mr. Bob Smith" out of column A, if it existed there.

Chuck

Posted 2013-07-23T23:00:19.273

Reputation: 61

1This should be a comment to the above solution by @nixda. Very useful. Thanks – shreyansp – 2017-02-06T12:26:49.817