How to Filter 2 Columns in Excel – Using Advanced Filter Function

  • Home
  • / How to Filter 2 Columns in Excel – Using Advanced Filter Function

Worksheets with large datasets can put you in trouble when you need to find certain values. Finding a specific value from larger worksheets is like finding a needle in a haystack. For this, Excel let you get benefit from filters. With filters, you can simply narrow down the data from the worksheet to see the required information instantly.

Using filters is a great option when dealing with a lot of content. Filtering a column is not a difficult thing; however, filtering more than one column definitely is a tricky thing. Anyhow, every problem comes with many solutions, so don’t worry about this query, how to filter 2 columns in Excel. We have the most reliable hacks that let you filter 2 columns in Excel at the same time.

So, without further ado, let’s dive in:

How to Filter Multiple Columns at the Same Time in Excel

Below you can see a dataset that contains the ID Number, Sales Rep., Location, Product, and Sales columns. In our first approach we will use this dataset, so consider following it.

Method 1: Applying the Filter Option to Filter Multiple Columns Simultaneously in Excel

How to filter multiple columns simultaneously in Excel, as the heading of this method shows, it simply clarifies that we will use the filter option here. To Filter multiple columns simultaneously we can use the filter option, which is a truly effective tool. With this tool, you can easily arrange the data. Suppose that you need to filter column C where the names start from the letter A to column D where the location is the USA.

  1. First, you need to choose the header of the data table by selecting cells B4:F4 to apply the filter option.
  2. Now, open the Data tab and then choose the Filter option given under the Sort & Filter group.

  1. The dataset header has the Filter icon, which you need to click on it to filter column C.
  2. Now, we need to choose the Names that start with A, and then unmark the other names.
  3. Click OK.

Ultimately, the filtered table shows data for names starting with A.

  1. Moreover, you need to click on the Filter icon of column D.
  2. Now, you can mark the USA as the Location, and we unmark the other Locations, just the way we did with the Names columns.
  3. Click on the OK button.

In the end, you can see the data in terms of names starting with A that is present in the USA. That’s how we use the filter option and the data get changed accordingly.

That’s it!

This is how you can filter multiple columns in excel by following the above simple and easy method.

Price banner Earn and Excel

Method 2: Filter Multiple Columns Simultaneously with an Advanced Filter

Using the Advanced filter option, we will sort out the query on how to filter 2 columns in Excel. On the basis of criteria from multiple columns, you have to filter them.

Product = AAA-1 and Order>80, or Total Price > 10000 to get the required results:

Below are some easy-to-follow steps using the Advanced Filter option:

  1. At first, you have to make the filter criteria you need and here you can put the criteria in the range G1:I2.

Note: In case you need to make an AND criterion, you will have to put the criteria in the same row. On the other hand, if you need to make an OR criterion, enter the criteria on separate rows.

  1. Once the criteria are done, now click on the Data tab and choose the Advanced option.
  2. From the Advanced Filter dialog box, you need to do the following procedure:
  3. Choose Filter the list, in place option given under the Action section:
  4. Click the button so that you can select the cell collection that needed to be filtered from the List range box:
  5. Now click the button to choose the filter criteria that needed to be filtered on the basis of the Criteria range box:

  1. In the end, click OK and the multiple filtered columns will be in front of you.

Here are the desired results you want excel filter multiple columns. Keep trying these approaches!

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

With the OR function, you can filter more than one column at the same time in Excel. As you know, this tool gives a “logical option” and this is what you need to filter multiple columns. Suppose you have to filter column “E” by Book and column “F” where the value is greater than “15000.”

In the Criterion table, you can see the criteria.

  1. In the starting, you can add a column “Filter” to the dataset.

  1. In cell G5, now enter the following formula: =OR (E5=$C$21, F5>$C$22)
  2. Now, press ENTER and you will see the results displayed in cell G5.
  3. In the next step, using the Fill Handle tool you will drag the formula down.

Here you can now see the Filter column. From the Filter column, you can filter the TRUE as well. for this, you need to add a Filter icon to the headers. Below are the steps:

  1. By selecting cells B4:F4, you can choose the header of the data table.
  2. Now, open the Data tab and choose the Filter option from the Sort & Filter group.

Ultimately, you will see the Filter icon available on the header of the dataset.

  1. Now, click on the Filter icon of column G to filter the TRUE from column G.
  2. Here you need to mark TRUE and unmark FALSE.
  3. Click OK.

Eventually, your results will base on the criteria.

You need to remember one thing, if a single logical value matches the criteria, the OR function will reveal it.

Wrap Up

If somebody asks you, can you filter multiple columns in Excel, the answer will be definitely yes. You can filter two columns in Excel or even more than 2 columns at the same time without being in trouble.

So, this is how to filter 2 columns in Excel using multiple approaches. Now, you understand the notion behind filtering a column that’s why you need to practice all the above-mentioned procedures.

Price banner Earn and Excel

Write your comment Here