In this article, you will learn about Excel AutoFilter. From enabling AutoFilter to applying different criteria, you will find them here. Filtering highest/lowest values, filtering by color, and specific date range, we have included almost everything regarding AutoFilter in this article.
Users mostly apply AutoFilter to analyze and explore data based on specific criteria. With this feature, users can apply multiple filters to different columns simultaneously. This flexibility allows for complex filtering scenarios and facilitates customized data analysis. By using it, users can save time and effort compared to manually sorting and filtering data.
Download Practice Workbook
Download this file to practice with the article.
How to Enable AutoFilter in an Excel Dataset
1. Apply Filter from Home Tab
- First, select any cell inside your range. We selected cell F7.
- From the Editing group of the Home tab, select Sort & Filter.
- Finally, choose Filter to apply.
You can see the Filter buttons in the header column.
2. Apply Filter from Data Tab
- Select any cell inside your range. We selected cell F7.
- From the Sort & FIlter group of the Data tab, select Filter.
You can see the Filter buttons in the header column.
3. Use a Keyboard Shortcut to Enable Filter
- Select cell D7 inside your range.
- Press Ctrl + Shift + L to apply the filter.
You can see the Filter buttons in the header column.
How to Apply AutoFilter to One Column/Criteria in Excel
We want the product details that are delivered only in the North region.
- Click the Filter button beside Delivery Region.
- Now, check the North option and press OK.
You will see all the products that are sent to other regions except North are hidden.
How to Apply AutoFilter for Two or More Criteria in Excel
We want the product details with the following criteria.
- Delivery region: North,
- Delivered value over $6000,
- Delivery status: Delivered.
- Click the Filter button beside Delivery Region.
- Now, from the selection of unique data, select North and press OK.
- Next, click the Filter button beside Product Price.
- From Number Filters, select Greater Than.
- In the Custom AutoFilter dialog box, put the desired number. We are inserting 6000.
- Press OK to apply.
- To filter the delivery status, click the Filter button beside Delivery Status.
- Uncheck Pending to filter only the delivered products and press OK.
Finally, you will be left with the data that fulfills your criteria.
How to Filter Highest and Lowest Values in Excel
1. AutoFilter Highest Values
- Click the Filter button next to Product Price.
- From Number Filters, select Top 10.
- In the middle box, choose the number of highest values you want. We insert 5.
- Press OK to apply changes.
You will see the 5 highest valued products after the filter.
2. AutoFilter Lowest Values
- Click the Filter button next to Product Price.
- From Number Filters, select Top 10.
- In the left box, choose Bottom to get the lowest prices.
- In the middle box, choose the number of lowest values you want. We input 5 here.
- Press OK to apply changes.
You will see the 5 lowest valued products are filtered out.
How to Filter by Color in Excel
1. Filter by Font Color
We set the font color of the status Pending as red and Delivered as green.
- Click the Filter button next to Delivery Status.
- Choose Filter by Color and select red to see the Pending items.
As you can see, the Pending items are filtered out leaving the Delivered items hidden.
2. Filter by Cell Color
The cells with Pending status are colored red and Delivered are colored green.
- Click the Filter button next to Delivery Status.
- Choose Filter by Color and select green to see the Delivered items.
As you can see, the Delivered items are filtered out leaving the Pending items hidden.
How to Filter for a Specific Date Range in Excel
We want the product lists that are delivered or will be delivered between 10 June 2023 to 15 June 2023.
- Click the Filter button next to Delivery Date.
- From Date Filters, select the Between option.
- Insert the dates in the Custom Autofilter dialogue box and then press OK.
- You will find the products listed for delivery within those dates.
How to Use AutoFilter with Search Box in Excel
We want to find the product’s name with the word Oil in it.
- Click the Filter button next to Product.
- In the Search Box, type Oil and press OK.
Just like you typed, products with the word Oil in their names are filtered out.
How to Filter Blank Cells in Excel
There are two blank rows in our dataset.
- Click the Filter button next to Delivery Region.
- Uncheck Blanks and press OK.
As you can see, the blank rows are hidden now.
How to Use Custom AutoFilter in Excel
1. Filter for One Criterion
We want to see the region names with th at the end.
- Click the Filter button next to Delivery Region.
- From Text Filters, choose Custom Filter.
- In the Custom Autofilter, from the left drop-down menu, choose ends with and type th in the box.
- Finally, press OK.
As you can see, North and South regions have been filtered.
2. Custom Filter for Two Criteria
We have two criteria to filter our dataset.
1. Product name with Oil,
2. Product name with Jar.
- Click the Filter button next to Product.
- From Text Filters, choose Custom Filter.
- In the Custom Autofilter, from both the left drop-down menus, choose contains and type Oil and Jar in the boxes. Also, check the Or option.
- Finally, press OK.
As you can see, products with the words Oil or Jar in their names are filtered out.
3. Custom Number AutoFilter
We want to filter products that are priced greater than $6500 and less than $15000.
- Click the Filter button next to Product Price.
- From Number Filters, select Custom Filter.
- In the left drop-down boxes, choose is greater than and is less than. And in the right boxes, insert the upper limit and lower limit of prices with And criteria.
- Press OK to apply changes.
The filtered data are shown below.
How to Delete Filtered Rows in Excel
We want to delete the delivered products from the list.
- Click the Filter button next to Delivery Status.
- Uncheck Pending to select Delivered products and then press OK.
- Now, select the filtered rows and go to Cells group and select Delete Sheet Rows from the Delete drop-down menu.
- As you can see, the delivered items have been deleted.
- Again, click the Filter button and check Pending to show the rest of the rows.
These are the rows that are left after deleting the delivered items.
How to Clear AutoFilter in Excel
1. Clear A Specific Filter
- Click the Filter button in the Delivery Status.
- Then choose Clear Filter from “Delivery Status” and click on OK.
You can see the whole dataset without any filter.
2. Clear All Filters
- To remove Filter altogether, deselect the Filter option from the Sort & Filter of Home tab.
You can see the dataset without Filter buttons.
How to Update an AutoFilter
- To update Filter after adding or removing data, choose Reapply from the Sort & Filter drop-down menu.
Filter not Working in Excel
- Reapply Filter. Look at previous method to see how.
- Clear out all Filter. Then apply again.
Frequently Asked Questions
1. Can I use Wildcards with AutoFilter in Excel?
Ans: Yes, you can use Wildcards with AutoFilter in Excel. You can use the asterisk (*) as a wildcard to represent any number of characters and the question mark (?) to represent a single character in your filter criteria.
2. Can I filter by text length using AutoFilter in Excel?
Ans: No, you cannot directly filter by text length using AutoFilter in Excel. AutoFilter allows filtering based on specific values, but it does not provide an option to filter by the length of text in a column. You would need to use other functions or techniques to achieve this type of filtering.
3. How do I sort data using AutoFilter in Excel?
Ans: Select the range of data you want to sort.
- Go to the Data tab and click on the Filter button to enable AutoFilter.
- Click on the dropdown arrow in the column header you want to sort.
- In the dropdown menu, select Sort A to Z (ascending) or Sort Z to A (descending). The data will be sorted based on your selection.
Conclusion
Excel AutoFilter is an amazing tool that allows users to filter data with great proficiency. It enables quick and flexible data analysis by providing a dropdown menu for each column, where users can choose specific criteria or apply custom filters. We covered everything there is to know about AutoFilter. After this, we expect you to filter your data with ease and without any trouble.
Excel AutoFilter: Knowledge Hub
<< Go Back to Filter in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
