Beyond Filtering: Advanced Filter Techniques — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily
  • LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Beyond Filtering: Advanced Filter Techniques

Get PDF file

by on
in The Office Tech Pro

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).

filtering in Microsoft Excel

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.

  1. 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).
  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 match the headers in in your list, exactly.
  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. 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!
  6. Click OK. To unfilter, just click the highlighted Filter button on the Data Tab or try the shortcut Ctrl+Shift+L

advanced filter in Microsoft Excel

{ 2 comments… read them below or add one }

Dave August 19, 2015 at 12:46 am

Thanks Melissa, good instructions on advanced filtering.

“To unfilter, just click the highlighted Filter button on the Data Tab or try the shortcut Ctrl+Shift+L” – for me, the Filter button isn’t highlighted (Excel 2010), instead I click the Clear button to the right of the Filter button (or Ctrl+Shift+L).



services advertiser September 19, 2013 at 7:49 pm

That’s an additional idea to all the business oriented people out there like me I guess. Well, for me online advertising is also a great technique that will surely increase your market. There are free advertisers like this one:


Leave a Comment

Previous post:

Next post: