How to Filter Multiple Columns in Excel?

In Microsoft Excel, when we apply a filter to a specific column it only filters all the columns based on that specific column.

But we often came across situations where we want to apply filters on multiple columns instead of a single column.

In this tutorial, I will guide you through all the methods using which you can easily apply filters to multiple columns in Excel.

Method 1: Filter Multiple Columns Using the Filter Option

Excel has in-built filter options that allow you to filter based on one or more than one columns in one go.

In this method, you have to select the column heading and then apply the filter from the option in the ribbon. Let’s see it in practice

For demonstration purposes, I will use a sample dataset of sales done by various representatives in different regions as shown below.

Dataset

In the above dataset, I want to filter this dataset to only show data where the region is ‘Central’ and the item is ‘Binder’. This means that I need to filter this data based on these two columns

Below are the steps to filter based on multiple columns in Excel:

  1. Select the column heading in the dataset
Select the column headers
  1. Click on the ‘Data’ tab in the ribbon
Click the data tab
  1. In Sort & Filter section, click on the ‘Filter’ option
Click the Filter icon
  1. This will apply a Filter to all the column headers in the dataset
Filter applied to all columns
  1. Now click on the dropdown icon in the Region column
click on the drop down icon
  1. From the option that shows up check the ‘Central’ checkbox and uncheck the others. This will filter the data and only show records for Central region
check the central option and uncheck all the rest
  1. Similarly, now click on the drop-down icon in the ‘Item’ column.
Click the filter icon
  1. From the option that shows up check the Binder checkbox and uncheck others
Check the binder option

This will filter all the data based on the Central Region and Binder Item as shown below

Data filtered based on multiple columns

In this example, I showed you how you can filter the data based on the region and item.

Similarly, you can apply filters to other columns as well.

In case you want to clear all the filters and get the original data back, you can go to the Data tab and click on the Filter icon.

Pro tip: You can use the keyboard shortcut Control + Shift + L to apply or remove filters in a dataset. To use this, select any cell in the dataset, then hold the Control and the Shift key and then press the L key

Also read: VBA Macro Codes to Filter Data In Excel

Method 2: Filter Multiple Columns Using the Advance Filtering Option

Advanced filtering is an awesome Excel feature to filter a range of data based on multiple columns.

For advanced filtering, first, we have to define the criteria range (based on which we want to filter the data) somewhere in the worksheet.

Microsoft Excel then uses that criteria range to filter data. Let’s see it in practice.

For demonstration purposes, I will use a sample data set of sales done by various representatives in different regions as shown below.

Dataset

Now I am going to show you how you can apply advanced filtering to the above data set. 

  1. Let’s say we want to filter the data based on the ‘Region’ and ‘Item’ columns in the above data set. So first, I defined the filter criteria where I specified Region as Central and Item as Binder as shown below.
Specify the filter criteria
  1. Click on the Data tab in the ribbon
Click the data tab
  1. In Sort & Filter section click on the option Advanced as shown.
Click on Advanced
  1. A dialog box gets open where you have to select the List range and Criteria range as shown below.
Advance filter dialog box
  1. Click inside the List range input box.
Click inside the List range
  1. Select the range of data including the column heading that you want to filter. To select a range, select a cell and then drag over the other cells with the left mouse button pressed.
Select the dataset
  1. Now, click on the Criteria range input box
click on the Criteria range input box
  1. Select the Criteria range. To select a range, select a cell and then drag over the other cells with the left mouse button pressed.
Select the Criteria range

It will filter all the data based on Region (Central) and Item (Binder). The result is shown in the screenshot.

Dataset filtered on multiple columns

Similarly, you can apply a filter to any other column as well. 

Note: In this example, I filtered the original dataset, but you also have the option to get the filtered data in a different location in the worksheet. This is useful when you want to filter and extract data to a different location and don’t want to alter the original dataset. To use this, select the Copy to Another location option in the ‘Advance Filter’ dialog box

Method 3: VBA Script to Filter Multiple Columns Using Autofilter Function

If you are looking for a VBA to filter multiple columns then this section would be helpful to you.

In this section, I will use the VBA script along with the AutoFilter function to filter multiple columns. Now let’s do it.

For demonstration purposes, I will use a sample dataset of sales done by various representatives in different regions as shown below.

Dataset

In the above data set let’s say we want to filter the data based on Region as Central and Item as Binder.

To do so open the VBA editor and write the following script (or copy and use the VBA code below). If you don’t know how to open the VBA editor, instructions are provided at the end of this section.

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub Filter_Multiple_Column()

StartRange = "A1"
RegionColumn = 2
ItemColumn = 3
RegionCriteria = "Central"
ItemCriteria = "Binder"

Range(StartRange).AutoFilter Field:=RegionColumn, Criteria1:=RegionCriteria
Range(StartRange).AutoFilter Field:=ItemColumn, Criteria1:=ItemCriteria

End Sub

You’ll need to customize the above VBA code depending on your own data.

Here is the explanation of the above VBA code to filter based on multiple columns in Excel.

StartRange: It is a user-defined variable that represents the start of the dataset that you want to filter. In our case, the sample dataset starts from “A1” which is why I specify its value as equal to “A1”. You can also specify the complete range of datasets here.

RegionColumn: This is a user-defined variable that specifies the column number of the Region, which is 2 in our case. 

ItemColumn:  This is a user-defined variable that specifies the column number of the Item, which is 3 in our case. 

RegionCriteria: It specifies the Region criteria based on which we want to filter our data, which is Central in our case.

ItemCriteria: It specifies the Item criteria based on which we want to filter our data, which is Binder in our case.

AutoFilter: It is a built-in Excel function that filters the data based on predefined criteria.

Now run the script by using the Run button or F5 from the keyboard and it will filter all the data based on the Central Region and Binder Item as shown below

Data filtered

Instruction to Run a VBA Script

  1. Open the VBA editor by pressing Alt + F11 from the keyboard in the case of Windows and Opt + F11 if you are using Mac.
Open the VB Editor
  1. To insert a new Module click on the Insert tab.
Click on Insert
  1. Now click on Module
Click on Module
  1. This will insert a new Module where you can write your VBA script.
  2. Copy and Paste the above VBA code into the module code window
  3. To run the code, place the cursor anywhere in the code and then press the F5 key (or click on the green play button in the toolbar)

In this tutorial, I showed you how you can filter multiple columns in Excel using different techniques.

The easiest way would be to use the in-built Excel filter and then filter multiple columns one by one.

You can also use the Advance Filter or the VBA method if you need to often filter based on the same criteria.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment