OmniDocHow to set multiple conditions in Excel conditional format: AND and OR combination
Explain how to write formulas in Excel conditional formatting that satisfy multiple conditions at the same time (and) or only meet one of them (or), and how to manage the priority of multiple rules so that a table can display different colors according to multiple conditions.
Key points of this article
- The formula in the conditional format should return TRUE or FALSE, use AND to mean "satisfy both", and use OR to mean "satisfy one of them".
- The cell reference in the formula should be written as a mixed reference, such as $B2, where the column is fixed and the row changes with the filling, otherwise the format will be misaligned.
- Multiple rules can be set in the same area, with the rule above taking priority. Check "Stop if true" to avoid overwriting by subsequent rules.
- "Multiple results" such as segmentation by score and color separation by status are usually implemented using multiple rules with mutually exclusive conditions, rather than one rule doing everything.
- After setting, check the boundary values, blank cells and rule sequence to confirm that each cell displays only one expected format.
Many people will encounter a problem when doing conditional formatting in Excel: it is very simple to set only one condition, but once it is required to "mark green if you pass both Chinese and mathematics, mark red if you fail in any subject", they don't know how to write it. If a score list of 30 people is pieced together using several independent rules, the colors will often cover each other and some rows will not change.
This article is written for people who need to mark data according to multiple conditions, including teachers and workplace users who do performance statistics, inventory warnings, and project progress tracking. After reading this, you will know three things: how to combine multiple conditions into one rule using AND and OR, how to set multiple results for the same area, and how to adjust the priority of rules when color conflicts occur. The methods in this article apply to common versions of Excel.
How to set multiple conditions? Combine it with a formula or split it into multiple rules? One sentence conclusion
First use a formula to express "satisfies both" or "satisfies either", and then use multiple rules to distinguish different results; the two are often used together. The table below provides recommended practices based on your situation.
| your situation | Recommended practices | reason |
|---|---|---|
| Several conditions must be met at the same time before color marking | Use AND to wrap all conditions in the formula | It can be completed with just one rule, making it easy to check and modify. |
| If any of several conditions are met, the color will be marked. | Use OR to wrap all conditions in the formula | Only one requirement is required, the logic is intuitive |
| The same area should be displayed in different colors according to intervals | Split into multiple mutually exclusive rules, each corresponding to an interval | Each cell only falls in one interval, and the colors will not conflict. |
| The conditions are in order and subsequent rules should not be overridden. | Arrange the rules in order and check "Stop if true" | After the previous hit, subsequent rules will no longer be executed. |
| There are many conditions and they intersect with each other | First calculate the judgment result in the auxiliary column, and then set simple rules for the auxiliary column | Formulas are shorter and easier to locate when errors occur |
Before you start: Confirm these points first
1. Confirm whether the conditions are "and" or "or". In the Chinese description, "and" and "at the same time" correspond to AND, and "or" and "any one" correspond to OR. Before writing the formula, state the rules clearly in one sentence to avoid writing the logic backwards.
2. Confirm whether the conditions are mutually exclusive. If you want to do "90 or more is excellent, 60 to 89 is passing, and less than 60 is failing", the three conditions do not overlap with each other, and the order of the rules does not matter. If the ranges overlap, such as "greater than 80" and "greater than 90" are set at the same time, priority or stop conditions must be used to determine the result.
3. Confirm the citation method. The formula for conditional formatting is written relative to the first cell in the upper left corner of the selected range. If you want to make a unified judgment on the entire column or the entire row, you should write it in the form $B2: add $ before the column number to fix the column, and do not add $ before the row number to change with the filling.
4. Confirm the processing of blank cells. Blank cells are often treated as 0 in formulas for comparison. For example, $B2<60 will also return TRUE for blank cells. If the data may be missing, you need to add judgment to the formula, such as AND($B2<>"",$B2<60).
5. Back up the original table or operate on the copy. Conditional formatting will change the display, but not the value. However, when adjusting the order in a multi-rule and multi-region table, making a backup copy can save you the trouble of going back.
Steps to set "AND" and "OR" conditions using formulas in Excel
Let's take a score table as an example: Column A is name, column B is Chinese, column C is mathematics, and the data starts from row 2. The goal is: the entire row with a score greater than or equal to 90 in both Chinese and Mathematics is marked green.
- Select the area where you want to apply formatting. For example, select A2 to C31. The upper left corner of the selection is A2, from which the formula will be written.
- Open the New Rule dialog box. Find the conditional formatting command in the top ribbon of Excel, expand it, select New Rule, and then select the rule type that formats by formula.
- Enter the AND formula. Enter =AND($B2>=90,$C2>=90) in the formula box. Cells with a formula result of TRUE will have the format applied, cells with a formula result of FALSE will not.
- Set format. Click the Format button in the rules window, select green in the fill color, and save the rule after confirmation.
- If you need to mark "Failed in any subject" in red, use OR instead. Create a new rule again, select A2 to C31 for the area, enter =OR($B2<60,$C2<60), and select red for the format.
- Check whether the reference is correct. Check whether the formula is based on row 2 in the conditional formatting management interface. If an absolute reference like =AND($B$2>=90,...) is displayed, it should be changed to $B2, otherwise all lines will be judged according to line 2.
- Adjust the order of rules. In "Manage Rules", the rules above have higher priority. If a row satisfies two rules at the same time, and both fill colors are set, only the color with the highest priority will be displayed.
- Check "Stop if true" if necessary. When the rules need to be judged in order, check this option in the previous rule, and the cells that satisfy it will no longer be affected by subsequent rules.
# check item by item after completion
After the rules are set, it is recommended to check them item by item according to the table below, especially in the areas where boundary values and multiple rules are superimposed.
| Check item | What do you think? | What if there's a problem? |
|---|---|---|
| Is the result of the formula correct? | Select a few lines to calculate by hand, such as lines 2 and 31, and check whether the colors are the same. | Check whether AND or OR is written upside down and whether parentheses and commas are paired. |
| Is the boundary value correct? | Look for numbers such as 90 and 60, which are exactly equal to the threshold, and see if they are classified as expected. | Confirm whether the formula uses & gt;= or & gt;, has different results on boundary values. |
| Whether the reference drifts | Drag the fill down to see if the format changes with the line | Keep the $before the column number fixed and the line number relative. |
| Blank or outlier | Deliberately leave a blank space or enter text to see if it has been miscolored. | Add a non-empty judgment to the formula, such as $B2roomlttransliteration; |
| Whether the colors conflict or not | Look for lines that meet both rules to see if they show a high priority color. | Adjust the upper and lower order in the management rules, or check "stop if true" |
| Scope of application of rules | Open the management rules to see if the "apply to" area of each rule covers all the data | Modify the area applied to avoid overwriting only part of the row |
# Core comparison
There are roughly three ways to combine multiple conditions, and their applicable scenarios are different.
| Mode | Writing example | Suitable for what? | Pay attention |
|---|---|---|---|
| AND combination | =AND($B2>=90,$C2>=90) | Multiple conditions must be established. | The more conditions, the longer the formula. Pay attention to parentheses. |
| OR combination | =OR($B2<60,$C2<60) | Any condition is marked immediately | It is easy to mismark the situation that should be satisfied at the same time. |
| Multiple mutually exclusive rules | The three rules correspond to excellence, pass and failure respectively. | The same column is divided into multiple results according to intervals. | The intervals should be connected from beginning to end to avoid overlap or omission. |
Nesting AND and OR together is also a common practice, for example, = AND ($B2>=60,OR($ C2GTX 90), which indicates a pass in Chinese and at least one excellent subject in maths or English. The number of nesting layers should not be too much, and when there are more than two layers, it is recommended to use auxiliary columns to calculate step by step.
# which method is suitable for you?
- you only need to mark the lines that meet all the criteria : use an AND rule to check the reference and use it directly. - need to be divided into several classes according to scores or values : use multiple mutual exclusion rules, each corresponding to only one interval, without having to rely on priority. - complex conditions and need to be modified repeatedly : first calculate the text results such as "meet the standard" or "early warning" with IF, AND and OR in the blank column, and then set simple text rules for this column, which makes the logic more transparent. - the form should be handed over to others for maintenance *: write a description next to the rule, or add a note to the table header, indicating the conditions represented by each color, so as to avoid misunderstanding when others modify it.
# frequently asked questions (FAQ)
1. Can functions other than AND and OR be used in conditional format formulas?
Sure. As long as the formula finally returns TRUE or FALSE, it can be used as a condition. For example, ISBLANK, COUNTIF, and MOD can all be used in conditional formatting formulas, but be careful whether they are based on the correct cells.
2. Why is the rule set and some cells do not change color?
There are three common reasons: the formula reference is miswritten, resulting in judging that the benchmark row is incorrect; the selection does not cover these cells; or the same property (such as fill color) is set first by higher priority rules. Check the formula and application scope of the management rules one by one to locate.
3. Can you display multiple formats in the same cell?
The same cell can satisfy multiple rules at the same time, but each attribute (such as font color, fill color) can only show which one has a higher priority. Different properties such as bold font and border can take effect at the same time.
4. Can conditional formats reference data from other worksheets?
In newer versions of Excel, conditional formatting formulas can refer to cells of other worksheets, but the writing and compatibility vary from version to version. If you encounter problems, it is safer to put the data you need in the secondary column of the current worksheet before referencing it.
# Summary
The core of multiple conditions is to use AND to express simultaneous satisfaction and OR to express one of them, to express multiple results with multiple mutually exclusive rules, and then to deal with conflicts with priority and stop conditions. After setting, spot-check the boundary values and blank cells to basically ensure that the results displayed in the table are consistent with expectations.
