OmniDocHow to set different colors for different conditions in Excel conditional formatting
Use multiple conditional formatting rules to set different fill colors for different values or text ranges. Explain clearly how to write formulas, the order of rules and overlapping processing, and provide an item-by-item verification method, which is suitable for tables that are graded and color-coded according to grades, progress, amounts, etc.
Key points of this article
- Each color corresponds to an independent rule, and the conditions between the rules should not overlap each other to avoid multiple rules competing for the color of the same cell.
- Use absolute references (such as $C2) for formula reference columns and relative references for rows, so that each row can be judged by its own value.
- When the same cell satisfies multiple rules and all have fill colors set, the color of the rule ranked first in the rule manager will take effect.
- Boundary values (such as 60 points, 90 points) should be divided into and only one interval. When writing formulas, pay attention to the signs of greater than, equal to, and less than.
When many people color tables in Excel, they first think of "setting a color for this column." However, in actual work, common requirements are: grades above 90 are marked green, scores between 60 and 89 are marked yellow, and scores below 60 are marked red, or "completed", "in progress" and "overdue" in the progress table are each in one color. This type of requirement requires Excel's conditional formatting and multiple rules to set colors separately.
This article is written for people who need to classify grades according to conditions, such as teachers counting scores, project leaders tracking progress, and financial verification amounts. After reading, you will know how to use formulas to write multiple rules that do not conflict with each other, how to check whether the colors are misaligned, and how to keep the colors correct after the data is added.
If the first question you have is "why a single rule doesn't work", it's usually a formula reference problem, which will be covered in Parts 2 and 3 of this article.
How to use formulas to set different colors for different conditions? One sentence conclusion
Use formulas to set different colors for different conditions. A safer approach is to create a separate rule for each color. The formula of each rule only corresponds to one numerical interval. There is no overlap between the intervals, and then use absolute references to lock the judgment columns. The table below can help you choose the method according to the situation.
| your situation | Recommended practices | reason |
|---|---|---|
| Values are divided into three or four fixed ranges, such as grades and amount levels. | Create a formula rule for each interval, and the intervals are mutually exclusive. | The judgment conditions for each color are clear, and changing one interval will not affect other colors. |
| Classify by text content, such as "Completed" and "In Progress" | The formula uses the equal sign to judge text, one rule for each state | The text is relatively simple, just judge by the equal sign, no need to write complicated formulas |
| It is necessary to judge two columns at the same time, such as "the status is in progress and has expired" | Use AND to combine the two column conditions in the formula and place them at the front | Combination conditions can be accurately positioned, and pre-rules can avoid being overwritten by subsequent colors. |
| I only want to see the gradient of numerical value, no binning required. | Use the built-in rules of the color scale class | The color scale changes color continuously according to the numerical value. There is no need to write a formula, but it is impossible to make clear several levels of colors. |
| The data source is a PDF table | First export the table to Excel, then set conditional formatting | Conditional formatting can only be applied to Excel cells. Tables in PDF need to be converted into tabular data first. |
Before you start: Confirm these points first
First determine the binning standards and boundaries. Before writing the formula, first write the interval corresponding to each color on the paper, such as "≥90 green, 60 to 89 yellow, <60 red". Which bin the boundary value should be classified into must be determined in advance, otherwise a number such as 60 or 90 may be hit by two rules at the same time, resulting in color conflict.
Confirm the judgment column and data range. The formula of conditional formatting should be understood based on a "starting cell". Assume that the score is in column C and starts from row 2, then the selected area should be in the range of C2:C31, and the formula is written with C2 as the basis. If the area you select is inconsistent with the starting point referenced by the formula, the overall color will be misaligned.
Check if the data is pure numbers and pure text. Content that looks like numbers may be in text format, and errors will occur during comparison. Select several cells and look at their format and alignment. Numbers in text format are often left-aligned. If this is the case, you need to convert it to a numeric value first, and then set the conditional format.
Pay attention to the reference symbols in the formula. Columns are locked with $, rows are not locked, for example $C2. In this way, when the rules are filled down, each row will judge the value of its own row. If it is written as C$2 or $C$2, the color will be stuck in a certain line and the result will be wrong.
If the data comes from PDF, convert it to a table first. If the grades or amounts come from a PDF file, you can first export it to Excel using the PDF editor of the OmniDoc desktop client, and then set conditional formatting in the table. It is not possible to apply these rules directly in the PDF.
Steps to set three colors using formulas
Take a grade list of 30 students as an example. The grades range from C2 to C31. A score above 90 is required to be marked green, a score from 60 to 89 is marked yellow, and a score below 60 is marked red.
- Select the grade area C2:C31, which is the scope of subsequent formulas.
- Open conditional formatting, select New Rule, and select Use formulas in the rule type to determine the cells to be formatted.
- Enter the formula =$C2>=90, click Format, set the fill color to green, and confirm. This rule only takes care of cells above 90 points.4. Create a second rule, enter =AND($C2>=60,$C2<90), and set the fill color to yellow. The AND function allows only values between 60 and 89 to hit.5. Enter =$C2<60 in the third rule and set the fill color to red. The intervals of the three rules exactly cover all scores without overlap.
- Open the Rule Manager and check whether all three rules apply to C2:C31. Verify that the formulas and colors for all three rules match steps 3 through 5.
- Change a few scores at will, such as 60 and 90, and see what colors they turn into respectively. 60 should be yellow and 90 should be green, indicating that the boundaries are correctly drawn.
If your requirement is text classification, such as recording status in column D, the formula can be written as =$D2="Completed", and so on for other statuses. The text must be exactly the same as the content in the table. If there is an extra space, it will not hit the target.
Check each item after completion
After the color setting is completed, don't just look at one or two lines. It is recommended to check it through the list below.
| Inspection items | How to see it | What should you do if there's a problem? |
|---|---|---|
| Check if the color matches the value | Randomly select 5 cells and compare their values and colors | Check the symbols and numbers in the formula, especially the difference between >= and > |
| Is the boundary value only one level? | Find cells equal to 60 or 90 and see if there is only one color | Adjust the symbols in AND to ensure the two intervals connect at the end without overlapping |
| Whether the rules all apply to the correct areas | View the "Applied to" scope of each rule in the Rule Manager | Remove redundant rules and change the scope to match the data |
| Check if the formula citation is correct | Check whether the columns in the formula use $ and whether the rows are not locked | Correct it to $C 2, then reapply it to the whole column |
| Whether the order of rules is reasonable | View the arrangement of rules in the manager | When two rules may hit simultaneously, place the rule that needs to be shown first and check if it is true to stop |
| Whether the new data is colored | Add a row of test values at the end of the data | Extend the rule to a sufficient number of rows, or use a whole column reference |
📊 Core Comparison
There are mainly three approaches to setting colors for different conditions, each suitable for different scenarios.
| Method | Suitable for the scene | Points to note |
|---|---|---|
| Multiple formula rules | Clear segmentation and complex conditions, such as crossing two columns to judge | You need to ensure intervals are mutually exclusive, and write many formulas |
| Built-in cell value rules | Only determine that the value is greater than, less than, between, or equal to a certain number | Operation is faster when conditions are simple, but less flexible when multiple conditions are met simultaneously |
| Levels or data bars | I just want to see the exact numbers directly, without needing clear tiers | The colors are continuous gradients, making it difficult to distinguish discrete categories like "qualified" and "unqualified." |
If you need "several clear levels," prioritize formula rules. If you just want to see the size distribution, the color gradation is much easier.
❓ Frequently Asked Questions (FAQ)
1. Why are there two colors set and only one is displayed in the table?
A common reason is that two rule conditions hit the same cell simultaneously, and both have a fill color set. At this point, the rule color listed at the front in the rule manager takes effect. The solution is to change the conditions to mutually exclusive or adjust the order.
2. The formula is clearly correct, but the color doesn't appear. What should I do?
First, check whether the application scope of the rule includes the cell, then check if the data is a text number. Comparing text numbers and values will yield different results; convert them to values and try again.
3. Each interval needs to be written as a rule. What if there are too many?
If there are more than four or five brackets, consider putting the bracket standard in a helper table, use the lookup function to return the bracket name, and then write a rule for the bracket name. This method only requires modifying the auxiliary table when modifying the standard, without modifying the rules item by item.
4. If the newly added line has no color, does that mean the rule is invalid?
Usually, the application scope of the rule only covers the original data rows. Expand the scope to the last row you need, or reference the entire column in the rule, and the new data will automatically be colored.
5. When setting text conditions with multiple colors, why do two words look identical but don't hit the mark?
Text comparison requires complete consistency, including spaces and full-width half-width symbols. You can use functions like TRIM to clean up the data first, or check for any spaces in the cells that are invisible to the naked eye.
📝 Summary
To set different colors for different conditions in Excel, the key is to write a rule that does not overlap for each color using formulas, use absolute references in columns, and check the order and range in the rule manager. By following the steps above and the checklist operations, you can use the idea of setting multiple colors in Excel conditional formatting and formulas to clearly label the score, progress, or amount table by file.
