Bottom line: In this 3rd video of the series on Excel Filters you will learn how to filter multiple columns. I explain the rules Excel uses when multiple columns are filtered, and how to use OR logic.
Skill level: Beginner
Video #3: How to Filter Multiple Columns in Excel
In this third video I explain how to filter multiple columns in Excel. You will learn the rules and that Excel uses to filter using AND logic. I also explain how to filter columns with OR logic.
Double-click video to view full screen HD.
Download the file I used in the video to follow along. This file also contains instructions on each shortcut, and can be used as a guide to learn the shortcuts.
Excel's Filter Logic
In the video above I explain the rules that Excel uses when filters are applied to more than one column. Excel uses AND logic when we apply filters to multiple columns. This means that ALL of the filter criteria must be met across all filtered columns for the row to be displayed (visible).
I also explain a technique to filter with OR logic. This is a formula based approach that works well when we want to display rows where ANY of the filter criteria are met across multiple columns. As an alternate solution, we can also use the Advanced Filter tool to apply filters with OR logic. However, the Advanced Filter tool can be difficult to setup and train other users on. I prefer the formula based approach explained in the video if the filter criteria does not change often.
Filter Mate's Criteria Box
At the end of the video I share a tip on how to view the filter criteria for a column by hovering the mouse cursor over filter drop-down menu icon.
This is a quick way to see the filter criteria without opening the filter drop-down menu. However, it still means a lot of horizontal scrolling to find each column that has a filter applied to it.
The column list in the Filter Mate window contains a list of all the columns in the filtered range/table. This list also tells us if a filter is applied to each column. A “True” will be displayed in the Filtered column if the column has a filter applied.
We can click on this item in the list to see the filter criteria in the Filter Mate window.
We can also filter down the column list to only see the columns that have filters applied. This makes it fast and easy to see the filter criteria across multiple columns in one place.
Filter Mate & Filters 101 are Now Available!
I am excited to announce two new Excel Campus products!
If you have watched any of the three videos in this series then you have seen some small previews of The Filter Mate Add-in. Filter Mate is an Excel Add-in that makes it faster and easier to work with the filters in Excel.
Filter Mate is a navigation tool that helps us find columns that have filters applied, or columns that we want to apply filters to. It helps eliminate all that horizontal “ping-pong” scrolling that we spend way too much time doing.
I have also developed a new online course to help you get the most out of the filters features in Excel.
The course is called Filters 101. This course will help you learn the filtering techniques I have used throughout my career to prepare and analyze data.
Filters 101 is an online course, which means you can watch the training videos anytime you like from any device. I have developed this course using my simple step-by-step teaching method, which makes it easy to learn each technique and implement them in your own work.
Right now I am running a special promotion for the launch of The Filter Mate Add-in and The Filters 101 Course. You can save up to 40% on both of these great products when you buy them today.
Questions on Filtering Multiple Columns?
Please leave a comment below with any questions on filtering multiple columns. There are quite a few different techniques for doing this, and I'd love to hear your thoughts. Thank you! 🙂