How to Filter in Excel? | Top 3 Methods to Filter Excel Data

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 is the filter, 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 filter we can filter only “Bangalore” city data.

Filter Options in Excel

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

Firstly take into consideration of below data set.

For this how do we apply 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. Probably you have not noticed this much.

Step 1: First, select the data and click on 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 “City” drop-down list and I can see all the cities which are there in the filter applied data range.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

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 simple keyboard shortcut we can apply the filter to the data.

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 “Greater Than” option.

Now we will see below window.

Enter the number as 10000.

This will filter out numbers which 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 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 “Number Filter” option.
If the rows are colored then you can filter specific colored row as well by using “Filter by Color”.