How to filter using OR logic between columns [Advanced Filter]

Author: Oscar Cronquist Article last updated on March 31, 2022

How to filter using OR logic between columns
Advanced custom date filter

1. How to filter using OR logic between columns

The built-in filter feature in Excel is a powerful tool, however, it won't allow you to filter with OR logic between columns.

This is where the Advanced Filter comes into the picture. It lets you do that and I will show you how now.

Copy your table headers and paste them somewhere on your worksheet.

Type the criteria you want to use right below the new headers, each below the header you want to filter, see picture below.

Make sure the condition is the only one on each row.

Now it is time to start the Advanced Filter. Go to tab "Data" on the ribbon and press with left mouse button on "Advanced Filter" button.

The "Advanced Filter" settings window appears.

Press with mouse on "List range:" field and select cell range B6:D13 (your data you want to filter).

Press with mouse on "Criteria range:" field and select cell range B2:D4 (your criteria you want to use).

Press with left mouse button on OK button.

The blue rows to the left show you that you have applied a filter to your data.

To clear the filter simply press with left mouse button on the clear button on the ribbon tab "Data".

Get Excel *.xlsx file

How to filter with OR logic between columns.xlsx

2. Advanced custom date filter

Question: How do I filter the last xx years or xx months in Excel?

How do I exclude the current month when using the year to date filter in Excel?

Answer: Use advanced filter with criteria ranges. I have calculated the exact dates needed to create the criteria ranges.

I will go through the exact steps on how to accomplish the date filter.

The picture below shows the calculation of the dates, the criteria ranges and the list to filter.

How to create a criteria range

Copy the header of the list to a new location, see cell A16:C16 in the above picture.
Type the criteria, see A17:B17 in the above picture.
Formula in A17:
="<=2009-07-31" + ENTER
Formula in B17:
=">=2009-01-01" + ENTER

How to filter a list using a criteria range

Press with left mouse button on "Data" in the ribbon
Press with left mouse button on Advanced
Select List range: A27:C64
Select the criteria range A16:C17
Press with left mouse button on OK!

The new filtered list.

Repeat the above steps to filter the last xx years or xx months using the criteria ranges.

Get excel example file.
filter-a-list-of-dates.xlsx
(Excel 2007 Workbook *.xlsx)

Advanced filter excel category

5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

Advanced custom date filter
Question: How do I filter the last xx years or xx months in Excel? How do I exclude the current […]

Create a unique distinct list using Advanced Filter in a macro [VBA]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]

See all articles in 'advanced filter excel' category

More than 1300 Excel formulas

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form