Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Filter Multiple Columns Simultaneously in Excel (4 Ways)

If you want to filter multiple columns simultaneously in Excel, you have come to the right place. Filtering data is a great way to find information quickly especially when the worksheet contains a lot of input. When you filter a column, then the other columns are filtered based on the filtered column. So, filtering multiple columns simultaneously in Excel can be a little tricky. There are certain easy ways of filtering data of multiple columns simultaneously in your worksheet. Today we will discuss 4 easy ways of filtering multiple columns.


Download Practice Workbook

Download this practice sheet to practice while you are reading this article.


4 Methods to Filter Multiple Columns Simultaneously in Excel

In the following dataset, you can see the ID Number, Sales Rep., Location, Product, and Sales columns. After that, using this dataset, we will go through 4 easy methods to filter multiple columns simultaneously in Excel.

Here, we used Excel 365. You can use any available Excel version.

Dataset to create Excel Filter Multiple Columns Simultaneously


1. Applying Filter Option to Filter Multiple Columns Simultaneously in Excel

In this method, we will use the Filter option to filter multiple columns simultaneously in Excel. Filter option is a common tool in excel to arrange your data. It is also effective when you are filtering multiple columns. Suppose we need to filter column C where their names start from the letter A respectively to column D where the location is USA.

Steps:

  • First of all, select the header of the data table by selecting cells B4:F4 to apply the filter option.
  • Then, go to the Data tab.
  • After that, from the Sort & Filter group >> select the Filter option.

Applying Filter Option for Excel Filter Multiple Columns Simultaneously

As a result, you can see the Filter icon on the header of the dataset.

  • At this point, to filter column C, we will click on the Filter icon of column C.

  • At this point, we will select the Names that start with A, and we will unmark the other names.
  • Then, click OK.

As a result, you can see that the data table has been filtered and it is showing data for names that start with A.

  • Furthermore, we will click on the Filter icon of column D.

  • Moreover, we will only mark the USA as the Location, and we will unmark the other Locations.
  • Along with that click OK.

Filtering Location USA for Excel Filter Multiple Columns Simultaneously

Hence, you can see the dataset is now showing the data of names that start with A and that are present in the USA.

Therefore,  we have our filtered data according to name and location.

Similar Readings:


2. Using Advanced Filter Feature to Filter Multiple Columns in Excel

Advanced Filter tool is an amazing tool to filter multiple columns at the same time. Here, we want to filter the names that start with A, and the location is the USA. You can see these criteria in the Criteria box. Now we will filter the data by the “Advanced Filter” tool based on the Criteria.

Criteria Table for Excel Filter Multiple Columns Simultaneously

Steps:

  • In the beginning, we will go to the Data tab.
  • Afterward, from the Sort & Filter group >> select Advanced Filter.

Use of Advanced Filter Feature for Excel Filter Multiple Columns Simultaneously

At this point, an Advanced Filter dialog box will appear.

  • Then, select cells B4:F18 as List Range.
  • Along with that, select cells B22:F23 as Criteria range.
  • Here, make sure to select Copy to another location.
  • Furthermore, select cell B26 in the Copy to box.
  • Moreover, click OK.

As a result, you can see the Filtered Columns data table is now showing the data of names that start with A and that are present in the USA.

Therefore,  we have our filtered data according to name and location.


3. Use of OR Logic to Filter Multiple Columns Simultaneously in Excel

You can filter multiple columns simultaneously using the OR function. This function will provide you with a “logical option” and based on that you can do your job. We will use the same datasheet. Suppose we need to filter column “E” by Book and column “F” where the value is greater than “15000”. You can see the criteria in the Criteria table.

Applying OR Function for Excel Filter Multiple Columns Simultaneously

Steps:

  • In the beginning, we add a column named “Filter” to our dataset.

  • After that, we type the following formula in cell G5.
=OR(E5=$C$21,F5>$C$22)

