How to Perform Custom Filter in Excel (5 Ways)

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.

Dataset to perform custom filter in Excel

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.

Select Filter to perform custom filter in Excel

  • 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.

Perform custom filter based on numbers in Excel

  • 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.

custom filter based on numbers in Excel

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.

perform custom filter based on texts in Excel

  • 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.

custom filter based on texts in Excel

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.

perform custom filter in a table in Excel

  • 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.

perform custom filter in a table based on numbers in Excel

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.

perform custom filter in a table for two columns in Excel

  • 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


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.

select advanced to perform custom filter in Excel

  • 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.

list range to perform custom filter in Excel

  • 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.

criteria range to perform custom filter in Excel

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.

record macro to perform custom filter in Excel

  • 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.

stop recording macro to perform custom filter in Excel

  • 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.

run macro to perform custom filter in Excel

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.


Further Readings

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo