How to write advanced filters or conditions in Excel

As we all know, data filtering is a common skill in EXCEL. It can be combined with date, text and numerical types and different filtering conditions to help us locate the data we want in one second. However, there is an advanced function for data filtering---Excel advanced filtering. Based on the original filtering, it can realize multi-condition filtering with one click, making it more convenient and efficient. Today I will introduce to you how to use advanced filtering or conditions in excel!

1. Open location:

Click "Advanced" in the "Sort and Filter" group under the "Data" tab. Open the Advanced Filtering window.

## 2. Method:

"Display filter results in the original area" and "Copy filter results to other locations"

Original Displaying filter results in an area means displaying filter results directly in the data source.

Copying the filter results to other locations means that they can be placed in other areas besides the data source, and you can choose it yourself.

List area, condition area and copy to:

The list area represents the data source, that is, the source area that needs to be filtered. You can select the range yourself, or you can select any cell in the data source range before clicking Advanced Filter, so that all data sources are selected in the list range by default.

The condition area represents the conditions we want to write here. Here’s the key point:

The filtering conditions are in an “and” relationship, that is, both conditions must be met at the same time, and the filtering conditions must be written in the same line.

The filtering conditions are in an "or" relationship, that is, if one of the two conditions must be met, the filtering conditions must be written in different lines.

Copy to means that when "Copy filter results to other locations" is selected, fill in the cell location copied to here.

The data source in the figure below is the daily sales records of all sales personnel in 2016 and 2017.

In advanced filtering: or

Filter conditions: Extract order areas starting with "华" or sales amount exceeding 2,000 The record is displayed in the original record.

Here are two filter conditions. The order area starts with "华" or the sales amount exceeds 2,000, which means or relationship. Then we should write the condition area like this:

Analysis:

Similarly, the first row corresponds to the column header of the filter column, which must be completely consistent with the fields of the data source.

Filtering conditions represent an “or” relationship, so they should be written in different rows and corresponding to column titles. When the numerical value is the filter condition, you can also directly use [>], [=], [In the advanced filtering tab, select "Show filter results in original area".

The results displayed in the original data area are as follows. Regions starting with the word "华" or orders with a sales amount greater than 2,000 are filtered out.

Recommended:

Excel Basic Tutorial

The above is the detailed content of How to write advanced filters or conditions in Excel. For more information, please follow other related articles on the PHP Chinese website!