Creating Custom Excel Reports and Dynamic Sub-Lists Using Complex Criteria

Excel

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.

This AND criteria filters the extract for North and Sales values over 220.

This AND criteria filters the extract for the North region and Sales values over 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.

Download the Dynamic Excel Sub-Lists Example File

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.

From a master Excel list, table, or database you can create sub-lists or custom reports.

From a master Excel list, table, or database like this, you can create sub-lists or custom reports.

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.

This AND criteria filters the extract for North and Sales values over 220.

Use multiple rows and headings for advanced criteria and always leave a space below the Criteria 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.

Learn how to use Excel range names

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.

Setup for OR conditions by putting two or more cells under a heading in the Criteria range.

Setup for OR conditions by putting two or more cells under a heading in the Criteria range.

The result is,

A Criteria range with two cells under a heading extracts values when one OR the other value is true.

A Criteria range with two cells under a heading extracts values when one OR the other value is true.

Download the Dynamic Excel Sub-Lists Example File

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.

Setup an AND criteria with two headings in the Criteria range.

Setup an AND criteria with two headings in the Criteria range.

The result is,

Use AND with an Advanced Excel filter to extract records when one criteria AND another are true.

Use AND with an Advanced Excel filter to extract records when one criteria AND another are true.

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.

Setup an AND criteria with two headings in the Criteria range.

Setup a Criteria range for both AND and OR conditions with two rows and two headings.

The result where Product = Laser Light OR Region = East is,

Setup for OR conditions by putting two or more cells under a heading in the Criteria range.

Extracting a sub-list with an Advanced Filter can have both AND and OR conditions.

 

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,

Operator Meaning
= Equal to
> Greater than
< Less than
>= 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.

Math operators can be used to specify numeric criteria.

Math operators can be used to specify numeric criteria.

The result from this Criteria contains only records from the master list where Region=North AND Sales>220.

This AND criteria filters the extract for North and Sales values over 220.

This AND criteria filters the extract for the North region and Sales values over 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.

Download the Dynamic Excel Sub-Lists Example File

Learn the basics of extracting a sub-list from a master list

Learn how to apply range names

 

Share the power...

Leave a Comment:

Leave a Comment: