What is the feature of MS Excel to get specific data from selected range of cells?

Home > Filters > Advanced

Excel Advanced Filter Introduction

See how to set up an Advanced Filter in Excel to create a list of unique items, or to extract specific items to a different worksheet, based on criteria. You can also use complex criteria with an Advanced Filter, so it is useful when a simple Excel AutoFilter can't do what you need.

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

Create an Excel Advanced Filter

Here are the steps for setting up your data, and creating an Advanced Filter in Microsoft Excel.

-- 1] Set up the database

-- 2] Set up the Criteria Range [optional]

-- 3] Set up the Extract Range [optional]

-- 4] Apply the Excel Advanced Filter

1. Set up the database

To set up the database on the spreadsheet, follow these dataset guidelines, so the Advanced Filter can work correctly.

1. The first row [A1:D1] has headings.
2. Each column in the data set must have a unique heading -- duplicate headings will cause problems when running an Advanced Filter.
3. Subsequent rows contain data.
4. There are no blank rows within the database.
5. There is a blank row at the end of the database, and a blank column at the right.

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.

WARNING: When you use text criteria for an advanced filter, Excel interprets it as "begins with". If you need an exact match for the text values, see the Exact Match example below.

1. In this example, cells F1:F2 are the criteria range.
2. The heading in F1 exactly matches a column header [D1] in the database.
3. 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 cell value in the Total column, that is greater than $500, will remain visible.

Other operators include: