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.
Table of contents
- What is Auto Filter in Excel?
- Filter Options in Auto Filter in Excel
- Two Ways to Enable Automatic Filter in Excel
- How to Use Excel Auto Filters?
- Important Things to Note
- Frequently Asked Questions (FAQs)
- Download Template
- Recommended Articles
- Auto Filter in Excel allows you to display rows after adding filters to the table. We can add filters to one or all columns or even individual cells.
- Thus, only specific rows are displayed, while other rows which do not satisfy the filter condition are not displayed.
- You can apply Auto filter through the Excel Ribbon or the shortcut key: Ctrl + Shift + L. You can apply the filter directly using the checkboxes or apply Customer Filters as required.
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:
- Equals to
- Greater than
- Less Than
- Greater than or equal to
For text, the filters include:
- Begin with
- Ends with
- Does Not Equal
The dates filters that can be found include:
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
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.
Steps to use Auto filters in excel are as follows –
- Place the cursor on any cell in table A1:D15.
• Click on “Filter” under “Sort & Filter” in the “Data” tab.
- The filter is applied in the row header, giving us a drop-down menu.
- Let us apply a filter to check the top scorers of grade 4. Go to the Grades column, click on the drop-down list, and un-check options 5,6 and 7.
- Press OK. You get the top scorers of Grade 4.
- Now, you can also apply other filters. For this, you should first remove the previous filter. Then, click on the filter symbol under Grades.
- In the drop-down menu, check the box “Select All.”
- All the other boxes get checked. Press OK. The Grades filter is removed.
Thus, you can click on any header and apply any other filter as per requirement.
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.
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.
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.
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 1st, 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)
The Auto filter can be applied in Excel by clicking on the Excel ribbon. Next, click on either Data → Filter or Home → Sort and Filter (Editing group) → Filter. When you do this, drop-down arrows are added to all headers, which help you apply the appropriate filters.
Auto Filter may not work if you have empty rows or columns in the table. Also, to apply the filter to all headers, click on only a single cell in the cell range. If you select multiple cells, or only a particular column, the Auto filter will only be applied to them.
Both Auto Filter and advance auto filter in Excel are present in the Data tab. The auto filter turns on filtering for selected cells, while Advanced filters out using complex criteria. It allows you to create the filter criteria outside blank cells, pick the criteria mentioned and apply them.
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.
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 –