Excel AutoFilter and How to apply it?
Excel Auto Filter is a method of data filtering where we can filter the huge Excel data set. Suppose we have a huge data set, it will be difficult to analyze due to a large amount of data set. So Excel has a feature Auto Filter, where we can filter our data by date, by cell color, by font color, and by its many options. Applying these can reduce the time by many folds. In this tutorial, we will learn about all these features of Excel Auto Filter.
4 Ways to Apply AutoFilter in Excel
Below are 4 methods to apply AutoFilter in Excel
1. By Right Click Properties
Put the cursor in the data where we want to filter the result and we will have the following options.
2. On the Data tab à go to “Sort & Filter” tab Filter ( Alt+A+T)
3. On the Home tab àEditing Group click “Sort & Filter” à and click “Filter” (Alt+H+S+F)
4. Using shortcut (Ctrl+Shift+L)
Just put the cursor in the header line and press “Ctrl+Shift+L” and the filter will be applied. See image below:-
Apply AutoFilter in Excel
In this tutorial, we will discuss here the different methods of Excel Auto Filter feature.
1. Excel AutoFilter by Cell Containing Text
In the below-given image, filter the data by text values. First, go to “Name” column drop-down arrow and select “Text Filters”. Text Filter contains “Equal”, “Does not equal”, ”Begin With”, “Ends With”, “Contains”, “Does not contains” and “Custom Filter”.
Choosing the “Custom Filter” option, data containing the text “Begin with” “M” and “Ends with” “N” and press “OK”. We will get the following result, all the text beginning with “M” or end with “N”.
2. Excel AutoFilter by Cell Color
After click on the drop down we will get the following image:-
Select the cell color which you want to filter and press “OK”.
3. Excel AutoFilter by Numbers Values
Select an option from the above-given list, choose “Between” and fill the criteria of both values as shown in the below image:-
And press “Ok”, we will get the below-given result. See image below:-
4. Excel AutoFilter by Cell Dates
Filter by date has a maximum option. Here we can select all months or selected months figure and options are given at the right side are the huge variety of filter option. We can select in a particular year’s particular month of all the months or selected months only.
Below is the example of filter the data between two date range:-
Here is the data between two date ranges. By applying the same method we can produce the more accurate result.
5. Excel AutoFilter by Cell Font Color
We can filter the data even by font color also. Below is the image where the selected column, some cells have a color and some cells font color is different.
Now if we want to filter the data from cell’s font color then we have to follow below-given instruction in the image:-
If any cell’s font has a color then “Filter by Font Color” option will there else this option will not be there. We need to select the font color by which we want to filter the data and result will be as displayed as below image:-
In this column there are two cell which containing the same font color, so the filter option is showing only two cell font data set
6. Excel AutoFilter by the Search Box
We can filter the data by search box also but we will apply this option if any cell has text value, then we will type the text and it will find out the result.
In the above image, we can see that in the search box we have type only “ma” and all the records containing “ma” text presents, we will pick “Manoj” here.
Now if we want to add another person name to the list, “how to do that?” Click “OK” then again go to search box and type your desired name but don’t forget to tick on “Add current selection to filter” checkbox and then click “OK”. See image below:-
And click “OK” the data will be as follows:-
7. (a) Excel AutoFilter by Cell Icons and Cell value
If the data set has “icons” then filter the data by cells “icons”. For that, we need to right-click on the columns which contain icons. See below image:-
And see the result below, filtered by “Icon”.
And same way we can filter the data by “cell value” also. For that right click on the data and select filter and then
(b) Excel AutoFilter by Cell’s Value
And press “OK”, we will get the following image:-
8. Excel AutoFilter by Wildcard Character
There are three wildcard characters we use in Excel AutoFilter.
9. Excel AutoFilter – How to Remove
There are 4 methods to remove a filter from the data set.
Click filter drop-down arrow and select “Clear Filter From particular field”. See image below:-
Go to Home tab –> click “Sort & Filter” in editing group –> Clear
Go to Data Tab –>”Clear”
Using shortcut key (Ctrl+Shift+L)
Select headers of the dataset and press shortcut “Ctrl+Shift+L” to remove the filter.