EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tools Excel Data Filter
 

Excel Data Filter

Pradeep S
Article byPradeep S
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated May 10, 2023

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, and 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 the Sort & Filter section. Or else we can apply the filter using the shortcut key ALT + D + F + F simultaneously or Ctrl + Shift + L together.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Uses of Data Filter in Excel

  • If the table or range contains many datasets, it isn’t easy to find & extract the precise requested information or data. In this scenario, the Data Filter helps out.

Filter

  • The 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 for sorting out data with simple drop-down menus.
  • The Data Filter option is significant to temporarily hide a few data sets in a table so that you can focus on the relevant data we need to work on.
  • The worksheet applies filters to rows of data.
  • Besides multiple filtering options, auto-filter criteria provide the Sort options relevant to a given column.

Definition

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)

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 must activate the Excel data filter for the Mobile sales data table in Excel, select the entire data range or table range, and click 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 must 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 that the 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.

The date filter lets you filter dates based on any date range. For example, you can filter conditions such as 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 that the 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 specify the required data you want to display. This process is called “Grouping of data,” which helps better analyze your data.
  • Excel data can also use 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.
  • The data Filter option can remove 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 filter out with the help of wild i.e by changing the question mark. (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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

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

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download Data Filter Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Data Filter Excel Template

EDUCBA

डाउनलोड Data Filter Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW