• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Go beyond Excel filtering with advanced techniques

Get PDF file

by on
in Excel Training,Office Technology

The problem

The dropdown filter menu in Excel allows for 10,000 unique items. Say you’re filtering a list of your 55,000 employees, each with their own employee number.

You need the records that have either of two criteria in one column and either of three criteria in another column.

You want to filter for more than two “and” criteria.

When Filter doesn’t give you exactly what you need, there are a few options to try.

Advanced Filter

On the Data tab, Sort & Filter group, you’ll find an Advanced button. Before you click it, set up a Query Form. Query Form? 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.” You should set up at least five 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.

1. Highlight the first five rows of your list using the row numbers and click Home>Cells>Insert or Ctrl++ (the Ctrl key and the plus key together).

2. 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 exactly match the headers in your list.

3. 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 *.

4. Single-click one cell inside your actual list and click the Advanced Filter button (Data>Sort & Filter>Advanced Filter).

5. You’ll notice that the table is already defined. 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!

6. Click OK. To unfilter, just click the highlighted Filter button on the Data Tab or try the shortcut Ctrl+Shift+L.


If you’re running Microsoft Office Professional or the stand-alone version of Access, it’s really easy to employ the robust query tools in this application to your Excel Lists, even without converting it over to an Access Database. If you are already comfortable with Access query basics, this is the way to go.

  1. On the External Data tab, click the Excel button.
  2. Browse for your Excel file, but do not just click OK.
  3. Click the third radio button that says, Link to the data source by creating a linked table, then OK.
  4. Locate the work sheet that has your list and click Next.
  5. If Access doesn’t recognize your column headings, fix this on the next step.
  6. Click Next, then Finish. You’ll notice your list shows up with an Excel icon rather than Access table icon. This indicates that it is linked and not imported. You may not update your list here, but you can Query and report on it to your heart’s content!

{ 1 comment… read it below or add one }

kenny January 27, 2018 at 5:57 am

Thanks very much for the tip on how to filter a table over the 10k limitation, this has helped me tremendously. Your page is the only one that has pointed me in a direction that I understand to get the result I want.
Question though, while I understand how queries on different rows in a single column are treated as OR’s, and queries on a single row in different columns are treated as AND’s on those two columns, is there any way to query a single column with an AND query?


Leave a Comment