Search Contextures Sites

 

Contextures
Excel news
by email

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

30 Excel Functions in 30 Days

 

 

 

Learn how to create Excel dashboards.

 

 

 

Learn how to create Excel dashboards.

 

 

Live-link your Excel dashboards to nearly any web data.

 

 

 

Learn how to create Excel dashboards.

 

 

Learn how to create Excel dashboards.

 

 

 

Excel 2003 Advanced Filter Introduction

1. Excel Advanced Filter--Introduction
    a) Apply an Excel Advanced Filter
    b) Filter Unique Records
    c) Extract Data to Another Worksheet
    d) Setting up the Criteria Range
    e) Using Wildcards in Criteria
    f) Criteria Examples
2. Advanced Filters -- Complex Criteria

For Excel 2007 and Excel 2010 instructions,
see Excel Advanced Filter Introduction

Download zipped Excel advanced filter workbook
with sample data and criteria.

Apply an Excel Advanced Filter

1. Set up the database

  1. The first row (A1:D1) has headings. Give each column a unique heading, because duplicate headings will cause problems with an Advanced Filter.
  2. Immediatlely below the headings, enter the rows of data.
  3. Do not leave completely blank rows within the database.
  4. Leave a blank row immediately below the database, and leave a blank column at the right.

Database


2. Set up the Criteria Range (optional)

In the criteria range for an Excel advanced filter, you can set the rules for the data that should remain visible after the filter is applied. You can use one criterion, or several.

  1. In this example, cells F1:F2 are the criteria range.
  2. The heading in F1 exactly matches a heading (D1) in the database.
  3. Cell F2 contains the criterion. The > (greater than) operator is used, with the number 500 (no $ sign is included).

Criteria Range

After the Excel advanced filter is applied, orders with a total greater than $500 will remain visible.

Other operators include:
< less than
<= less than or equal to
>= greater than or equal to
<> not equal to

3. Set up the Extract Range (optional)

If you plan to copy the data to another location, you can specify the columns that you want to extract. If you want to extract ALL columns, you can leave the extract range empty for the Excel advanced filter.

Criteria Range

  1. Select the cell at the top left of the range for the extracted data.
  2. Type the headings for the columns that you want to extract. These must be an exact match for the column headings, in spelling and punctuation. The column order can be different, and any or all of columns can be included.

4. Apply the Excel Advanced Filter

  1. Select a cell in the database.
  2. From the Data menu, choose Filter, Advanced Filter. (In Excel 2007, click the Data tab on the Ribbon, then click Advanced Filter.)
  3. You can choose to filter the list in place, or copy the results to another location.
  4. Excel should automatically detect the list range. If not, you can select the cells on the worksheet.
  5. Select the criteria range on the worksheet
  6. If you are copying to a new location, select a starting cell for the copy
    Note: If you copy to another location, all cells below the extract range will be cleared when the Advanced Filter is applied.
  7. Click OK

Apply Filter

Filter Unique Records

You can use an Excel Advanced Filter to extract a list of unique items in the database. For example, get a list of customers from an order list, or compile a list of products sold:

Note: The list must contain a heading, or the first item may be duplicated in the results.

  1. Select a cell in the database.
  2. From the Data menu, choose Filter, Advanced Filter.(In Excel 2007, click the Data tab on the Ribbon, then click Advanced Filter.)
  3. Choose 'Copy to another location'.
  4. For the List range, select the column(s) from which you want to extract the unique values.
  5. Leave the Criteria Range blank.
  6. Select a starting cell for the Copy to location.
  7. Add a check mark to the Unique records only box.
  8. Click OK.

Unique Records

Watch the Video

View the steps described above, in a short video clip.   Excel 2007 video

Extract Data to Another Worksheet

If the database is on Sheet1, you can extract data to Sheet2, by using an Excel Advanced Filter:

  1. Go to Sheet 2
  2. Select a cell in an unused part of the sheet (cell C4 in this example).
  3. From the Data menu, choose Filter, Advanced Filter.(In Excel 2007, click the Data tab on the Ribbon, then click Advanced Filter.)
  4. Choose Copy to another location.
  5. Click in the List Range box
  6. Select Sheet 1, and select the database.
  7. (optional) Click in the Criteria range box.
  8. Select the criteria range
  9. Click in the Copy to box.
  10. Select the cell on Sheet 2 in which you want the results to start, or select the headings that you have typed on Sheet 2.
  11. (optional) Check the box for Unique Values Only
  12. Click OK

Extract Data to Another Sheet

Watch the Video

View the steps described above, in a short Excel video tutorial on Excel advanced filter.

Setting up the Excel Advanced Filter Criteria Range

AND vs OR

If a record meets all criteria on one row in the criteria area, it will pass through the Excel advanced filter. In the example below --
customer must be MegaMart AND product must be Cookies AND total must be greater than 500.

Criteria on different rows are joined with an OR operator. In the second example at right --
customer must be MegaMart OR product must be Cookies OR total must be greater than 500.

By using multiple rows, you can combine the AND and OR operators. In the third example at right --
customer must be MegaMart AND product must be Cookies
OR
product must be Cookies AND total must be greater than 500.


Using Wildcards in Criteria

Use wildcard characters to filter for a text string in a cell.

The * wildcard

The asterisk (*) wildcard character represents any number of characters in that position, including zero characters.

In this example, any customer whose name contains "mart" will pass through the Excel advanced filter.


The ? wildcard

The question mark (?) wildcard character represents one characters in that position. In this example any 4-letter product that begins with c, and ends with ke, will pass through the Excel advanced filter.

The ~ wildcard

The tilde (~) wildcard character lets you search for characters that are used as wildcards. In this example any products that begins with Good and ends with Eats, will pass through the Excel advanced filter.

To find only the product named Good*Eats, use a tilde character in front of the asterisk.


Excel Advanced Filter Criteria Examples

Extract Items in a Range

To extract a list of items in a range, you can use two columns for one of the fields (e.g. Date). If you enter two criteria on the same row in the criteria range, you create an AND statement. In this example, any records that are extracted must be greater than the first date AND less than the second date.

Extract Items

Create Two or More Sets of Conditions

If you enter criteria on different rows in the criteria range, you create an OR statement.

In this example, extracted records must meet both conditions in row 2 OR both conditions in row 3.

Multiple Conditions


Extract Items with Specific Text

When you use text as criteria with an Excel advanced filter, Excel finds all items that begin with that text. For example, if you type "Ice" as a criterion, Excel finds "Ice", "Ice Cream" and "Ice Milk"

To extract only the records for Ice, use the following format:
      ="=Ice"

Multiple Conditions

Dowload the Sample File

Download zipped Excel advanced filter workbook
with sample data and criteria.

For Excel 2007 and Excel 2010 instructions,
see Excel Advanced Filter Introduction

Advanced Filter Tutorials

Excel Advanced Filters -- Complex Criteria

1. AutoFilter Basics
2. AutoFilter Tips
3. AutoFilter Programming

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2013
All rights reserved.