Advance Filters

The best way to set up a sheet to use Advanced filters is to add a few rows above your data.

We will start with this data: Its the Invoice table from the famous Northwind Trading Company. (A Sample database provided by Microsoft)

And add a few rows at the top.

For this example we can add references in row 1 to mirror the tables headers in A4

Now we can add some criteria: For the names I like to just copy and paste data from the tables.

Copy both Andrew and Robert under the Salesperon header of the filter. Under Total I only want ones that are by Andrew AND greater than 100 OR by Robert AND greater than 200.

After adding the criteria we can apply the filter.

In the data tab, click advanced

The Advance filter window will pop up.

The list range, is the data in the table A5:I53

The criteria range is A1:I3 this will include the filter header and all rows below it that contain data.



Note: Be sure all of the rows you select in the filter data range contain data, or excel will include all results with no constraints. (It returns all of your data)

Click OK and now only the data were looking for is shown.



When you are finished reviewing the data or want to try another combination, press the clear button and try again.

Top Tip: When adding criteria 2 constraints in the same row will be calculated as AND, constraints in seperate rows will be calculated as OR. For our example above we are looking for invoices that were completed by Andrew AND were greater than 100, OR, by Robert AND greater than 200.