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. We’ll filter the results by multiple columns.

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

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.

• Click on the Filter icon of column C.

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

• Click on the Filter icon of column D.

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

• 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

We want to filter the names that start with A with the location USA. You can see these criteria in the Criteria box.

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

• Here are the results.

Read More: How to Search Multiple Items in Excel Filter

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

We need to filter column E by Book and column F by values greater than 15,000. You can see the criteria in the Criteria table.

Steps:

• Add a column G named Filter to the dataset.

• Enter the following formula in cell G5.

• Press Enter.
• Drag down the formula with the Fill Handle tool.

• Select the header of the data table, i.e. cells B4:F4.
• Go to the Data tab.
• From the Sort & Filter group, select the Filter option.

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

• Mark TRUE and unmark FALSE.
• Click OK.

• Here are the results.

Read More: How to Filter Data in Excel Using Formula

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

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.

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

How to Apply Multiple Filters in One Column in Excel

We want to find the Sales values that are greater than or equal to $8,000 and lower than $20,000.

Steps:

• Select the column headings B4:F4.
• Go to the Data tab.
• From the Sort & Filter group, select the Filter option.

• Click on the Filter icon of column F.

• Select Number Filters then choose Custom Filters.

• A Custom Autofilter dialog box will appear.
• Click on the drop-down of the first box.
• Select the option is greater than or equal to.

• Select $8,000.

• Click on the drop-down arrow of the second box.
• Select the option is less than.

• Select $20,000.

• Click OK.

• Here’s 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.

Practice Section

We have included a practice dataset you can use to test the methods.

Download the Practice Workbook

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