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

When you Filter a column, the other columns are filtered based on that filtered column so filtering multiple columns simultaneously in Excel can be a little tricky. In the following dataset you can see the ID Number, Sales Rep., Location, Product, and Sales columns.

Method 1 – Using the Filter Option to Filter Multiple Columns Simultaneously in Excel

In this example we will filter columns C and D to find the names that start with the letter A and whose location is USA.

Steps:

• Select the header of the data table by selecting cells B4:F4 to apply the filter option.
• Go to the Data tab.
• From the Sort & Filter group >> select the Filter option.

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

• To filter column C, we will click on the Filter icon of column C.

• Select the Names that start with A and unmark the other names.
• Click OK.

You can see that the data table has been filtered and it is showing data for names that start with A.

• Click on the Filter icon of column D.

• Mark the USA as the Location and unmark the other locations.
• Click OK.

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

Read More: How to Filter Multiple Columns Independently in Excel

Method 2 – Using the Advanced Filter Feature to Filter Multiple Columns Simultaneously in Excel

Here we want to filter the names that start with A with the location 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:

• Go to the Data tab and from the Sort & Filter group, select Advanced Filter.

• In the Advanced Filter dialog box, select cells B4:F18 as List Range. Select cells B22:F23 as Criteria range.
• Select Copy to another location and select cell B26 in the Copy to box and click OK.

You can see the Filtered Columns data table is now showing the data of names that start with A and that are in the USA.

Read More: How to Search Multiple Items in Excel Filter

Method 3 – Using OR Logic to Filter Multiple Columns Simultaneously in Excel

Suppose we need to filter column “E” by Book and column “F” by values greater than “15000”. You can see the criteria in the Criteria table.

Steps:

• Add a column named “Filter” to the dataset.

• Enter the following formula in cell G5.

• Press ENTER.

You can see the result in cell G5.

• Drag down the formula with the Fill Handle tool.

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

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

• Select the header of the data table by selecting cells B4:F4.
• Go to the Data tab.
• From the Sort & Filter group >> select the Filter option.

Below you can see the Filter icon on the header of the dataset.

• Click on the Filter icon of column G to filter column TRUE from column G.

• Mark TRUE and unmark FALSE.
• Click OK.

Here you can see the result based on the criteria.

REMEMBER: If any of the logical values match the criteria, the OR function will show that. Because the other logical value was matched with the criteria Pen, Pencil, and Smartwatch are listed as well.

Read More: How to Filter Data in Excel Using Formula

Method 4 – Using the FILTER Function to Filter Multiple Columns Simultaneously in Excel

Here we will filter the dataset based on the location USA as shown in the Criteria table.

Steps:

• Enter the following formula in cell B24.

• Press ENTER.

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

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

Steps:

• To add a Filter icon to the headings, select the column headings by selecting cells B4:F4.
• Go to the Data tab.
• From the Sort & Filter group >> select the Filter option.

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

• Click on the Filter icon of column F.

• Select Number Filters >> select Custom Filters.

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

• Click on the downward arrow of the first box.
• Select the option is greater than or equal to.

• Select $8000.

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

• Click on the downward arrow of the second box.
• Select the option is less than.

• Select $20,000.

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

• Click OK.

You can see the filtered Sales column.

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.

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