Finding data from a worksheet that contains large data content would be difficult. In that case, we often use filters to organize and narrow down the data in the worksheet. Using filters will make your sheet understandable and easy to manage.
Custom Autofilter Excel
Custom Autofilter Excel is used to sort out data in several conditions. For instance, when a cell consists of, starts with, or ends with a text, we tend to use this filter. Suppose the following dataset with all the Products in Column B and Prices in Column C.
If you need to filter the rows that only contain the word phone from column B, follow the steps:
- Activate the filter function and click on any cell in the given data range, which is B1:C12.
- From the Ribbon, open the Home tab, then Sort & Filter option, and then choose the Filter option.
- Press the filter button adjacent to Product B1 and open the Text Filters. Now, choose Custom Filter.
- Choose Contains from the drop-down menu and add the text you need to filter (phone).
- Click OK.
In the end, Rows 10 and 11 will appear as Microphone and Mobile phone in Column B when the rest of the rows are not visible.
Custom Number Autofilter
The above Autofilter is used for text, however here we will discuss the number Autofilter. Using the same dataset, let’s filter the products with prices less than $100.
- Press the filter button adjacent to Price C1 and open the Number Filters. Choose the Custom Filter option.
- Choose the “is less than” option from the drop-down in the Custom Autofilter window. Add condition 100 and click OK.
Ultimately, you will see rows 2, 4, 5, 10, and 12, because these rows have prices less than $100.
Identifying the type of relationship that two conditions have with the And option button. Well, the And option is activated by default. You will have the following choices, when you choose the operator at first and then in the leftmost drop-down list given at the top and lower of the Custom Autofilter dialog box. It also depends on the kind of values you have selected in the list:
Equals: this point holds the right to record the values given in the field that is identical to the text, date, or number
Does Not Equal: it helps in matching records of the values given in the field other than the text, date, or number
Is After: when the value in the date field appears after the date, this point records what appears after the date you added in the box.
Is After or Equal To: it helps in recording the values in the date field that appears after or selects in the related box.
Is Before: it records the value in the date field that leads to the date you added or selected in the related box.
Is Before or Equal To: it records the value in the date field that leads or is similar to the date you added in the related box.
Is Greater Than: it records the value in the field that shows the text in the alphabet, and appears after the date
Is Greater Than or Equal To: it records the value in the field that shows text in the alphabet or is identical, or the number is larger than or equal to the value you added in the related box.
Is Less Than: it records the value in the field that appears before the text in the alphabet, or is less than the number you added in the related box.
Is Less Than or Equal To: it records the value in the field that appears before the text in the alphabet or the number is less than or equal to the one you added in the related box.
Begins With: it records the value in the field that begins with the text, the part of the date, or the number you added in the related box
Does Not Begin With: it records the value in the field that begins with anything except text, the part of the date, or the number you added in the related box
Ends with: it records the value in the field that ends with the text, the part of the date, or the number you added in the related box.
Does Not End With: it records the value in the field that ends with anything except the text, the part of the date, or the number you added in the related box
Contains: it records the value in the field that has the text, the part of the date, or the number you added in the related box
Does Not Contain: it records the value in the field that carries anything except text, the part of the date, and the number you added in the related box
Final Thoughts
Considering the information given above, you can perform the filter function smoothly. You are aware of the methods of how to apply custom Autofilter Excel. You can try any of the above methods without facing any issues. Continue exploring Excel and you will become an expert.