Formula Breakdown

  • OR(E5=$C$21,F5>$C$22) → the OR Function determines whether any logical tests are true or not.
  • E5=$C$21 → is logical test 1
  • F5>$C$22 → is logical test 2
    • Output: FALSE
  • Explanation: Since none of the logical tests are true, the OR function returns FALSE.
  • After that, press ENTER.

As a result, you can see the result in cell G5.

  • At this point, we will drag down the formula with the Fill Handle tool.

Using Fill handle tool for Excel Filter Multiple Columns Simultaneously

Hence, you can see the complete Filter column. Next, we will filter the TRUE from the Filter column.

To do so, we have to add a Filter icon to the headers.

  • Therefore, we will select the header of the data table by selecting cells B4:F4.
  • Then, go to the Data tab.
  • After that, from the Sort & Filter group >> select the Filter option.

As a result, you can see the Filter icon on the header of the dataset.

  • At this point, to filter column TRUE from column G, we will click on the Filter icon of column G.

  • At this point, we will mark TRUE, and we will unmark FALSE.
  • Then, click OK.

Filtering TRUE to Filter Multiple Columns Simultaneously

Finally, we can see the result based on the criteria.

Here, one thing must be remembered if any of the logical values match the criteria, the OR function will show that. That’s why we get Pen, Pencil, and Smartwatch instead of only Book because the other logical value was matched with the criteria.


4. Applying FILTER Function in Excel

In this method, we will use the FILTER function to filter multiple columns simultaneously in Excel. This is a quick and easier method to do the task.

Here, using the FILTER function we will filter the dataset based on the location USA.

The criteria is given in the Criteria table.

Use of FILTER Function for Excel Filter Multiple Columns Simultaneously

Steps:

  • First of all, we will type the following formula in cell B24.
=FILTER(B5:F18,D5:D18=D5,"")

Formula Breakdown

  • FILTER(B5:F18,D5:D18=D5,” “) → the FILTER function filters a range of cells based on criteria.
  • B5:F18 → is the array.
  • D5:D18=D5 is the criteria
  • ” ” → returns a blank cell when the criteria are not met.
  • After that, press ENTER.

Therefore, you can see the Filtered Columns based on the location USA in cells B24:F26.


Things to Remember

  •  While using the advanced filter tool, you can choose “Filter in the list” to filter the data in the same place where you select the range.
  •  If any one of the values in the “OR” function is true, The result will show “True” whether the other values are right or not.

How to Apply Multiple Filters in One Column in Excel

Here, we will show you how you can apply multiple filters in one column. We will use the Custom Filter feature for this purpose. Here, we will apply multiple criteria to filter the Sales column. In the Sales column, we want to find the values that are greater than or equal to $8000, and less than $20,000.

Steps:

  • First of all, to add a Filter icon to the headings, we will select the column headings by selecting cells B4:F4.
  • Then, go to the Data tab.
  • After that, from the Sort & Filter group >> select the Filter option.

As a result, you can see the Filter icon on the header of the dataset.

  • Therefore, we will click on the Filter icon of column F.

  • Furthermore, we will select Number Filters >> select Custom Filters.

At this point, a Custom Autofilter dialog box will appear.

  • Then, we will click on the downward arrow of the first box.
  • After that, we will select the option is greater than or equal to.

  • Furthermore, we will select $8000.

This will filter the values that are greater than or equal to $8000.

  • Furthermore, we will click on the downward arrow of the second box.
  • Afterward, we will select the option is less than.

  • Afterward, we will select $20,000.

This will filter the values that are less than $20,000.

  • Then, click OK.

As a result, you can see the filtered Sales column.

Hence, you can filter one single column based on multiple criteria.


Practice Section

You can download the above Excel file and practice the explained methods.


Conclusion

In this article, we describe 4 easy and effective methods to filter multiple columns simultaneously in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.


Further Readings

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo