Auto Filter in Excel

What is Auto Filter in Excel?

Auto filter in Excel is used to filter out information that you want to exclude. For example, you can filter out specific rows or columns based on some criteria. When you use the Auto filter, you get a subset of the original values in the table after specific ranges/columns have been filtered. You have an inbuilt button Filter button in the “Home” tab. You can also use the Ctrl + Shift + L key combination. There are also various other ways to filter data which we will discuss below.

Let us take an example of a shop. The owner needs to check the sale of items to replace them accordingly. To activate the filer, select the “Date and Item” headings in the table. Here, we have chosen the date and item. Next, go to Data tab → Sort and Filter Section → Filter. When you click on the icon, the headers have a drop-down list. Now, you can filter the rows accordingly. For instance, we choose handbags under the Item list; the table displays only those rows containing the item, handbag.

Filter Options in Auto Filter in Excel

Auto filter in Excel allows us to filter specific data based on our preferences. After filtering out the specified data, we get a subset of the original table as the output. Adding Excel Auto filter to the headers gives a drop-down menu in the header row.

To set up Auto filter in Excel, the options available depend on the data type of the column. If the column contains numbers, you have filters like the following:

1. Equals to
2. Greater than
3. Less Than
4. Greater than or equal to

For text, the filters include:

1. Begin with
2. Ends with
3. Equals
4. Does Not Equal

The dates filters that can be found include:

1. Equals
2. Before
3. After
4. Tomorrow

Two Ways to Enable Automatic Filter in Excel

In Excel, there are two ways to enable Auto filter.

#1 – Through the Excel Ribbon

Go to the “Data” tab, and click on “Filter” in the “Sort & Filter” section.

#2 – Using Shortcut Keys for Auto Filter in Excel

We can use the excel shortcut keys Ctrl + Shift + L to apply filters to the column headers. You can click on any header to apply it throughout the table or on specific headers to apply it to them and press CTRL + SHIFT + L.

How to Use Excel Auto Filters?

Adding an Auto filter in Excel is simple. It helps you filter out data you don’t want to display and compactly showcase the required information. Below is an example of how to use Auto filter in Excel.

Below is a table containing information on the top marks scored by students in different subjects in a school. You have columns representing their names, grades, subjects, and scores. We can add filters to the columns to obtain the necessary information.

Let’s apply filters to the columns so that we can track the data by filtering out according to our convenience.

Examples

Auto filter allows us to hide our data per our requirements. After applying the filters, the rows that do not meet the filters” criteria are not displayed. Let us look at some interesting examples.

Example #1

Below we have a table listing some employees of an organization. Their designation and the yearly bonus are mentioned. You can find out which employee is earning a particular bonus and his designation.

• Step 1: We have already seen an example of applying a filter through the Data tab. Now. Let us apply the filter using the keys Ctrl + Shift +L. First, place the cursor anywhere in the table and press the key combination:

Filters are immediately applied to all the headers.

• Filter data in Excel by specifying conditions.

• Step 2: Suppose you want to view all employees earning a bonus of $3,000 or more. Click on the Bonus arrow and choose “Number Filters.” Here, select Greater Than.

• Step 3: In the popup window, enter the value 3000, as shown below.

• Step 4: Press OK. All the employees whose bonuses are above $3,000 are displayed.

• Turn off Auto filter in Excel

• Step 5: Remove the filter by clicking on the filter icon in “Bonus” and choosing “Clear Filter from “Bonus.”

• Step 6: The filter is removed and we get the original table.

Let us filter for all managers earning a bonus of above $3,000. First, go to the column Designation and uncheck all options except “Manager” after clicking on the Filter icon.

• Step 7: Press OK. Now, go to the Bonus column. Click on the Filter icon. You can either choose the option “Number Filters” and use “Greater than” like how we used earlier, or check the options > $3,000.

• Step 8: Press OK. Now, you get the result of all managers with a bonus greater than $3,000.

Example #2

Below is another example of items sold by a retailer in different regions. Again, let us add a text filter to check for our required data. Below you can observe the table.

• Step 1: Let us apply Auto filter in Excel to the headers by choosing any cell in the table. Go to the Sort and Filter button on the Home tab, then click Filter.

It is another way of selecting Auto filter. However, if you select multiple cells, the filter will be applied only to those cells. Hence, be careful in using it in a single cell.

• Step 2: Let us apply a text filter. Apply a filter to find regions that sold more than or equal to 50 products beginning with the letter “S.”

Go to the Item header and click on the drop-down arrow. In the drop-down menu, choose “Text Filter.”

• Step 3: Choose the option “Begins With.” Then, enter ‘S’ in the text box in the popup window, as shown below.

• Step 4: Press OK. Now, you can observe all the rows which contain either “Smart TV” or “Speakers” are displayed.

• Step 5: To get all items beginning with “S” that have sold greater than or equal to 50 units, click on the arrow in “Units.”
  • Choose the option “Number Filter.”
  • Choose “Greater Than or Equal to.”

• Step 6: Enter 50 in the text box in the popup window.

• Step 7: Press OK. Now, the table displays all items beginning with S and greater than or equal to in excel 50 items sold.

As observed, you can observe the Auto filter icon in those columns where the filter conditions have been applied.

Example #3

Let us look at different ways filters can be applied to the table below. This table contains information on the employees at a radio station.

• Step 1: Suppose you wish to only apply filters for the “Date of joining” column. If you click on cell B1 and apply filters through the Data tab à Auto filter, it will apply the filter for all the headers.

• Step 2: To apply a filter, select the entire column and then apply Auto filter.

• Step 3: Let us filter by date and choose those employees who joined after Jan-1, 2019. For this, we must use the Custom Filter option. Next, click on the drop-down arrow and select “Date Filter.”

• Step 4: In the pop-up window, under the Date of joining, choose “is after” and enter the date 1-Jan-2019 in the text box.

• Step 5: You get the rows containing employees who joined after Jan 1^st, 2019.

Important Things to Note

• You can apply the Auto filter in Excel by clicking on any cell in the table. However, select up to one cell, or the Auto filter is used only for those cells and not the headers.
• If your header is across multiple rows, select all the cells in the cell range to apply the Auto filter. 
• There should be no empty rows or columns in the table, else the cells after it will be ignored. 

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Auto Filter in Excel, with its options and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to Auto Filter in Excel. Here we explain how to use Auto filter, its different options, with examples & downloadable excel template. You can learn more from the following articles – 

• Advanced Filter in Excel
• FILTER In Excel
• Column Sort In Excel