Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Filter in Excel? A Step-by-Step Guide

How to Filter in Excel? A Step-by-Step Guide

Note: This guide on how to filter in Excel is suitable for all Excel versions including Office 365.

The data filter is probably the most underrated feature in Excel. It is very robust and gets more things done easily than its counterparts. I feel that It doesn’t get the attention it deserves, especially in beginner’s Excel guides. 

That is why we have made this essential guide on how to use the data filter in Excel, covering all its lesser-known but important aspects. 

You’ll learn:

  • How to Filter in Excel ? – A Simple Example
  • How to Use the Excel Filter for Text, Numbers and Dates?
    • Filters Based On Text Values
    • Filter Based on Numeric Values
    • Filter Based On Date Values
  • How to Filter in Excel Based on Multiple criteria?
  • How to Use the Sheet View Feature in Excel Filter?
  • How to Clear the Filter in Excel?

How to Filter in Excel ? — A Simple Example

The Filter option in Excel selects a part of the table and displays it only if it meets certain criteria. Use the filter option to search for a record or a particular row(s) when the table is large.

To use the Excel filter, follow these five steps:

  1. Select the criteria or the column header based on which you need to filter the rows. Then select the Data tab in the Excel ribbon and locate the Filter option.
How to Filter in Excel?
Filter button
  1. When you click on Filter, small arrows will appear in the columns. Clicking on it will take you to a drop-down menu with options to filter the data. 
  1. All the column headers get an arrow like this . If you click on any one of them, it will display all the values based on which you can filter the data.
  2. From this list, select all the data you want to view and click OK. This will display the required information and temporarily hide other information.
How to Filter in Excel?
How to Filter in Excel?
  1. You will see the Filter iconon the column header to indicate that a filter is active and applied on the corresponding column. 

Related:

The Excel FILTER Function

Dynamic Array Functions in Excel

How to Custom Sort Excel Data? 2 Easy Steps

How to Use the Excel Filter for Text, Numbers and Dates?

In Excel, you can create four kinds of filters based on text, numbers, date, and based on criteria. These filters are mutually exclusive, which means when one filter is used another cannot be applied. Let us see how to use them one by one. 

Filters Based On Text Values

Text-based filters are very common. In this example we want the data to be filtered only for the customers who purchased a monitor. 

To do this, click on the drop-down menu, to view all the items listed in the table. 

Now, select only the items you are interested in. Excel will filter and display the relevant data. 

How to filter based on text values?
How to filter in Excel based on text values?

You can also filter data using the following predefined logical criteria: Equals, Does Not Equal, Begins With, Ends With, Contains, and Does Not Contain. 

How to use logical text filters?
How to filter in Excel using logical statements?

Filter Based on Numeric Values

Use this option when you need to filter based on numeric values.  If you have already clicked on the Filter option,  the drop-down menu appears at the column header. Otherwise, click on the Data tab and then click on the Filter option to get the arrow icon, next to the column header.

When you click on the drop-down menu, the values in that column are displayed. Now you can select the ones you are interested in. Here too, there are some logical filter options like Equals, Does Not Equal, Greater Than, Less Than, Between, Top 10, Above Average, Below Average. 

These will come in handy when you need to quickly filter based on such criteria. 

How to filter based on numeric values?
How to filter in Excel based on numeric values?

Below these options, you can find a Customized Filter that can be used to still more manipulate the numbers to filter data.

Use custom filters
Use custom filters

Also Read:

How to Record a Macro in Excel? In 6 Easy Steps (For Dummies)

How to Delete a Named Range in Excel? 3 Easy Methods

3 Best Methods to Find Duplicates in Excel

Filter Based On Date Values

You can filter based on the date values in the column. When you click on the drop-down menu in the date column, the list of values in the table appears.

You can select any of the options to get the details based on the particular date.

How to filter based on dates?
How to filter in Excel based on dates?

You have date filters as well such as Equals, Before, After, Between, Tomorrow, etc.

How to use logical date filters?
How to use logical date filters?

How to Filter in Excel Based on Multiple criteria?

You can filter the data based on different criteria across two columns. Let’s take the example below. We have a view of the sheet which has the data of who purchased the laptops.

Already applied filters are marked with a small filter icon
Already applied filters are marked with a small filter icon

You can again search the list based on the date of purchase and filter out the details of laptops that were purchased in the year 2020.

Apply separate filter in another column
Apply a separate filter in another column
Filter with Multiple Criteria
Filter with Multiple Criteria

How to Use the Sheet View Feature in Excel Filter?

The sheet where the filtered results appear is the temporary sheet. You can save many such temporary sheets.

You can find this option in the View tab→Sheet View. Click on Keep and the view will be saved as View1 for the first sheet. If you don’t want to save the view, click on exit.

Go to the View tab and click on Sheet View
Go to the View tab and click on Sheet View
Click Exit to discard the sheet view
Click Exit to discard the sheet view

How to Clear the Filter in Excel?

You can remove the filters applied to the columns simply by clicking on the Filter button again or the Clear button next to Filter.

Click on the Clear option in the Sort & Filter group
Click on the Clear option in the Sort & Filter group

Or you can clear the filter by clicking on the Clear Filter option that you get after applying the filter.

Click on the filter you want to clear and select the Clear Filter option
Click on the filter you want to clear and select the Clear Filter option

Suggested Reads:

How to Add a Watermark in Excel? 2 Easy Methods

How to Remove Hyperlinks in Excel? 3 Easy Methods

How to Use the Format Painter Excel Feature? — 3 Bonus Tips

Closing Thoughts

This article explained everything about how to filter in Excel. Filters are widely used to quickly view and extract relevant information from a database. It is one of the must-know Excel skills for everyone.

We hope that you found this article useful. Please visit our free resources centre for more high-quality Excel guides. 

Ready to take the next step and hone your skills in Excel?

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Record a Macro in Excel? In 6 Easy Steps (For Dummies)

How to Navigate the Microsoft Project 2016 Workspace

How to Move a Pivot Table in Excel? 4 Easy Ways

How to Setup Payroll Items in QuickBooks 2018

Agile vs. Scrum: Similarities and Differences [2022]

Working with Page Breaks in Microsoft Word

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)