OmniDocHow to do advanced filtering in Excel: Conditional regional setting steps
It explains how to write the condition area of Excel advanced filtering, how to distinguish the AND-OR relationship between the same row and different rows, how to execute and check the results, and is suitable for people who need to filter by combining multiple conditions.
Key points of this article
- The header of the condition area must be exactly the same as the field name of the original data. Try to copy the original header directly instead of typing by hand.
- Multiple conditions on the same line are in an "and" relationship, and conditions in different rows are in an "or" relationship. Writing the correct position is more critical than adjusting the conditions.
- When you want to judge the interval in the same column, write two headers with the same name in the condition area, and set the ">=" and "<=" conditions respectively.
- Saving numbers as text will result in empty filter results. Please confirm the format of numbers and dates before executing.
- When you need to keep the original table, output the filter results to other locations and the original data will not be hidden.
Many people have used the automatic filtering of tables to select a value in a certain column, but when encountering combined conditions such as "Chinese is no less than 80 points and math is no less than 70 points", or "the class is in the first class or the total score is in the top ten", the buttons are not enough. How to do advanced filtering in tables? The core is to build a separate "condition area" next to the data, write the filtering rules into a small table, and then let Excel read the data according to this rule.
This article only talks about advanced filtering and does not expand ordinary automatic filtering. Suitable readers are those who want to filter by a combination of multiple conditions, or who need to take out the results individually, such as selecting students who meet the requirements from a score sheet with hundreds of rows, or finding records that meet one of the two conditions from a customer list. After reading, you can design your own conditional areas, distinguish "and" and "or", perform filters, and check whether the results are correct.
How to do advanced filtering in the table? One sentence conclusion
Conclusion in one sentence: Copy the header in the blank space next to the data area, write the conditions below the header, the same line means "and", different lines means "or", then open the advanced filtering and specify the list area and condition area respectively.
| your situation | Recommended practices | reason |
|---|---|---|
| Filter only one value or simple range by one column | Just use normal automatic filtering | Faster operation, no need to create additional conditional areas |
| Multiple conditions must be met simultaneously | Conditions are written on the same line | Excel determines "and" relationship by row |
| Just meet any conditions | Conditional points are written on different lines | There is an "or" relationship between different rows |
| The same column needs to be judged as an interval, such as 60 to 80 minutes | Write two headers with the same name in the condition area, set ">=60" and "<=80" respectively. | Two conditions for the same column in the same row require two table headers |
| It is necessary to keep the original table and only show the results | Export filter results to other locations | The original data is not hidden and the results are stored separately. |
Before you start: Confirm these points first
- Whether the original data is a standardized data table. The first line should be the field name, without any empty rows, empty columns or merged cells. Reason: Excel will break at blank rows when recognizing the data range, resulting in missing rows.
- Numbers and dates are not real numbers and dates. "85" in text form will be treated as text, and the condition ">=80" cannot match it. You can select the cell and take a look. The content aligned to the left is usually text.
- The condition area should be separated from the data area. Place the condition area in the blank space above or on the right side of the data table, leaving an empty row or column between it and the data. Reason: When the two are closely aligned, Excel may mistake the conditional row for data.
- Field names must match word for word. Includes spaces, full-width and half-width characters. Reason: The header of the condition area does not match the original table, Excel will consider the condition invalid, and the result may be empty.
- Whether you want to keep the original table. If you want to keep it, copy a worksheet in advance, or output the results to other locations during execution. Reason: In-place filtering will hide rows that do not meet the criteria and need to be cleared to restore.
Steps to set conditional areas in tables and perform advanced filtering
- Copy the header. Select the header row of the original data and copy it to the blank area as the first row of the condition area. In this way, the field names are completely consistent with the original table.
- Write the conditions in the same line below the header. For example, write ">=80" under "Chinese" and ">=70" under "Mathematics". Put both on the same line, indicating that both conditions must be met at the same time.
- Use different lines to express "or". When any set of conditions needs to be met, write the other set of conditions on the next line. Do not leave a blank line between the two lines.
- Use wildcards and comparison characters. The text can use asterisks to represent any number of characters, and question marks to represent a single character; numbers can use comparison characters such as ">", "<", >=", "<=", etc., such as ">=80".
- Confirm list area. Click any cell in the original data to open the advanced filtering function and check whether the entire table has been automatically selected in the list area. If the range is incorrect, manually select the correct data area.
- Specify the condition area. In the dialog box, select the condition area just written, including the table header and all condition rows.
- Select the destination of the results. The filter results can be displayed directly in the original area; if you want to keep the original table, output the results to another location instead and specify the starting cell of the results.
- Execute and view the results. Click OK. If there are duplicate records in the results, you can check "Select non-duplicate records" before executing.
Check each item after completion
After the screening results come out, do not use them directly. Check them according to the table below first.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Number of result rows | Compared with the number of qualifying rows manually counted, it should be roughly the same. | If the result is empty or significantly underwhelmed, check whether the table header matches and whether the numbers are text |
| Moreover, it is related to or others | Randomly check a few lines to confirm that all peer conditions are met, and if no other row meets one group, just suffice | Move the condition to the correct row or column and rerun |
| Boundary values | Check records that exactly match the threshold, such as whether 80 points are included | The test uses ">=" or ">", but the results are different |
| wildcard | If an asterisk or question mark is used in the condition, check if the result matches unexpected content | Confirm whether the data itself contains these symbols, and if necessary, use more precise conditions |
| Original data | Confirm that the original table has not been mistakenly deleted or altered | In-place screening can restore the erase function; If output to other locations, the original table remains unaffected |
❓ Frequently Asked Questions (FAQ)
1. What should I do if the advanced filter results are empty?
First, check whether the headers of the condition area are exactly the same as the original table, including the spaces. Then check whether the numbers being compared are in text format. Finally, confirm that the conditions are not written in lines that shouldn't be written.
2. Where should the condition area be placed?
Place it in a blank area above or to the right of the data table, leaving at least one blank row or column between it and the data. Do not overwrite the original data, and do not let the two areas be pressed tightly together.
3. Can formulas be used as filtering criteria?
Yes, you can. Leave the header of the condition area blank, or write a name different from the original table, and below it write the formula that returns TRUE or FALSE. When referencing data in the formula, the first record in the data area is used as the reference.
4. How to cancel advanced filtering and restore all data?
If filtering in place, use the clear function in the table to restore all rows. If the result is output elsewhere, simply delete that result area; the original table will not be affected.
📝 Summary
The key to advanced filtering in tables lies in the condition area: the header matches the original table, the same row represents and, and different rows represent or. After writing the conditions step by step and performing the filter, then using the checklist to check the results, you can reliably obtain the data you want.
