We can quickly search for specific information on our spreadsheet with the auto filter. In this article, we will learn the ways we can automatically filter such as filtering by color, text or number. We will also learn how to use custom auto filters.
Figure 1 – How to automatically filter
How to use the autofilter
To enable autofilter, we can follow these steps:
- We will go to the Data tab and click on the Filter button. Arrows will appear on our header rows immediately.
Figure 2 – Range autofilter
- When we click on these arrows, we will see that we have a lot of options
Figure 3 – Auto filter list
- As shown in Figure 3, these drop-down arrows help us to sort our data alphabetically, by color and text filters. We can also find a pop-menu where we can uncheck any cell to make it disappear from the list. However, we can’t uncheck all cells, because Excel will send us a message that the list is completely empty by greying the OK area.
Figure 4 – Auto filter process
- To add all the cells we removed from the Column, we will click on Clear Filter from “Zone”
Figure 5 – Autofilter field
How to Filter By Color
- To view by color, we will take our cursor around the Filter by the Color option to see the different cell colors.
Figure 6 – Macro filter
- Next, we will click on the colors we want and select OK
Figure 7 – How to use the autofilter field
Autofilter Mode for Specific Text
- We will click again on the filter arrow, select Text Filters and then choose an option. In our example, we will select Begins with
Figure 8 – Auto filter
- Next, we will see the Excel Custom Autofilter box. Here, we may decide to filter for two options as displayed
Figure 9 – Custom autofilter
- We will filter for A using the autofilter and tap OK
Figure 10 – How to automatically filter
Auto Filtering with numbers
- We will click on the arrow in the Amount Column and choose Number filters
- Next, we will select Number filters and make a choice. In this example, we will select Between.
Figure 11 – How to use the auto filter
- Next, we fill in our criteria. We want numbers between 80 and 250.
Figure 12 – How to use the excel custom autofilter
- We will click OK
Figure 13 – Using the range autofilter
Using the Excel Advanced Autofilters
- We will click on Advanced right next to filter in the Data Tab
- Next, we will specify the listed range we want to filter. In the criteria range, we will enter the parameters we want and check Filter the list, in-place.
Figure 14 – Advanced filters
- Now, we will select OK.
Figure 15 – Using the Autofilter
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment