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 from multiple columns simultaneously in your worksheet. Today we will discuss 4 easy ways of filtering multiple columns.

How to Filter Multiple Columns Simultaneously in Excel: 4 Methods

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.

1. Applying the 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 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.

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.

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.

Read More: How to Filter Multiple Columns Independently in Excel

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.

Steps:

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

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.

Read More: How to Search Multiple Items in Excel Filter

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.

Steps:

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

• After that, we type the following formula in cell G5.

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

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.

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.

Read More: How to Filter Data in Excel Using Formula

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.

Steps:

• First of all, we will type the following formula in cell B24.

• After that, press ENTER.

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

Read More: How to Filter Column Based on Another Column in Excel

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.

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.

Practice Section

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

Download Practice Workbook

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

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.

<< Go Back to Data | Filter in Excel | Learn Excel