How To Use Advanced Filter In Excel

Excel Advanced Filter Introduction

Use an Advanced Filter in Excel to create a listing of unique items, or to extract specific items to a different worksheet. You tin can besides use complex criteria with an Advanced Filter, so it is useful when a simple AutoFilter tin can't do what you need.

Create an Excel Advanced Filter

Lookout this video to see the steps for setting up your criteria range, and running an Advanced Filter. The written instructions are below the video.

Create an Excel Advanced Filter

Hither are the steps for setting upward your data, and creating an Advanced Filter.

-- Gear up upwards the database

-- Ready the Criteria Range (optional)

-- Set the Excerpt Range (optional)

-- Utilize the Excel Advanced Filter

1. Set upward the database

1. The showtime row (A1:D1) has headings. Each column must have a unique heading -- indistinguishable headings will cause problems when running an Advanced Filter.
2. Subsequent rows contain data.
3. At that place are no blank rows within the database.
4. There is a blank row at the terminate of the database, and a bare column at the correct.

2. Set upwards the Criteria Range (optional)

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

WARNING: When you apply text criteria, Excel interprets it every bit "begins with". If you need an exact match, see the Exact Friction match example below.

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. Prison cell F2 contains the criterion. The > (greater than) operator is used, with the number 500 (no $ sign is included).

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 the Excerpt Range (optional)

If you plan to copy the information to another location (on the same sheet, or a unlike worksheet), yous can specify the columns that you want to extract.

NOTE: If you desire to extract ALL columns, you can leave the excerpt range empty for the Excel avant-garde filter.

1. Select the prison cell at the top left of the range for the extracted data.
2. Type the headings for the columns that y'all want to extract.
   1. These must be an exact lucifer for the column headings in the data, in spelling and punctuation
   2. Cavalcade order tin can be different
   3. Any or all of columns can be included.

four. Utilize the Excel Advanced Filter

Follow these steps to apply the Advanced Filter

NOTE: If you lot want to send the data to a different sheet, follow the steps here: Send Data to Another Sheet

1. Select a cell in the database.
2. On the Excel Ribbon'south Data tab, click Advanced, to open the Avant-garde Filter dialog box

3. You can choose to filter the list in identify, or copy the results to another location.
4. Excel should automatically detect the list range. If not, y'all 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 jail 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, to close the Excel Advanced Filter dialog box

Filter Unique Records

With an Excel Advanced Filter, you can filter the data in identify, or to a different location. When applying the advanced filter, you tin specify that you want to run into unique items just. With that option selected, all the duplicates are removed from the filtered list, and the event is a unique list of items.

The written instructions for this technique are beneath the video.

Filter Unique Records

You can use an Excel Advanced Filter to extract a list of unique items in the database. For example, become a listing of customers from an social club list, or compile a list of products sold. In this instance, the unique listing is copied to a different location, and the original listing in unchanged.

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

1. Select a jail cell in the database.
2. On the Excel Ribbon'due south Information tab, click Advanced.
3. In the Advanced Filter dialog box, choose 'Copy to another location'.
4. For the List range, select the column(s) from which y'all want to extract the unique values.
5. Leave the Criteria Range blank.
6. Select a starting cell for the Copy to location.
7. Add together a check mark to the Unique records only box.
8. Click OK.

Duplicates in Unique Records

Occasionally, if yous run an Advanced Filter for Unique Records Merely, you might see duplicates in the results.

This can happen when in that location are numbers in the information, and Excel detects hidden differences between the numbers.

To learn more nearly this problem, go to the Remove Duplicates Trouble page. You'll come across why the duplicates are listed (floating bespeak precision), and how to fix the problem past using the ROUND function.

Video: Remove Duplicates

In Excel 2007, and later on versions, there is a Remove Duplicates tool. Unlike the Avant-garde Filter's Unique Records Only option, this does not go out the original list unchanged -- it completely removes all duplicate items from the list. But the get-go example of each item is left.

You can download the sample file that was used in this video.

Excerpt Data to Another Worksheet

With an advanced filter, you can extract data to a different sheet. Picket this video to come across the steps, and the written instructions are below the video.

Excerpt Data to Another Worksheet

If your Excel data is on one sheet, y'all can transport filtered data to a dissimilar canvas, by using an Advanced Filter. In this example, the data is on Sheet1, and will be copied to Sheet2.

1. Go to Sheet2 (see the steps on the video in a higher place)
2. Select a cell in an unused part of the sheet (jail cell C4 in this example).
3. On the Excel Ribbon'south Data tab, click Advanced
4. Choose Copy to another location.
5. Click in the List Range box
6. Select Sheet1, 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 jail cell on Sheet2 in which you want the results to start, or select the headings that you have typed on Sheet2.
11. (optional) Check the box for Unique Values Only
12. Click OK

Setting upwardly the Criteria Range

These examples show how to fix criteria ranges for one criterion, or multiple criteria, using text, numbers, and wildcards. You can also use formulas in the criteria range -- see examples on this page: Avant-garde Filters -- Complex Criteria

Warning: When you enter text criteria, Excel interprets it as "begins with". If you need an exact match, meet the Verbal Lucifer example beneath.

Criteria Range Location

The examples on this page bear witness the criteria range about the data, but that isn't required.

• The criteria range tin can be on the same sheet as the data, or on a different canvass.
• The criteria range can be directly in a higher place the data, or in columns to the left or right of the data.

Notation:

If the criteria range is on the same sheet, and in the same rows as the data, the criteria cells might exist subconscious when yous apply an "In Place" advanced filter in.

That will non affect the avant-garde filter -- you just won't be able to see some of the criteria range.

AND vs OR

If a record meets all criteria on one row in the criteria area, information technology volition pass through the Excel avant-garde filter.

AND Rule

In this example, there is a single row of criteria rules:

• customer name must brainstorm with "MegaMart"
• AND production name must begin with "Cookies"
• AND full must be greater than 500

OR Rules

In this example, in that location are 3 rows with criteria rules.

Criteria on dissimilar rows are joined with an OR operator. In the second example --

• client name must begin with "MegaMart"
• OR product name must begin with "Cookies"
• OR full must be greater than 500.

AND/OR Rules

By using multiple rows, you can combine the AND and OR operators, as in this example:

• customer name must begin with "MegaMart" AND product name must brainstorm with "Cookies"
  • OR
• product name must begin with "Cookies" AND total must be greater than 500.

Using Wildcards in Criteria

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

-- The * Wildcard

-- The ? wildcard

-- The ~ wildcard

The * wildcard

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

Annotation: Because Excel interprets text criteria as "begins with", you don't demand to add the * wildcard at the stop of the string

In this example, the Customer criterion is: *o*r

Two customer names, FoodMart and Mart-o-rama, incorporate an "o", followed by an "r" anywhere after the "o". The records for those two customers appear in the filtered list.

The ? wildcard

The question mark (?) wildcard character represents ane characters in that position.

In this example any product name that begins with c, followed past any unmarried character, and then ke, will laissez passer through the Excel avant-garde filter. Both Coke and Cake are in the filtered results.

The ~ wildcard

The tilde (~) wildcard character lets you search for characters that are used as wildcards.

In the outset example, an asterisk is in the criteria cell -- Expert*Eats -- so any client name that brainstorm with Skillful, and then any characters, followed past Eats, volition pass through the Excel advanced filter.

To find just the customer name that begins with Good*Eats, use a tilde graphic symbol (~) in forepart of the asterisk in the critereia jail cell: Proficient~*Eats

Criteria Examples

Here are a couple of other Accelerate Filter criteria examples:

-- Extract Items in a Range

-- Extract Items with Specific Text

Notation: There are more criteria examples on this folio: Avant-garde Filters -- Complex Criteria

Extract Items in a Range

To excerpt a list of items in a range, you can use two columns for one of the fields. In this example, 2 columns are used for the Total field.

If yous 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 or equal to 500
  • AND
• less than or equal to 750

Excerpt Items with Specific Text

When y'all use text as criteria with an Excel advanced filter, Excel finds all items that begin with that text.

For instance, if you blazon "Ice" as a benchmark, Excel finds "Water ice", "Water ice Cream" and "Ice Milk", considering they all begin with "Ice".

To extract only the records that are an exact friction match for the criteria text, employ the following format in the critera range (upper or lower example won't affect the results):
="=ice"

Where Should Criteria Range Be?

Some Excel lessons insist that you should put the criteria range directly above the information. This is not necessary -- use the location that works best for y'all.

The examples on this folio show the criteria range near the data, only you don't need to go out it there.

• The criteria range tin be on the same canvass every bit the data, or on a different sheet.
• The criteria range tin exist straight above the data, or in columns to the left or correct of the data.

Annotation

If the criteria range is on the aforementioned canvass, and in the same rows as the data, the criteria cells might be hidden when you apply an "In Place" advanced filter in.

The hidden criteria rows will not affect the advanced filter -- you just won't be able to come across some of the criteria range.

Become the Sample Workbook

Download zipped Excel avant-garde filter workbook with sample data and criteria.

For Excel 2003 instructions, see Excel 2003 Advanced Filter Introduction

More Tutorials

Avant-garde Filters -- Complex Criteria

Avant-garde Filter Criteria Slicers

Avant-garde Filter Macros

How To Use Advanced Filter In Excel,

Source: https://www.contextures.com/xladvfilter01.html

Posted by: thompsonocces1967.blogspot.com

Share this post