When You Are Performing An Advanced Filter, Where Do You Enter The Range For The Dataset?

Excel Advanced Filter Introduction

Apply an Advanced Filter in Excel to create a listing of unique items, or to extract specific items to a different worksheet. You lot can also use circuitous criteria with an Advanced Filter, then it is useful when a simple AutoFilter can't do what you need.

Create an Excel Avant-garde Filter

Watch 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

Here are the steps for setting up your data, and creating an Avant-garde Filter.

-- Set up the database

-- Set upwards the Criteria Range (optional)

-- Prepare the Extract Range (optional)

-- Apply the Excel Advanced Filter

1. Prepare the database

1. The first row (A1:D1) has headings. Each column must take a unique heading -- duplicate headings volition cause problems when running an Advanced Filter.
2. Subsequent rows contain data.
3. There 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 right.

2. Set the Criteria Range (optional)

In the criteria range for an Excel advanced filter, you tin set the rules for the data that should remain visible after the filter is practical. Y'all can use ane criterion, or several.

Alarm: When you utilize text criteria, Excel interprets it every bit "begins with". If you need an exact match, see the Verbal 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. Cell F2 contains the benchmark. 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
• <> non equal to

3. Gear up the Extract Range (optional)

If yous program to copy the data to another location (on the same canvas, or a different worksheet), you can specify the columns that you want to extract.

NOTE: If you want to extract ALL columns, you can exit the excerpt range empty for the Excel advanced filter.

1. Select the cell at the top left of the range for the extracted information.
2. Type the headings for the columns that you desire to excerpt.
   1. These must be an verbal match for the column headings in the data, in spelling and punctuation
   2. Cavalcade order can be different
   3. Any or all of columns can exist included.

4. Apply the Excel Advanced Filter

Follow these steps to apply the Avant-garde Filter

Note: If you want to ship the data to a dissimilar sheet, follow the steps here: Send Data to Another Sheet

1. Select a cell in the database.
2. On the Excel Ribbon's Data tab, click Advanced, to open up the Advanced Filter dialog box

3. Y'all can choose to filter the list in place, or copy the results to another location.
4. Excel should automatically discover 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 re-create to another location, all cells below the extract range will be cleared when the Advanced Filter is applied.
7. Click OK, to shut the Excel Advanced Filter dialog box

Filter Unique Records

With an Excel Advanced Filter, y'all can filter the data in identify, or to a different location. When applying the advanced filter, yous can specify that yous desire to meet unique items only. With that pick selected, all the duplicates are removed from the filtered list, and the outcome 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 excerpt a list of unique items in the database. For example, get a list of customers from an society list, or compile a list of products sold. In this instance, the unique list is copied to a different location, and the original listing in unchanged.

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. On the Excel Ribbon's Information tab, click Advanced.
3. In the Advanced Filter dialog box, choose 'Re-create to another location'.
4. For the Listing range, select the column(due south) from which you want to extract the unique values.
5. Exit the Criteria Range blank.
6. Select a starting cell for the Copy to location.
7. Add a bank 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 But, you might encounter duplicates in the results.

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

To learn more about this problem, go to the Remove Duplicates Problem page. You lot'll see why the duplicates are listed (floating betoken precision), and how to set the problem by using the Round role.

Video: Remove Duplicates

In Excel 2007, and afterwards versions, in that location is a Remove Duplicates tool. Dissimilar the Advanced Filter's Unique Records Only option, this does not leave the original list unchanged -- it completely removes all duplicate items from the list. Only the first instance of each detail is left.

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

Extract Data to Another Worksheet

With an advanced filter, you tin extract information to a different sheet. Watch this video to see the steps, and the written instructions are below the video.

Extract Information to Another Worksheet

If your Excel data is on 1 canvass, you tin can send filtered data to a unlike canvas, by using an Avant-garde Filter. In this example, the data is on Sheet1, and volition be copied to Sheet2.

1. Go to Sheet2 (encounter the steps on the video above)
2. Select a jail cell in an unused part of the canvas (cell C4 in this instance).
3. On the Excel Ribbon's 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 Re-create to box.
10. Select the jail cell on Sheet2 in which you want the results to start, or select the headings that you accept typed on Sheet2.
11. (optional) Check the box for Unique Values Only
12. Click OK

Setting up the Criteria Range

These examples show how to fix upwards criteria ranges for one criterion, or multiple criteria, using text, numbers, and wildcards. Y'all can also utilize formulas in the criteria range -- run across examples on this page: Avant-garde Filters -- Complex Criteria

Alarm: When you lot enter text criteria, Excel interprets information technology as "begins with". If you lot need an exact friction match, see the Verbal Match instance below.

Criteria Range Location

The examples on this page testify the criteria range nigh the data, but that isn't required.

• The criteria range tin can be on the same sheet as the data, or on a unlike canvass.
• The criteria range can exist directly above the data, or in columns to the left or right of the information.

NOTE:

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

That will not affect the avant-garde filter -- you just won't be able to encounter some of the 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.

AND Rule

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

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

OR Rules

In this example, there are three rows with criteria rules.

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

• customer name must begin with "MegaMart"
• OR production name must begin with "Cookies"
• OR full must be greater than 500.

AND/OR Rules

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

• client proper name must begin with "MegaMart" AND product name must brainstorm with "Cookies"
  • OR
• product proper name must begin with "Cookies" AND full must exist greater than 500.

Using Wildcards in Criteria

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

-- The * Wildcard

-- The ? wildcard

-- The ~ wildcard

The * wildcard

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

NOTE: Because Excel interprets text criteria equally "begins with", you don't need to add the * wildcard at the stop of the cord

In this instance, the Client criterion is: *o*r

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

The ? wildcard

The question marking (?) wildcard grapheme represents i characters in that position.

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

The ~ wildcard

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

In the get-go example, an asterisk is in the criteria cell -- Good*Eats -- then any customer name that begin with Good, then any characters, followed by Eats, will pass through the Excel advanced filter.

To find merely the customer name that begins with Adept*Eats, use a tilde graphic symbol (~) in front of the asterisk in the critereia cell: Good~*Eats

Criteria Examples

Hither are a couple of other Advance Filter criteria examples:

-- Extract Items in a Range

-- Extract Items with Specific Text

NOTE: There are more than criteria examples on this page: Advanced Filters -- Complex Criteria

Excerpt Items in a Range

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

If you enter two criteria on the same row in the criteria range, you create an AND statement. In this case, any records that are extracted must be:

• greater than or equal to 500
  • AND
• less than or equal to 750

Extract Items with Specific Text

When you utilize text as criteria with an Excel advanced filter, Excel finds all items that begin with that text.

For example, if you lot type "Ice" every bit a criterion, Excel finds "Ice", "Ice Cream" and "Water ice Milk", because they all begin with "Ice".

To extract but the records that are an verbal match for the criteria text, use 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 lot should put the criteria range directly in a higher place the data. This is not necessary -- use the location that works best for you.

The examples on this page show the criteria range near the information, but y'all don't need to leave information technology at that place.

• The criteria range tin can be on the same canvass every bit the data, or on a unlike sheet.
• The criteria range can be directly above 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 be hidden when yous apply an "In Place" advanced filter in.

The subconscious criteria rows volition not affect the advanced filter -- you merely won't be able to see some of the criteria range.

Become the Sample Workbook

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

For Excel 2003 instructions, see Excel 2003 Advanced Filter Introduction

More Tutorials

Advanced Filters -- Circuitous Criteria

Advanced Filter Criteria Slicers

Advanced Filter Macros

When You Are Performing An Advanced Filter, Where Do You Enter The Range For The Dataset?,

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

Posted by: velasquezancticipse.blogspot.com

Share this post