EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Excel AutoFilter
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

Excel AutoFilter

By Karthikeyan SubburamanKarthikeyan Subburaman

AutoFilter in Excel

Excel AutoFilter (Table of Contents)

  • AutoFilter in Excel
  • How to Use AutoFilter in Excel?

AutoFilter in Excel

IAutofilter in excel allows us to filter the data on various criteria but not limited to Equal to, Greater than, Greater than or equal to, Contains and Does not contain. And there are 2 ways to enable this. First is by pressing short cut keys Shift + Ctrl + L together and choosing the relevant filter criteria from the drop-down menu list. And other is by enabling the filter from the Data menu tab and then selecting the appropriate criteria from that dropdown.

Start Your Free Excel Course

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

In excel, we can find the Autofilter option in the data tab in the sort and filter group; click filter as shown in the below screenshot:

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,412 ratings)

Excel AutoFilter Step

The shortcut key for applying a filter is CTRL+SHIFT+ L, where it applies a filter for the entire spreadsheet.

How to Use AutoFilter in Excel?

AutoFilter in Excel is very simple and easy to use. Let’s understand the working of Excel AutoFilter by some examples.

You can download this AutoFilter Excel Template here – AutoFilter Excel Template

AutoFilter in Excel – Example #1

Consider the below example where we have BRAND sales data like local, zonal, and national wise.

Assume that we need to filter only the specific brand sales to view the sales data. In this scenario, filters can be used, and they will be very useful for displaying only the specific data. Now let’s see how to apply the Excel Autofilter by following the below procedure.

  • First, select the cell to apply a filter. Go to Data Tab. Choose the Autofilter option.

AutoFilter Example 1-1

  • We can see that filter has applied for the entire cells.

AutoFilter Example 1-2

  • Now we need to filter the specific brand to check the sales.
  • We can find BRAND in the C column; click on the drop-down list where it shows all the brands as per the below screenshot.

AutoFilter Example 1-3

  • Unselect all the check mark and select the brand BRAUN from the following list.

AutoFilter Example 1-4

  • Once we select BRAUN from the following list, excel filters only BRAUN, and it will hide all the other rows and display the specific brand as the output as follows.

AutoFilter Example 1-5

  • The above screenshot shows that only brand sales of BRAUN like local, zone, and National wise.

Clear AutoFilter in Excel

  • Once the Excel Autofilter has been applied, we can clear the filter where Autofilter has a clear filter option to remove it, which is shown in the below screenshot.

AutoFilter Example 1-6

  • The above screenshot shows the option “Clear Filter from BRAND” click on it so that the filters will be getting removed from the entire spreadsheet.

AutoFilter Example 1-7

AutoFilter in Excel – Example #2

In this example, we are now going to check the year wise sales along with brand wise sales and consider the below example.

AutoFilter Example 2-1

The above screenshot shows the year-wise sales data that has happened for all the brands; let us consider that we need to check the year-wise sales, so by using the filter option, we can do this task to display it.

  • Select the cells to apply the filter.
  • Go to the Data tab and click the filter option.

AutoFilter Example 2-2

  • Now the filter has been applied as follows. We can see that filter has been applied for the entire cell.

AutoFilter Example 2-3

  • Select the Year column and click on the drop-down list, and the output as follows.

AutoFilter Example 2-4

  • Once we click on the drop-down list, we can see the month name that has been checked; now, go to the specific month we need to check for the sales data. Assume that we need to check for April month sales. so remove the select all checkbox from the list as shown below.

AutoFilter Example 2-5

  • Now we can see that all checkbox is unselected and now choose for the “APRIL” month so that it filters only the “APRIL” month sales.

AutoFilter Example 2-6

  • Here the April month sale has been displayed along with brand wise and Local, Zone, and National wise sales. Hence we can give the month-wise sales data by applying the filter option.

AutoFilter Example 2-7

AutoFilter in Excel – Example #3

Here in this example, we are going to see the number filter, which has various filters as follows.

Let’s consider the same sales data assuming that we need to find out the zone wise with the highest sales numbers.

  • Select the F column cell named Local.
  • Click on the drop-down box where we will get various number filters as equal, does not equal, greater than etc.

AutoFilter Example 3-1

  • At the bottom, we can see the custom filter where we can apply a filter by our own choice.
  • Click on the custom filter.

AutoFilter Example 3-2

  • After choosing the custom filter, we will get a custom dialogue box as follows.

AutoFilter Example 3-3

  • Click on the custom Autofilter drop down, and we will get the list as follows.

AutoFilter Example 3-4

  • Choose the “is greater than” option and give the corresponding highest sales values to be get displayed.

AF Example 3-5

  • In the above screenshot, we have given the sales value “is greater than 2000”, So the filter option will display the sales data that has the highest sales value more than 2000, and the output is displayed as follows.

AF Example 3-6

AutoFilter in Excel – Example #4

In this example, we are going to see how to get the top 3 highest sales that have been happened for this year.

Consider the same sales data; we can get the top 5 highest sales figure by choosing the Top 10 options given in the number filter as shown below:

AF Example 4-1

  • Click on the top 10 option as shown above, and we will get the dialogue box as follows.

AF Example 4-2

  • Here we can increase or decrease the top option numbers as per our choice; in this example, we are going to consider the top 3 highest sales and then click ok.

AF Example 4-3

  • The below result shows the top 3 sales that have been happened in this year.

AF Example 4-4

AutoFilter in Excel – Example #5

In this example, we are going to see how to apply a filter by using color, Normally in the FMCG field, low sales data values have been highlighted as RED and Medium as Yellow, and Highest Values AS Green. Let us apply the same color for the values and check with the same example as follows.

AF Example 5-1

In the above screenshot, we have applied three different colors where Red Indicates Lowest, yellow indicated the Medium, and Green Indicates the Highest Sales value.

  • Select the cell F column named Local.
  • Click on the drop-down box and select “Filter by Color”.
  • Now it shows five different color choices for selection.

AF Example 5-2

  • First, we will select the Green color to check the highest sales value.

AF Example 5-3

  • The output will be as follow.

AF Example 5-4

Now the above result shows that filter by color has been applied, which shows the highest sale value.

Things to Remember 

  • Ensure your data is clear and accurate so that we can Autofilter the data easily.
  • Excel AutoFilters will not work if the header is blank, so make sure that all the headers are named before applying AutoFilter.
  • Excel Autofilters will not work if the cells are merged, so before applying the filter, make sure that all cells are individual.
  • Do not use blank rows and columns while using the Excel AutoFilter.

Recommended Articles

This has been a guide to AutoFilter in Excel. Here we discuss how to use AutoFilter in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Scrollbar in Excel
  2. Excel Flash Fill
  3. Excel Auto Fill
  4. Autofit Row Height in Excel
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
1 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 AutoFilter Excel Template

EDUCBA

Download AutoFilter Excel Template

EDUCBA

डाउनलोड AutoFilter 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