Excel AutoFilter – Filter by Cell Color, Font Color, Date, Text

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.

 

AutoFilter_1

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.

 

Apply AutoFilter by Right Click

 2.  On the Data tab à go to “Sort & Filter” tab Filter ( Alt+A+T)

AutoFilter by Second way

 

  3.  On the Home tab àEditing Group click “Sort & Filter” à and click “Filter” (Alt+H+S+F)

AutoFilter by 3rd way

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:-

AutoFilter by Ctrl+Shift+L

Apply AutoFilter in Excel

First, we will select the headers of the data set and by any of above method, apply the filter.
 
In the below-given image, there is a filter drop-down arrow in each header of the row. Every drop down arrow has a separate feature.
 
Filter dates, text and numeric values from their respective column date, text, and numeric values.
 
If the data set has any colored cell or font or an icon, we can filter according to cell or font color or icon also.

 

Applying AutoFilter Image

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”.

Filter By Text

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”.

AutoFilter By Text

 

2. Excel AutoFilter by Cell Color

Filter the data by cell color, some cells are colored due to conditional formatting. So in that case, we can filter the data according to cell color option.
 
Filter according to cell color, go to the data column which has the colored cells. Click the filter drop-down arrow and select option “Filter by Color”. See image below:-

Excel AutoFilter by Cell Color

After click on the drop down we will get the following image:-

Excel AutoFilter by Cell Color

Select the cell color which you want to filter and press “OK”.

3.  Excel AutoFilter by Numbers Values

Filter the data according to numeric values, here we have many options like:- values greater than; less than; between; top 10 etc.
 
For filter according to number values, go to the related column drop down arrow where we want to apply a filter. See image below:-

Excel AutoFilter by Number

Select an option from the above-given list, choose “Between” and fill the criteria of both values as shown in the below image:-

 

Excel AutoFilter by Number

And press “Ok”, we will get the below-given result. See image below:-

Excel AutoFilter by Numbers

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.

 

Excel AutoFilter by Dates

Below is the example of filter the data between two date range:-

Excel AutoFilter by Dates

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.

Excel AutoFilter by Font Color

Now if we want to filter the data from cell’s font color then we have to follow below-given instruction in the image:-

Excel AutoFilter by Font Color

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:-

Excel AutoFilter by Font Color

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.

Excel AutoFilter by Text Box

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.

Excel AutoFilter by Search Box

 

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:-

Excel AutoFilter by Search Box

And click “OK” the data will be as follows:-

Excel AutoFilter by Search Box

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:-

Excel AutoFilter by Cell Icons and Cell value

And see the result below, filtered by “Icon”.

Excel AutoFilter by Cell 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

 

Excel AutoFilter by Cell Value

And press “OK”, we will get the following image:-

 

Excel AutoFilter by Cell Value

8. Excel AutoFilter by Wildcard Character

There are three wildcard characters we use in Excel AutoFilter.

Excel Wildcard Characters

 

9.  Excel AutoFilter  – How to Remove

There are 4 methods to remove a filter from the data set.

1st Method

Click filter drop-down arrow and select “Clear Filter From particular field”.  See image below:-

 

Remove Filter

2nd  Method

Go to Home tab –> click “Sort & Filter” in editing group –> Clear

 

Remove Filter

3rd Method

Go to Data Tab –>”Clear”

Remove Filter

4th Method

Using shortcut key (Ctrl+Shift+L)

Select headers of the dataset and press shortcut “Ctrl+Shift+L” to remove the filter.

 

Thanks

Narendra

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.