EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Advanced Filter in Excel
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Advanced Filter in Excel

By Madhuri ThakurMadhuri Thakur

Advanced Filter in Excel

Advanced Filter in Excel (Table of Contents)

  • Advanced Filter in Excel
  • How to Use Advanced Filter in Excel?

Advanced Filter in Excel

Advanced Filter in excel is the next level filter option available in the Data menu tab under Sort & filter section, which is used to filter the selected data as per the criteria we set for this. For this, first, we need to scrub the data by removing the blank cell, keeping the header to all the columns. To advance filter, we need to define criteria by which we need to filter the data, and the criteria should be placed in separated cells from the table. Once we select the Advanced Filter Option, select the complete range we want to filter, then select the cell where we defined the criteria.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

What is the difference between regular and advanced filter?

  1. You can use the advanced filter for more complex criteria filtering. (I will explain in detail with an example.)
  2. A regular filter will filter data on the existing dataset, while with the latter, you can extract data to some other location keeping original data intact.
  3. The advanced filter can be used to extract unique entries in our dataset.
  4. Regular Filter is a sequential filter viz. you can’t use multiple criteria simultaneously to extract records from one dataset; you have to extract data satisfying each criterion, ultimately leading to duplicity.

E.g. If you want to extract records where Reporting Manager is “Aakash Harit” or Employee name is “Vishal Kumar”, you have to extract data 2 times using a regular filter, first data having Aakash Harit is RM, and second, when Emp name is Vishal while using an advanced filter, you can extract all unique records in one go.

How to Use Advanced Filter in Excel?

This Advanced Filter is very simple easy to use. Let us now see how to use the Advanced Filter in Excel with the help of some examples.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,325 ratings)
You can download this Advanced Filter Excel Template here – Advanced Filter Excel Template

Advanced Filter in Excel Example #1

  • Suppose you have the following dataset, and you have to get all the records where the order total is greater than 900, and the employee name is “Nishu Kumari”.

