Using Advanced Filters in Excel 2010

More Information

The Advanced Filter gives you the flexibility to extract your records to another location on the same worksheet or another worksheet in your workbook. It also allows the use of an "OR" statement in your Filters. (Example: Which sales were less than $400 "OR" greater than $600). Excel’s AutoFilter filters things in place and doesn't allow you to do complex filtering. To learn the basics of Advanced Filtering follow the steps below:

Step 1: Set up your Database

Advanced filter uses the concept of a database. A database is designated as a row of labels with columns of data underneath each of those labels. Each column contains data of a specific type like a date, a number or text. Here are some rules for setting up your database:

1. The first row of your database should only consist of labels. Bolding the labels help to remind you that they are not part of your data.
   
   

2. Under each label is the data that the label describes.
   
   Note: Make sure there are no blank rows within the database.
   
   

3. Make sure there empty cells around the whole database (The edge of the page is considered an empty cell). See image.

   

   
   

Step 2: Set up the Criteria Range.

The Criteria Range is just another way of saying "What do you want to Filter (Search) on?". You set the rules for the data that will remain visible after the filter is applied. You can use as many or as few rules as you need. We'll walk you through the Filtering process using this small database:



Note: Ensure that AutoFilter is off and there is always a blank row and column separating your database from any other text on the spreadsheet. Otherwise the Advanced Filter will think it's part of the database.


Setting up a Criteria Range area.

1. Copy and paste the label row to another spot on the worksheet. See image.

   
   
   

2. There's no set spot, just ensure you have enough room so that any text you type in is not in a row or column connected to the database.
   

Step 3: Using your Filters.

In Advanced Filtering you apply Rules that hide everything except what you're looking for. It acts like a very detailed Search when you start using the Rules.

Filtering using one Rule.

First you need to establish your Criteria Range. For this example we'll use the Criteria Range of "What orders were less than $450?".

The following symbols can be used in your Filters:

• < less than

• <= less than or equal to

• >= greater than or equal to

• <> not equal to

1. Enter <450 (There is no need for the $ sign.) under Total in your Criteria Range section.
   
   

2. Click in any cell in the database.See image.

   
   
   

3. Go to: The Data tab > Sort & Filter > Advanced.
   
   

4. In the Advanced Filter dialog box that opens, click in the Criteria Range box. See image.

   
   
   

5. Go back to the worksheet, In the Criteria Range area you just made, click on Total and drag your mouse over the Rule (<450) you entered. The Advanced Filter dialog box will disappear and an Advanced Filter - Criteria dialog box will take its place. See image.

   
   
   

6. When you release the mouse button, there should be a dotted line around both cells and the Advanced Filterdialog boxwill reappear. Click OK.
   

7. The database view will immediately change to show only those rows where the Total is less than $450.See image.

   
   
   

8. In order to see the whole database again or perform another Filter process, you're going to have to clear the view of your last Filter. Go back to the Sort & Filter under the Data tab and click Clear, the database will return to its original view.See image.

   
   

Filtering using an "AND" statement.

First you need to establish your Criteria Range. For this example we'll use the Criteria Range of "What orders did Sid "AND" Fred have that were less than $450?".

The following symbols can be used in your Filters:

• < less than

• <= less than or equal to

• >= greater than or equal to

• <> not equal to

1. Enter Fred and Sid in separate rows under Customer and <450 under Total for both Fred and Sid (There is no need for the $ sign.) in your Criteria Range section. Remember to keep a blank row and column around your database.
   

2. Left click in any cell in the database. See image.

   
   
   

3. Go to: The Data tab > Sort & Filter > Advanced.
   

4. In the Advanced Filterdialog box that opens, click in the Criteria Range box. See image.

   
   
   

5. Go back to the database, In the Criteria Range area you just made, click on Customer and drag your mouse to cover all the Rules you entered. The Advanced Filter dialog box will disappear and an Advanced Filter - Criteria dialog box will take its place. See image.

   
   
   

6. When you release the mouse button, there should be a dotted line around the Rules and the Advanced Filter dialog box will reappear. Click OK.
   

