EDUCBA

EDUCBA

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

By Pradeep SPradeep S

Data Filter in Excel

Data Filter in Excel (Table of Contents)

  • Data Filter in Excel
  • Uses of Data Filter in Excel
  • Types of Data Filter in Excel
  • How to Add Data Filter in Excel?

Data Filter in Excel

Data Filter in excel has many purposes apart from filtering the data. Although its main purpose is to filter the data as per the required condition, apart from this, we can sort, arrange the data, filter the data as per the color of cells or fonts or any condition available in the Text filter in the column where the filter is applied. To apply the filter, first, select the row where we need a filter, then from the Data menu tab, select Filter from Sort & Filter section. Or else we can apply filter by using short cut key ALT + D + F + F simultaneously or Ctrl + Shift + L together.

Uses of Data Filter in Excel

  • If the table or range contains a huge number of datasets, it’s very difficult to find & extract the precise requested information or data. In this scenario, the Data Filter helps out.

Filter

  • Data Filter in Excel option helps out in many ways to filter the data based on text, value, numeric or date value.
  • The Data Filter option is very helpful to sort out data with simple drop-down menus.
  • The Data Filter option is significant to temporarily hide few data sets in a table so that you can focus on the relevant data we need to work on.
  • Filters are applied to rows of data in the worksheet.
  • Apart from multiple filtering options, auto-filter criteria provide the Sort options also relevant to a given column.

Definition

Start Your Free Excel Course

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

Data Filter in Excel: it’s a quick way to display only the relevant or specific information which we need & temporarily hide irrelevant information or data in a table.

To activate the Excel data filter for any data in excel, select the entire data range or table range and click on the Filter button in the Data tab in the Excel ribbon.

Data Filter

(keyboard shortcut – Control + Shift + L)

Types of Data Filter in Excel

There are three types of data filtering options:

1. Data Filter Based On Text Values – It is used when the cells contain TEXT values; it has below mentioned Filtering Operators (Explained in example 1).

Data Filter Text Values

Apart from multiple filtering options in a text value, AutoFilter criteria provide the Sort options also relevant to a given column. i.e. Sort by A to Z, Sort by Z to A, and Sort by Color.

Data Filter Text Values 1

2. Data Filter Based on Numeric Values – It is used when the cells contain numbers or numeric values

It has below mentioned Filtering Operators (Explained in example 2)

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

Data Filter Number Values

Apart from multiple filtering options in Numeric value, AutoFilter criteria provide the Sort options also relevant to a given column. i.e. Sort by Smallest to Largest, Sort by Largest to Smallest, and Sort by Color.

Data Filter Number Values 1

3. Data Filter Based On Date Values – It is used when the cells contain date values (Explained in example 3)

Data Filter Date values

Apart from multiple filtering options in date value, AutoFilter criteria provide the Sort options also relevant to a given column. i.e. Sort by Oldest to Newest, Sort by Newest to Oldest, and Sort by Color.

Data Filter Date values 1

How to Add Data Filter in Excel?

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

You can download this Data Filter Excel Template here – Data Filter Excel Template

Example #1 – Filtering Based on Text Values or Data

In the below-mentioned example, the Mobile sales data table contains a huge list of datasets.

Data Filter Example 1

Initially, I have to activate the Excel data filter for the Mobile sales data table in excel, select the entire data range or table range, and click on the Filter button in the Data tab in the Excel ribbon.

Or click (keyboard shortcut – Control + Shift + L)

Data Filter Example 1-1

When you click on Filter, each column in the first row will automatically have a small drop-down button or filter icon added at the right corner of the cell i.e.

Data Filter Example 1-2

When excel identifies that the column contains text data, it automatically displays the option of text filters. In the mobile sales data, if I want sales data in the northern region only, irrespective of date, product, sales rep & units sold. I need to select the filter icon in the region header; I have to uncheck or deselect all the regions except the north region. It returns mobile sales data in the northern region only.

Data Filter Example 1-3

Once a filter is applied in the region column, Excel pinpoints you that table is filtered on a particular column by adding a funnel icon to the region column’s drop-down list button.

Data Filter Example 1-4

I can further filter based on brand & sales rep data. Now with this data, I further filter in the product region where I want the sales of the Nokia brand in the north region only irrespective of the sales rep, units sold & date.

Data Filter Example 1-5

I have to just apply the filter in the product column apart from the region column. I have to uncheck or deselect all the products except the NOKIA brand. It returns Nokia sales data in the north region.

Example 1-6

Once the filter is applied in the product column, Excel pinpoints you that table is filtered on a particular column by adding a funnel icon to the product column’s drop-down list button.

Example #2 – Filtering Based on Numeric Values or Data

When excel identifies that the column contains NUMERIC values or data, it automatically displays the option of text filters.

If I want data of units sold in the mobile sales data, which is more than 30 units, irrespective of date, product, sales rep & region. For that, I need to select the filter icon in the units sold header; I have to select the number of filters, and under that greater than an option.

Data Filter Example 2

Once greater than option under number filter is selected, pop up appears, i.e. Custom auto filter, in that under the unit sold, we want datasets of more than 30 units sold, so enter 30. Click ok.

Example 2-1

It returns mobile sales data based on the units sold. i.e. more than 30 units only.

Example 2-2

Once the filter is applied in the units sold column, Excel pinpoints you that table is filtered on a particular column by adding a funnel icon to the units sold column drop-down list button.

Sales data can be further Sorted by Smallest to Largest or Largest to Smallest in units sold.

Example #3 – Filtering based on Date Value

When excel identifies that the column contains DATE values or data, it automatically displays the option of DATE filters.

Date filter lets you filter dates based on any date range. For example, you can filter on conditions such dates by day, week, month, year, quarter, or year-to-date.

In the mobile sales data, if I want mobile sales data only on or for the date value, i.e. 01/15/17, irrespective of units sold, product, sales rep & region. I need to select the filter icon in the date header; I have to select the date filter, and under that equals to option.

Data Filter Example 3

Custom AutoFilter dialog box will appear; enter a date value manually, i.e. 01/15/17

Example 3-1

Click ok. It returns mobile sales data only on or for the date value, i.e. 01/15/17

Example 3-2

Example 3-3

Once a filter is applied in the date column, Excel pinpoints you that table is filtered on a particular column by adding a funnel icon to the date column drop-down list button.

Things to Remember

  • Data filter helps out to specify the required data that you want to display. This process is also called “Grouping of data, ” which helps out better analyse your data.
  • Excel data can also be used to search or filter a data set with a specific word in a text with the help of a custom auto filter on the condition it contains ‘a’ or any relevant word of your choice.
  • Data Filter option can be removed with the below-mentioned steps:

Go to the Data tab > Sort & Filter group and click Clear.

Data Filter clear 1

A Data Filter option is Removed.

Data Filter clear 1-1

  • Excel data filter option can filter the records by multiple criteria or conditions, i.e. by filtering multiple column values (more than one column) explained in example 1.
  • Excel data filter helps out to sort out blank & non-blank cells in the column.
  • Data can also be filtered out with the help of wild characters, i.e.? (question mark) & * (asterisk) & ~ (tilde)

Recommended Articles

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

  1. Excel Filter Shortcuts
  2. Excel Column Filter
  3. Advanced Filter in Excel
  4. VBA Filter
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
6 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 Data Filter Excel Template

EDUCBA

Download Data Filter Excel Template

EDUCBA

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