How to Filter in Excel (Table of Contents)
How to 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 option in excel. 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 in excel.
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.
How to Add a Filter in Excel?
Below are the following examples of the filter in excel.
Firstly take into consideration of below data set.
For this how do we apply filter?
To apply the filter in excel. 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 in excel. Probably you have not noticed this much.
First, select the data and click on FILTER option under the editing section.
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. 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.
Now if I want to filter out only “Delhi” then I have 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 excel shortcut we can apply the filter to the data.
Following are shortcuts for the filter in excel –
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.
Working With 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.
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.
How to Filter Data By Typing the Value in Excel?
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 in excel.
- 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”.
Recommended Articles
This has been a guide to How to Filter in Excel. Here we discuss top 3 methods to add a filter in excel (Filter Option, Data Tab, By and Shortcut Key) along with examples and downloadable excel template. You may learn more about excel from the following articles –
- Filter in Excel VBA
- Excel Reverse Order | Using Sort
- Create Contour Plots in Excel
- Excel XOR Function
- How to Reduce Excel File Size?
- AutoFilter using VBA
- How to Filter Pivot Table?
- Use Advanced Filter in Excel
- Add Filter in Excel Examples
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