Advanced Filter Example 1

  • To use an advanced filter with some criteria, first, you have to copy all the headers & paste them somewhere else in the worksheet. (In my case, I am going to use blank rows to make it easier to understand.

Advanced Filter Example 1-1

  • Now, specify the conditions under these headers according to your requirement, which will act as an input in a filter.
  • Now, select the entire data set along with the headers and the Go-To Data tab – Sort & Filter. This will open the Advanced Filter dialog box.

Advanced Filter Example 1-2

  • Hotkey to apply the advanced filter after selecting the dataset is (Alt key+ A+ Q).

Advanced Filter Example 1-3

  • Now, as shown above, you have to enter the following details:
  1. Action: It has 2 options. First, filter the list in place (this will remove the original data, and the result of this filter will be placed on the same location) and Second, to copy another location. (this will allow you to save filtered data on location)
  2. List Range: It refers to the dataset from where you want to find data. (Here it’s A7: K35)
  3. Criteria Range: In this Criteria is mentioned, (A1: K2)
  4. Copy To: This cell will be activated if you go with the second option in the Action criteria.
  5. Copy Unique Records Only: Check this only if you require unique records only.
  • I have entered all the details in this dialogue box and chose to copy them to another location(A38) with unique records.
  • It will look somewhat as in the Screenshot on the next page. Now click ok, and it will give all unique records.

AF Example 1-4

The output is given below, where it shows only those data which contain the Employee name as Nishu Kumari.

AF Example 1-5

Filtered data with complex criteria as constructed above.

Advanced Filter in Excel Example #2

Now, many combinations of criteria can be used in Advanced Filter. (Using AND and OR Criteria) Some examples are:

  • Filter data where Customer Name is “Chloe Jones” AND Product name is “Apple”.

AF Example 2

I have entered all the details in this dialogue box and choose to copy them to another location(N3) with unique records.

AF Example 2-1

The output is given below:

AF Example 2-2

  • Filter data where the Reporting Manager is “Aakash Harit” AND Product Qty is greater than 50.

AF Example 2-3

I have entered all the details in this dialogue box and choose to copy them to another location(N9) with unique records.

AF Example 2-4

The output is given below:

AF Example 2-5

  • Filter data where Employee name is “Rajkumar Singh” AND Product Name is “Pineapple”, AND order total is greater than 100.

AF Example 2-6

I have entered all the details in this dialogue box and choose to copy them to another location(N17) with unique records.

AF Example 2-7

The output is given below:

AF Example 2-8

The above table shows empty data because it does not matches the given condition.

  • Filter data where Employee Name is “Vishal Kumar” OR Order Total is less than 500.

AF Example 2-9

I have entered all the details in this dialogue box and choose to copy them to another location(N22) with unique records.

AF Example 2-10

The output is given below:

AF Example 2-11

  • Filter data where Employee Name is “Abhay Gaurav” OR “ Nishu Kumari”.

Advanced Filter Example 2-12

I have entered all the details in this dialogue box and choose to copy them to another location(N38) with unique records.

Advanced Filter Example 2-13

The output is given below:

Advanced Filter Example 2-14

  • Filter data where the Reporting Manager is “Divya Sharma” OR Product Name is “Grapes”.

Advanced Filter Example 2-15

I have entered all the details in this dialogue box and choose to copy them to another location(N50) with unique records.

Advanced Filter Example 2-16

The output is given below:

Advanced Filter Example 2-17

From the above examples, you might have noticed that while using AND criteria, all values must be mentioned in the same row and in OR criteria, all values must be in different rows.

Advanced Filter in Excel Example #3

Another important feature in Advanced Filtering is filtering data using Wild Card Characters. There are wildcard characters:

  • An asterisk (*): It represents any number of characters. E.g. To filter data with Employee name starting from “R”,. You will write “R*”, So any employee name starting with initials R will be filtered out.

Advanced Filter Example 3

I have entered all the details in this dialogue box and choose to copy them to another location(N9) with unique records.

Advanced Filter Example 3-1

The output is given below:

Advanced Filter Example 3-2

  • Question Mark (?): It represents one single character. E.g. filter data with where employee name initials are “Nish”. Filter criteria will be like, “Nish?” and it can mean Nishu or Nisha.

Advanced Filter Example 3-3

I have entered all the details in this dialogue box and choose to copy them to another location(N9) with unique records.

Advanced Filter Example 3-4

The output is given below:

Advanced Filter Example 3-5

  • Tide (~): It is used to find any wildcard character within a text.

Advanced Filter in Excel Example #4

You can also remove duplicates or quickly extract the unique records from the data set.

  • Now suppose you have the following data set, which has some duplicate entries(highlighted), and you want to extract unique records from a dataset; you can use the advanced filter option to do it, keep in mind that there is no need to mention any values in criteria option and simply check unique records option. This will give you records with unique values.

Advanced Filter Example 4

  • After applying the filter, it will give a result, as shown below:

Advanced Filter Example 4-1

  • You can notice in the below image; all duplicates are removed. The last 3 rows are empty

Advanced Filter Example 4-2

  • However, latest versions of Excel (mainly after Excel 2007), we have an option to remove duplicates from a dataset (Go to Data Tab.Remove Duplicates or use hotkey Alt Key+ A+ M), but it will alter the original dataset. But if you want to keep original data intact, you can use an advanced filter to get a dataset with unique entries at some other location.

Key Notes:

  • While applying an advanced filter, always take care that headers in the criteria should be exactly the same as in the data set. Viz. there should be no spelling errors, space errors or even case errors, although alignment can be mixed. E.g. in the above, you can mention criteria like Product name first and employee name second. But headers should be exactly the same, as shown below.

Advanced Filter product name

  • You can’t UNDO advanced filtering when copied to some other location.
  • If you are using an advanced filter to extract unique entries, make sure to select headers also, the otherwise first entry will be taken as headers in it.

Things to Remember 

  • Advanced Filter is an advanced version of a regular filter used to filter data with complex criteria and multiple conditions.
  • It is used to extract unique entries from the dataset.
  • You must include headers while selecting the data set, and the headers in the criteria must be exactly in the same order as in the dataset.
  • You can’t undo it if filtered data is copied to another location.
  • The shortcut to apply an advanced filter after selecting the dataset is (Alt key+ A+ Q).

Recommended Articles

This has been a guide on Advanced Filter in Excel. Here we discuss how to Advanced Filter in Excel along with excel examples and downloadable excel templates. You may also look at these useful functions in excel –

  1. VBA Filter
  2. Excel Column Filter
  3. Excel Filter Shortcuts
  4. Excel AutoFilter
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Advanced Filter Excel Template

EDUCBA

Download Advanced Filter Excel Template

EDUCBA

डाउनलोड Advanced Filter Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more