What are the Filters in Excel?
Filters in excel can be used to filter down the data in your worksheet and hide the rest of the data from a view. This also works as the grouping of data. filters can be used from the sort and filter option from the editing section in the Home tab or we can use the keyboard shortcut using CTRL + SHIFT + L, to use filters we need to select the header of the table and use any one of the options above.
How to use Data Filters in Excel? (with Examples)
Let’s consider a data set of sales from different countries.
Follow the steps to add a filter in excel –
- Step 1: Select any cell from the dataset.
- Step 2: Click on the Data tab in the Sort & Filter group.
- Step 3: Then Click Filter to apply the filter in the given data set.
- Step 4: Click the arrow next to Country and filter the given data set by country India.
And the output is as follows:
In the second example, we apply the filter to the quarter column in the above ‘sales’ data set.
After applying the filter of the quarter, we get the below data.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
In the third example, we apply the filter of numbers of data sets.
Let’s consider the below-given data set and apply the filters on the basis of requirements.
There are the following Number filter conditions available in the data filters, as shown in the below tables.
Conditions can be:-
<> Not equal to
> greater than
< less than
<= less than or equal to
Now apply the custom auto filter on the condition is greater than 150.
After applying the sorting on Oct month is greater than 150, the output will be as follows:
Let’s apply the filter on the data set of Names that have ‘a’ in the name.
Now apply the custom auto filter on the condition it contains ‘a.’
The output will be:-
Things to Remember
- You cannot apply the filter on two datasets in a single excel sheet.
- You cannot perform any calculations or data manipulation in the filter out data.
This has been a guide to Filters in excel. Here we discuss how to use Data Filters in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –