Excel: filter a column by more than two values

Question

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?

Answer 1

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

1. 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)
2. Select your whole table (A1:A11 in example)
3. Go to: Menu Bar » Data » Filter » Advanced
4. Select your whole criteria range including the header under Criteria range (C1:C4 in example)

Answer 2

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.

Answer 3

I duplicated the column I was filtering by and that gave me 4 arguments to use, 2 for each column.

Answer 4

For multiple "Contains" conditions its easier to create a second column with the particular values you're after. Make sure the 2nd column has the same Column Header as the 1st column that you plan to filter.

1. Select the column you want to filter (column B)
2. Click Data Menu > click Advanced Filter
3. Click Criteria Range
4. Select the 2nd columns particular values (Column G)
5. OK

eg: Filter my purchases by Tax Deductibles.