Sanjida Ahmed
Excel sheets with massive datasets are hard to deal with. But if you can filter the dataset according to your specific need, then the task becomes quite easier to handle. In this article, we will show you how to perform a custom filter in Excel.
Download Workbook
You can download the free practice Excel workbook from here.
5 Ways to Perform Custom Filter in Excel
In this section, we will show you how to filter values in Excel in customized ways using Excel command tools, Macro etc.
Above is the dataset that we will be using to perform our custom filter.
1. Filter Value Based on Number in Excel
You can perform a customized filter in Excel and extract data based on specific numbers.
Steps:
- Select any cell within the range.
- In the Home tab, select Sort & Filter -> Filter from the Editing group.
- A drop-down arrow will appear beside each column header.
- Click on the arrow beside the column that you want to filter. We wanted to filter based on Total Sales so we clicked the drop-down arrow right beside it.
- From the drop-down list, select Number Filters -> Custom Filter.
- A Custom AutoFilter pop-up box will appear. Choose the options that you require from the drop-down arrow list. We wanted to extract the Total Sales value between 500 and 900 so we picked is greater than from the first drop-down option and wrote 500 in the label box beside it.
- As we wanted two options to be true so we checked the And option. If you want the result based on only one condition then uncheck And and check Or option.
- From the second drop-down list, we picked is less than and wrote 900 in the label box beside it.
- Press OK.
We got the Product details that hold the Total Sale value of 750, which is between 500 and 900.
2. Filtering Data Based on Specific Text
Similar to the previous section, you can also implement a custom filter to your dataset according to specific text values.
Steps:
- As shown above, select any cell within the range.
- In the Home tab, select Sort & Filter -> Filter from the Editing group.
- A drop-down arrow will appear beside each column header.
- Click on the arrow beside the column that you want to filter. This time we will filter based on Month so we clicked the drop-down arrow right beside it.
- From the drop-down list, select Text Filters -> Custom Filter.
- From the appeared Custom AutoFilter pop-up box, choose the options that you require from the drop-down arrow. We wanted to extract the Product details of Months ahead of June except for July, so we picked is greater than from the first drop-down option and wrote June in the label box beside it.
- As we wanted two options to be true so we checked the And option.
- From the second drop-down list, we picked does not equal and select July from the drop-down list in the label box to exclude it from the condition. You can also manually write the month name here.
- Press OK.
We got the Product details for Months ahead of June except for July through a customized filter in the Excel worksheet.
Read More: How to Filter Unique Values in Excel
3. Save Custom Filter in a Table in Excel
Until now we are showing you how to custom filter with a dataset, but you can save the custom filter in a table too. To do that you have to convert the dataset into a table. Let’s see how to do that in Excel.
Steps:
- Select the dataset.
- From the Home tab, select Format as Table.
- You can give your table a customized name or you can leave the name as it is. We wanted to store a name for our table so we named it CustomTable. Again, this is not mandatory.
- Once you have done those, you will notice a drop-down arrow will appear beside each column header.
- Your dataset is now converted as a table with filter options. You can perform the custom filter shown in the above sections or any other way you want. We wanted to see the Product Details for the month of July so we unchecked the Select All and checked only July.
- Press OK.
Only the Product details from July will be shown in the table.
3.1. Execute Custom Filter for Two Columns in a Table
After filtering one column of a table, you can filter another column if you want. Like after extracting only the information of July, now we want to have the Product details that hold the Total Sale value from 500 to 800.
- To filter based on Total Sales, we clicked the drop-down arrow right beside it.
- From the drop-down list, select Number Filters -> Custom Filter.
- From the appeared Custom AutoFilter pop-up box, we picked is greater than from the first drop-down option and wrote 500 in the label box beside it.
- As we wanted two options to be true so we checked the And option.
- From the second drop-down list, we picked is less than and wrote 800 in the label box beside it.
- Press OK.
Now you will get the Product details that were manufactured in July and has a Total Sale of 750 (which is between 500 and 800).
Read More: How to Filter Multiple Columns Simultaneously in Excel
Similar Readings
- How to Filter Multiple Rows in Excel (11 Suitable Approaches)
- Excel Filter Data Based on Cell Value (6 Efficient Ways)
- How to Use Text Filter in Excel (5 Examples)
- Shortcut for Excel Filter (3 Quick Uses with Examples)
- How to Filter Multiple Values in One Cell in Excel (4 Methods)
4. Perform Custom Filter Using Advanced Filter in Excel
Apart from using only the drop-down filter option, you can also utilize the Advanced feature in Excel to filter the data in a custom way.
Steps:
- Select Advanced from the Data tab.
- You will notice that there will be a pop-up box named Advanced Filter which already has the range of your dataset in the List range box.
- Now what you are going to do is, go back to the dataset, in another cell store the data based on which you want to perform the filter. For instance, we wanted to extract data for Mobile, so we stored Mobile in Cell G5 and named the column as Product Name in Cell G4.
- Now again, select the Advanced option, in the pop-up box, define the Criteria range by dragging the newly defined cells. In our case, we dragged through Cell G4 and G5 as input values in the Criteria range.
- Press OK.
You can see only the details of Mobile are there in our dataset.
5. Macro Record to Filter Data in Custom Way in Excel
There is another quick and effective way to save any kind of custom filtering of data in Excel using Macro. Using macro you can save the custom filter and apply that later in another sheet in Excel. Steps to implement macro to filter data in a custom way are given below.
Steps:
- From the Developer tab, select Record Macro.
- Name the macro in the Record Macro pop-up box. We named it MacroCustom in the Macro name box.
- Press OK.
- Now you can perform any type of filter in your dataset, macro will record it and apply the exact filter into another worksheet. For example, after pressing Record macro, we wanted to extract the Total Sale of July so we unchecked the Select All option and checked July only from the drop-down list by clicking the arrow beside the column header.
- After pressing OK it will show us only the Product details for July.
- Now we will select Stop Recording from the Developer tab. It will record the exact procedure that we followed to filter data.
- Now go to another worksheet that you want to filter in the same way. Select Macros from the Developer tab.
- Select the macro name that you provided before. For our case, we selected the MacroCustom here.
- Press Run.
The exact filter process that you followed in the previous worksheet will be applied here. Look at the picture below that holds only the Product details manufactured in July.
Read More: Filter Multiple Criteria in Excel with VBA
Conclusion
This article showed you how to perform a custom filter in Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.
