What is Filter in Excel: How to Use Them?

In Excel, a filter is a powerful tool that allows users to display only the data that meets specific criteria, making it easier to analyze and manage large datasets. Filters can be applied to a range of data or an entire table, enabling users to hide rows that do not match the selected criteria while keeping relevant information visible. In this article, you will learn how to use filters in Excel.

The filter in Excel is an essential tool for displaying relevant data. It temporarily eliminates irrelevant entries from the view. This tool filters data according to criteria to help analyze critical data points. The article discusses the topic: What is Filter in Excel?

Content

• How To Use Filters In Excel
• Excel Filters Options
• Filter By Multiple Columns
• How To Remove A Filter In Excel
• Types of Filters in Excel
• Keyboard Shortcuts for Filters in Excel

Filter Data in an Excel Table

The Filter button is located in the ribbon. You can find the Filter command –

When you press the Filter button, you will see that the data range headers have arrows, indicating that the dataset is ready to use filters. 

Must Read – What is MS Excel?

How To Use Filters In Excel?

We must choose a column to filter the information and click the corresponding filter arrow to display the filtering options. At the bottom, all filters will display a list of unique values ​​with a checkbox to the left.

In the above screenshot of the dataset, you can see that when we click on the arrow icon in the Product Name header, a drop-down menu allows us to select the product for which we want to check the data.

Excel Filters Options

The Excel filtering options of the headers are presented to us with a dialogue box or options box allowing us to do various operations. These filter options are categorized into three segments – 

Sort Options:

• Sort from A to Z (or from highest to lowest if numeric)
• From Z to A (or smallest to largest if numeric)
• Sort by color (custom option) 

Must Explore – Free MS Excel Courses

Filter Options

• Clear Filter From (Heading Name)
• Filters by Color
• Text filters

Explore MS Excel Courses

Selection Panel 

• Search bar 
• Multiple selection list for quick filters

Filter a Database in Excel

One option to filter the data is to individually choose those values ​​we want to display on the screen. We can also use the (Select All) option to check or uncheck all items on the list. In this dataset, we have selected only the data for iPhone 13 Pro, so the filter will show only the rows where iPhone 13 Pro is mentioned.

Pressing the OK button will hide the rows that do not meet the established filter criteria. Notice that the filter arrow in the Product Name has changed to tell us we’ve applied a filter for iPhone 13 Pro. We can now see data only for iPhone 13 Pro.

The funnel sign in the header suggests that it has a filter applied.

Filter by Multiple Columns

If we want to segment further the data displayed on the screen, we can filter it by several columns. In the previous example, I filtered the rows belonging to iPhone 13 Pro. Still, if I also need to know which ones belong to the West and North regions, then I must select these options within the Region column filter, selecting the data for West.

Accepting these changes will display only the rows that meet both criteria. Note that both columns will have changed their icons to indicate that a filter has been applied to each.

This example shows that it is possible to create as many filters as there are columns in our data, and the more filter criteria we apply, the greater the data segmentation we will obtain.

How to Remove a Filter in Excel?

To remove a filter applied to a column, we must click on the filter arrow and select the Clear option filter from “Column,” where Column is the name of the column we have chosen. 

This action will remove the filter from a single column, but if we have filters applied to several columns and want to remove them all with a single action, we must press the Clear command found in the Data ⇒ Sort and filter.

Types of Filters in Excel

When we have a massive list of unique values ​​and can’t quickly identify the ones we want to select, we can use the search box to find the values ​​we need. You can also use wildcard characters such as the asterisk (*) or question mark (?) just as if we were doing a fuzzy search in Excel so that we can broaden the search results.

Text Filters In Excel

Apart from the options already mentioned to filter in Excel, when the text data type is detected in a column, a menu option called Text Filters will be displayed like the following:

• When choosing any of these options, a dialog box will be displayed to allow us to configure each available criterion. For example, choosing the Begins With option will display the following dialogue:

• If we put the letter “a” in the text box next to the “begins with” option, then Excel will show only the items in the Country column that start with the letter “a.”

• If you want to be more specific and select a particular country, then uncheck Select All.

• Select the country for which you want to check the data. For example – we have checked Afghanistan here.

Similarly, you can uncheck the boxes for the values for you do not need data.

Number Filters In Excel

Similarly, suppose Excel detects that a column contains numerical values. In that case, it will allow us to use specific filters for that data type, as you can see in the following screenshot.

Unlike the Text Filters, Excel will allow us to use the Number Filters to show values ​​greater than or equal to another or simply those greater than the average, Top 10, and even custom filter.

Date Filters In Excel

Dates are the type of data that provide us with the most filtering options, as shown in the following image:

You can select the data year-wise, month-wise, and day-wise. In the example below, we unchecked Select All and just clicked 24 inside the August month data.

Now I can see only the data for August 24, 2022.

Filter By Color In Excel

We can also filter the data by Color, as discussed above. To enable this option, the cells must have a fill color applied either by a conditional formatting rule or by directly modifying the fill color with the formatting tools. In our example, we have applied the Conditional Formatting option for cells containing employee data above PayGrade 15. 

You can see that the cells with numbers more than 15 are colored yellow. Now you can Filter by Color. 

Keyboard Shortcuts for Filters in Excel

MS Excel Filters can also be applied using keyboard shortcuts. Check them out -

You can now see the data coded Yellow.

Hope it was fun to learn about using Filters in Excel. Try your hands on the data sets to understand the functions better.

Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel