Here is the problem …
You are trying to filter using multiple AND and OR criteria in Microsoft Excel.
One or more columns in your list have more than 10,000 unique items (think employee numbers, serial numbers, transactions).
Advanced Filter is the solution …
On the Data tab, in the Sort & Filter group, you’ll find an Advanced button. Before you click it, we should set up our Query Form. Wait! What? Isn’t that an Access term? It sure is! However, the Advanced Filter tool requires that you type your criteria in isolated cells apart from your list or “database.” I recommend at least 5 blank rows above your data to start. You can actually have this query form anywhere in your workbook, but if you’re just learning, this is a good place to start.
- So, highlight the first 5 rows of your list using the row numbers and click Home>Cells>Insert or Ctrl++ (the Ctrl key and the plus key together).
- Next, copy the header row of your list to the first row of your query form. These column titles become your query fields and must match the headers in in your list, exactly.
- Keeping in mind that criteria typed across a row means AND, and that criteria typed down a column means OR, key in your criteria. You can use >, <, >= and <=, as well as mask characters like *.
- Single-click one cell inside your actual list and click the Advanced filter button (Data>Sort & Filter>Advanced Filter).
- You’ll notice that the table is already defined. So, just tab down to the Criteria field and then select the cells that show your criteria. Be sure to include the column titles or Excel won’t know what to match!
- Click OK. To unfilter, just click the highlighted Filter button on the Data Tab or try the shortcut Ctrl+Shift+L