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

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

Back to top

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.

filter-a-date-range-in-excel-20071

How to create a criteria range

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

How to filter a list using a criteria range

  1. Press with left mouse button on "Data" in the ribbon
  2. Press with left mouse button on Advanced
    advanced-filter
  3. Select List range: A27:C64
    advanced-filter2
  4. Select the criteria range A16:C17
  5. Press with left mouse button on OK!

The new filtered list.

advanced-filter3

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)

Back to top