7. The database view will immediately change to show only those rows where Fred "AND" Sid's Total is less than $450. See image.

   
   
   

8. In order to see the whole database again or perform another Filter process, you're going to have to clear the view of your last Filter. Go back to the Sort & Filter under the Data tab and click Clear, the database will return to its original view. See image.

   
   

Filtering using an "AND" and an "OR" statement.

First you need to establish your Criteria Range. For this example we'll use the Criteria Range of "What orders were greater than $400 "AND" less than $560, "OR" greater than $1000?".

The following symbols can be used in your Filters:

• < less than

• <= less than or equal to

• >= greater than or equal to

• <> not equal to

Let’s pull apart the question to understand how to create the Criteria Range.

1. We want to find out what orders were greater than $400 AND less than $560. In order to do this, add another column label with the same title as your "AND" column label. In this case we're adding another Total label.See image.

   
   
   

2. Using your "AND" requirements going across and "OR" requirements going down, enter your Criteria Range. Put >400 under the first Total column and <560 under the new Total column. Now add the OR requirement by putting >1000 under the first Total column. The image shows what your Criteria Range should look like after creating your Criteria Range. Click anywhere in the database.See image.

   
   
   

3. Go to: The Data tab > Sort & Filter > Advanced.
   

4. In the Advanced Filter dialog box that opens, click in the Criteria Range box. See image.

   
   
   

5. Go back to the database, In the Criteria Range area you just made, click on Total and drag your mouse to cover all the Rules you entered. The Advanced Filter dialog box will disappear and anAdvanced Filter - Criteria dialog box will appear. See image.

   
   
   

6. When you release the mouse button, there should be a dotted line around the Rules and the Advanced Filter dialog box will reappear. Click OK.
   

7. The database view will immediately change to show only those rows where the Total is greater than $400 and less than $560 or greater then $1000. See image.

   
   
   

8. In order to see the whole database again or perform another Filter process, you're going to have to clear the view of your last Filter. Go back to the Sort & Filter under the Data tab and click Clear, the database will return to its original view.See image.

   
   

Step 4: Set up the Extract Range (The Basics).

When you use the Extract Range, it's same as saying: "I want the filtered data to appear in 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.

Filtering is done that same way as demonstrated in the "Using your filters" section. Step 6 is where the additional steps come into play for displaying the filtered results in a different location. For this example we will use the Criteria Range of: "What orders were over $400".

1. Enter >400 (There is no need for the $ sign.) under Total in your Criteria Range section. See image.

   
   
   
   

2. lick in any cell in the database.
   

3. Go to: The Data tab > Sort & Filter > Advanced.
   

4. In the Advanced Filter dialog box that opens, click in the Criteria Range box. See image.

   
   
   

5. Go back to the database, In the Criteria Range area you just made, click on Total and drag your mouse over the Rule (<450) you entered. The Advanced Filter dialog box will disappear and an Advanced Filter - Criteria dialog box will appear. See image.

   
   
   
   

6. When you release the mouse button, there should be a dotted line around both cells and the Advanced Filter dialog box will reappear. In the Action section, click the Copy to another location button. See image.

   
   
   

7. In the Advanced Filter dialog box, click on the Copy to: button. See image.

   
    
   

8. The Advanced Filter dialog box will close and the Advanced Filter - Copy to: dialog box will open. Click in the cell where you want the filtered output to be displayed and click the Advanced Filter - Copy to: button. See image.

   
   
   

9. When the Advanced Filter dialog box returns, click OK. The records that meet your Criteria Range will then be displayed starting at the cell you specified. See image.

   
   
   

Find out more about Filtering to a different worksheet by reading: Support article KB-909527 which also applies to Excel 2010.



Find out more about Advanced Filtering by reading: Filter by using advanced criteria
Find out more about Criteria Ranges by reading: Examples of complex criteria


You can also get help from the Microsoft Community online, search for more information on Microsoft Support or Windows Help and How To, or learn more about Assisted Support options.