How to Filter Multiple Columns Simultaneously in Excel (3 Ways)

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 can be a little tricky. There are certain easy ways of filtering data of multiple columns simultaneously in your worksheet. Today we will discuss three easy ways of filtering multiple columns.

Download Practice Sheet

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

Three Easy Ways to Filter Multiple Columns Simultaneously

Let’s say, we are given this set of data like the one in the picture below. Now we are going to filter these columns simultaneously.

Filter Multiple Columns Simultaneously

In this article, we will discuss three ways of solving this problem. They are,

1. Using Filter Option on Multiple Columns

2. Using Advanced Filter Option on Multiple Columns

3. Using “OR” Logic

You can use any of the three ways to filter your data. 

1. Using “Filter” Option on Multiple Columns

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 “D” where their names start from letter “A” respectively to the column “E” where the location is “USA”.  Now, we will do it step by step. 

Step 1 :

Select the header of your data set to apply the filter option.

Filter Multiple Columns Simultaneously

Step 2 :

Apply the “Filter” option.

Filter Multiple Columns Simultaneously

Step 3: 

Filter the column ”D”  by the names from the letter “A” and click ok.

Step 4:     

Now, Filter the column ”E”  by the location “USA” and click ok.

Result: 

Now you have your filtered data according to name and location

2. Using “Advanced Filter” Option on Multiple Columns

Advanced filter tool is an amazing tool to filter multiple columns at the same time. Now we will filter the data by the “Advanced Filter” tool using the same condition. The data set is the same too.

Step 1:

First, copy the header to another location in your worksheet and write down the criteria in the columns.

Step 2: 

Go to “Data” and choose “Advanced Filter”.

Step 3: 

After opening the advanced filter tool select “Copy to another location” if you want to get your filtered data on another location and click “List Range”.

Step 4 : 

Now select the “List range” and click on this

Filter multiple columns advanced filter

Step 5 : 

Select “Criteria Range” and click

Step 6:

Again select “Copy to” and click to select where you want to copy your data and click . Then choose unique values only if you don’t want duplicate values.

Finally, 

We have our filtered data. 

3. Using “OR” Logic

You can filter multiple columns simultaneously using the function “OR”. This function will provide you “logical option” and based on that you can do your job.

Filter multiple columns or logic

We will use the same datasheet. Suppose we need to filter column “F” by Book and column “G” where the value is greater than “15000”.Now we will solve this following these steps. 

Step 1 : 

Add another column and you can name it “Filter”. Now input the criteria in another location. Please Follow the picture .

Step 2 : 

Input your logical information in the “OR function” Remember to Block your column cell references by Pressing “F4”

Step 3:

Now we got our result but we need to filter out the “False Value”.

Step 4 : 

Use “Filter” and click on “True”.

Finally, 

Our result is here. But we need to remember that if any of the logical values match with the criteria, the “OR” function will show that. That’s why we get “Smartwatch” instead of “Book” because the other logical value was matched with the 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.

Conclusion

So we learned how to filter multiple columns simultaneously using three different ways. Hope this article is useful to you. Do you have any questions? Feel free to ask us.


Further Readings

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo