How to Filter in Excel?

Filter Data in Excel

The filter is the most often used tool in excel especially when we work with a large amount of data. Usually working with a large amount of data is challenging so to deal with relevant information from the large data set we have filter options.

In this article, we will expose you to how to add a filter in excel. This article covers what the filter is, how to apply the filter, how to work with them, and what are the things we need to vary when we work with the filter option.

Filter, as the name suggests, is used to filtering the data as per the criteria are given. We can filter out a particular set of data from the huge database and remove all other elements of the data. For example, if you have city-wise sale data, then by applying a filter, we can filter only “Bangalore” city-data.

Filter Options in Excel

Firstly take into consideration the below data set.

For this, how do we apply a filter?

To apply the filter, we have several ways; I will show you each one of them now.

Method 1: Apply Filter from FILTER Option under HOME tab

Under the HOME tab, we have a FILTER option. You probably have not noticed this much.

Step 1: First, select the data and click on the FILTER option under the editing section.

Step 2:  As soon as you click on the filter option. It will apply the Filter to the selected data range.

As we can see, a small drop-down kind of arrow is the filter.

Step 3: Click on the drop-down list to see what the items it has in it are.

I have clicked on the “City” drop-down list, and I can see all the cities which are there in the filter applied data range.

Step 4: Now, if I want to filter out only “Delhi,” then I have to select only “Delhi” and uncheck all other boxes.

It will filter out only “Delhi” data.

Method 2: Filter From Data Tab

We can also apply the filter from the Data tab as well. Under the Data tab, we have a FILTER option.

Method 3: Apply Filter by Using Shortcut Key

This is my favorite… Without breaking any sweat with a simple keyboard shortcut, we can apply the filter to the data.

The following are shortcuts for the filter–

The shortcut key to apply a filter is SHIFT + CTRL + L

Another shortcut key to apply a filter is ALT + D + F + F

Both these keys are toggle key to apply and remove the filter.

How to Apply Filter in Excel?

Working with filter is fun because it almost fits all the needs that we wish to do. As we have seen in the above example, if we want to filter out any particular data, then we just need to select that and unselect the rest of the items.

Similarly, when we try to filter numbers, we can filter out numbers we can use advanced techniques.

Example #1

For example, in the above data, if we want to filter the numbers which are greater than 10000, then open the filter in the invoice value column.

After opening the filter option in that column, go to “Number Filter” >>> here, we see various options; choose the “Greater Than” option.

Now we will see the below window.

Enter the number as 10000.

This will filter out numbers that are greater than 10000.

This symbol is the indication that the filter has been applied to the column.

As we can see in the above image, it has filtered all the numbers >10000.

If you want to filter numbers which are greater than 10000 but less than 20000, then in the same window, choose less than value.

Now in the below box, enter 20000.

It will filter the number which is greater than 10000 but less than 20000.

Example #2

Filter Data By Typing the Value

If the data is large, then we will get flinty of values when we open the filter. Below is an example of the same.

In these cases, we can simply type the value that we need to filter. If we want to filter the value “Prd 1,” then we just simply need to type the value in the search box.

As we can see above, it has filtered only that value from the huge list. We get the following data.

Things to Remember

Below are the things to remember while applying the filter.

• If the list is huge, then type the value that you want to filter; as soon as you type the value, it will start filter out all the possible matches.
• If you want to filter number which is greater than or less than a specific number, then use the “Number Filter” option.
• If the rows are colored, then you can filter specific colored rows as well by using “Filter by Color.”

Recommended Articles

This has been a guide to How to Filter in Excel. Here we discuss the top 3 methods to add a filter in excel (Filter Option, Data Tab, By and Shortcut Key) along with examples and a downloadable template. You may learn more about excel from the following articles –

• VBA Filter
• How to Filter Pivot Table?
• Advanced Filter in Excel
• Types of Filters in Power BI