How to filter using OR logic between columns [Advanced Filter]
Table of Contents
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
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
Excel categories
4 Responses to “How to filter using OR logic between columns [Advanced Filter]”
Leave a Reply
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.
The advanced filter works perfectly with the dates intervals >DD/MM/YY and DD/MM/YY; however when you record it under a MACRO it does not work !!!
Can someone help?
The advanced filter works perfectly with the dates intervals >DD/MM/YY and <DD/MM/YY; however when you record it under a MACRO it does not work !!!
Can someone help?
What did you record in your macro?
[…] was inspired by this approach Advanced custom date filter in Excel 2007 | Get Digital Help - Microsoft Excel resource only thinking that I could take my form data and make a hidden version of what has been done in […]