Create sub-lists and custom Excel reports on a worksheet separate from the database using Excel’s Advanced Filter with complex criteria, such as ANDs, ORs, and math conditions. This is an excellent way to create sales reports by region and amount sold, inventory restocking lists that show low items, customer response lists by purchase amounts and time frame, and so forth. Using this technique you filter a large database or master list and create an extract in a different worksheet. It’s great for creating reports from a large master list.
Using what you learn in this article you can create sub-lists or extracts from a database that meet very complex criteria. For example, in this figure a complex criteria specifies the extracted sub-list must have the region equal to North and the sales greater than 220.
A previous article, Creating Dynamic Excel Lists and Custom Reports from One Master List describes how to setup and use a master Excel list and extract its contents to a sub-list on another page. It also described how to use simple criteria, such as Region=West or Product=Laser Heavy. Now you will learn how to use simple formulas to extract records and create a sub-list that meet complex criteria.
If you have not used an Advanced Filter before, download and experiment with the example file using the directions from the previous article. Notice that in the sample file the Criteria range and the sub-list Extract range are on a separate worksheet. Be sure you start the Advanced Filter command from the worksheet containing the Criteria and Extract range.
The database or list used in these examples looks like this,
The master list may be tens of thousands of rows deep and contain many unique headings, but you can use complex criteria to extract a sub-list containing exactly what you want.
The extracted sub-list and the Criteria range are on a separate worksheet. Notice that the Criteria range for complex criteria is well above the extract range and is separated from it by at least three blank rows. OR criteria needs multiple rows under the headings in the Criteria range.
ALERT: Always leave at least one blank row between the Criteria range and the master list, Database, or Extract range.
Use Range Names to Save Time
Range names are a great time and error saver in Excel. When you are using Advanced Filters you can save time by assigning these names so that Excel automatically completes the Advanced Filter dialog box for you. Of course, if you are changing between complex criteria that include a different number of headings or rows you will need to change the shape of the Criteria range.
Excel will automatically apply the range names Database, Criteria, and Extract to the selections you use in the Advanced Filter dialog box, so don’t be surprised if you see these range names appear the next time you use the Advanced Filter dialog box.
Database, Extract (Copy to), and Criteria Ranges for these Examples
The ranges used in the following examples in the Advanced Filter dialog box are,
Range Name Cell Reference
Database ‘Master List’!$D$5:$F$41
Extract (Copy to) Headings (only) used by extract
Criteria Criteria headings and the outlined area underneath in the figures
Extracting a Sub-List with an OR Condition – Multiple Conditions in the Same Column Must be True
Use this method if you want to extract results that meet one condition OR another. For example, Region=West or Region=East. In the following example the criteria range is $D$5:$D$7 so that either one condition OR the other must be true for a row to be extracted from the master list.
The result is,
Custom Excel Report Tip
Your OR condition can contain more than two conditions. For example, you could extract Region=East or Region=West or Region=South. Just include additional rows in the Criteria range and enter values in those additional ranges. Do not leave a blank cell or you will get all values returned.
Extracting a Sub-List with an AND Condition – Criteria in Multiple Columns Must be True
Use this method if you want to extract results that meet one condition AND another. For example, Region=EAST and Product=Laser*. The asterisk is a wildcard that allows any characters in that position to meet the criteria. In the following example the criteria range is $D$5:$E$6 so that one condition AND the other must be true for a row of data in the database to be extracted.
The result is,
The extract contains rows where the criteria must satisfy two conditions. The product name must start with Laser because the * is used as a wildcard to indicate any characters may follow AND the region must be East.
Extracting a Sub-List where One Condition in each Column is True
Use this method if you want to extract results where a condition is true for one heading OR a condition is true for a different heading. For example, Region=EAST OR Product=Laser Light. In the following example the criteria range is $D$5:$E$7 so that either one condition OR the other must be true.
The result where Product = Laser Light OR Region = East is,
Extracting a Sub-List that Meets Numeric Criteria
For many situations you will need to extract a sub-list that meets a numeric criteria, for example, an inventory item that has fewer than 12 in stock, salespeople that sold more than 500 units of a product, and so forth. For these cases you use a numeric condition in the Criteria range. Criteria ranges can contain both numeric and text conditions.
Numeric operators you can use are,
|>=||Greater than or equal to|
|<=||Less than or equal to|
|<>||Not equal to|
In this example the Criteria range has been setup where Region=North and Sales >220.
The result from this Criteria contains only records from the master list where Region=North AND Sales>220.
Different criteria are kind of fun to experiment with. Ok, there are many more ways to have a lot more fun, but if you’re stuck in an office AND you have a few minutes to experiment OR this is a skill you know you will need, then give it a try.
- 1 Use Range Names to Save Time
- 2 Database, Extract (Copy to), and Criteria Ranges for these Examples
- 3 Extracting a Sub-List with an OR Condition – Multiple Conditions in the Same Column Must be True
- 4 Extracting a Sub-List with an AND Condition – Criteria in Multiple Columns Must be True
- 5 Extracting a Sub-List where One Condition in each Column is True
- 6 Extracting a Sub-List that Meets Numeric Criteria