OmniDocRed Yellow Green Conditional Formatting in Excel
Learn how to color Excel values red, yellow and green with threshold rules, color scales and Yes/No text rules, plus the checks that keep the colors accurate.
If you need an Excel red yellow green conditional format, you probably want a column of numbers, such as sales targets, test scores or project costs, to show at a glance which values are good, borderline or poor. Red, yellow and green, often called a traffic-light scheme, does this well because the colors carry meaning without the reader having to compare every number. The problem is that the built-in options look similar but behave differently. Some color cells by fixed cutoffs, others shade them by their position in the range, and a few rules fail quietly on blank cells or on numbers stored as text.
This guide shows you how to set up both approaches, how to handle Yes/No and either-or values, and how to check that every cell gets the color you intended. The main method is a threshold rule for each color, because fixed cutoffs are easier to explain to anyone who reads the sheet. You will also see when a color scale is the better choice.
How do you add red, yellow and green conditional formatting in Excel? The short answer
Select the numeric range, create one rule per color with clear cutoffs, and check the boundary values. Use a color scale only when relative shading is enough.
| Your situation | What to do | Why |
|---|---|---|
| You have fixed targets, such as 80 for good and 60 for acceptable | Create three cell value rules, one for each color | The colors mean the same thing every time the sheet changes |
| You want to see which values are high or low within one list | Apply a three-color scale | The colors adjust to the smallest and largest values in the range |
| Lower numbers are better, such as defect counts | Use red for high values and green for low ones, or reverse the scale | The colors match the meaning of the metric |
| Your column holds Yes or No text | Use formula rules that compare the cell to "Yes" or "No" | Text cannot use numeric cutoffs |
| Your values come from a PDF table | Export the table to Excel format first, then apply the rules | Rules work on real numbers and text, not on a picture of a table |
Before you start
Check these points first. Each one prevents a common reason for colors that look right on some rows and wrong on others.
- Confirm the numbers are real numbers. Values that look numeric but are stored as text are ignored by number rules. Left-aligned figures are often a sign of this.
- Write down the cutoffs. Decide, for example, that 80 and above is green, 60 to 79 is yellow and below 60 is red. Rules are only as useful as the thresholds behind them, and changing them later is easier when they are written down.
- Decide which direction is good. For revenue, higher is better. For late deliveries or error counts, lower is better, so the colors should be reversed.
- Leave out the header row. Select only the data cells so the header text does not get tested against the rules.
- Plan for readers who cannot tell red from green. Color alone can be hard to read for some people and can look different on a black-and-white printout. Add a text column such as "Pass", "Watch" or "Fail" next to the colored values.
If your data is in a PDF, you can use the PDF editor in the OmniDoc desktop client to run OCR on a scanned file and export the table to Excel, Word or PowerPoint format. Once the figures are in an .xlsx file, the steps below apply.
Setting up a traffic-light rule for a score column, step by step
This example uses a 30-row list of scores in column B, with the scores starting in B2. Replace the cutoffs and cell references with your own.
- Select the data range. Click B2, then drag down to B31 so the selection covers only the scores.
- Open Excel's conditional formatting options. Choose the command for conditional formatting, then pick the option to create a new rule that is based on cell values.
- Create the green rule. Set it to greater than or equal to 80 and choose a green fill. Using a light fill keeps the text easy to read.
- Create the yellow rule with a formula. Choose the option to use a formula to decide which cells to format, and enter =AND(B2>=60,B2<80). Excel adjusts the reference B2 for each row, so the rule works down the whole column. Give this rule a yellow fill.
- Create the red rule. Set it to less than 60 and choose a red fill. Blank cells also count as less than 60 in Excel, so the next section explains how to exclude them.
- Review the rule order in the rules manager. Open the list of rules for the sheet and confirm that the three rules apply to the correct range. If two rules set the same property, such as fill color, the rule higher in the list wins.
- Apply the rules and scroll through the list. Look at a few values near each cutoff to confirm the colors match the thresholds you wrote down.
📊 At a glance
Excel offers more than one way to color values. Choosing the right one depends on whether your cutoffs are fixed.
| Method | How it decides the color | Good fit when |
|---|---|---|
| Threshold (cell value) rules | Compares each cell to a fixed number you set | Targets are set in advance and must stay the same |
| Formula rules | Uses a formula, which can combine conditions or check text | You need Yes/No matching or several conditions in one rule |
| Three-color scale | Shades each cell by its position between the smallest and largest value | You want to see relative differences within one list |
| Icon sets | Adds a symbol, such as a colored circle, to each cell | You want a symbol in addition to the fill, or the sheet will be printed in black and white |
🎯 Which approach fits you?
- Tracking performance against targets. Use threshold rules so a score of 80 is green in every month's report, even when the rest of the data changes.
- Ranking items within one list. Use a three-color scale. For example, you can see which region has the highest sales in a 12-row summary without deciding what counts as high.
- Marking Yes/No or Pass/Fail columns. Use formula rules that compare the cell to the exact text. Add TRIM to the formula if the entries are typed by different people, because extra spaces stop an exact match.
- Sharing a sheet with a printer or a mixed audience. Combine colors with a text label or an icon set so the meaning survives in grayscale.
For Yes/No columns, create two formula rules. Use =TRIM(C2)="Yes" with a green fill, and =TRIM(C2)="No" with a red fill, applied to the whole column. Excel's text comparison ignores capitalization, so "yes" and "YES" both match.
Check the result
Look at the colored sheet against your written thresholds. These checks catch the problems that often show up in practice.
| Check | How to see it | If it's wrong |
|---|---|---|
| Boundary values | Temporarily type 79, 80, 59 and 60 in spare cells in the same column | Make sure the green and yellow rules meet at exactly one point and the red rule starts where you intended |
| Blank cells | Look for red fills in rows that should be empty | Change the red rule's formula to =AND(B2<>"",B2<60) so blanks do not match |
| Numbers stored as text | Check whether a value that should be colored has no color | Convert the text to numbers, then reapply the rules |
| Conflicting rules | Open the rules list and check the order and range of each rule | Move the correct rule to the top, or narrow the range so the rules do not overlap |
| Colors in print | Print a preview in grayscale | Add a text label or icon set so the status is still clear |
Conditional formatting changes only how cells look. The values underneath stay the same, so a sort or a formula that uses those values still works as before. To remove the colors later, select the range and clear the rules that apply to it through the conditional formatting options.
❓ FAQ
1. Can I use icons instead of colored fills?
Yes. Excel's icon sets add a symbol to each cell, and you can combine a symbol with a fill so the status is still readable when the sheet is printed in black and white. Set the icon thresholds the same way you set the color cutoffs.
2. Does conditional formatting change my data?
No. The rules only change the display. A cell that is colored red still holds the original number or text, so formulas, sorts and filters use the stored value.
3. Why does one row have the wrong color even though the rule looks right?
The likeliest cause is a number stored as text or a rule range that does not start at the row where your data begins. Check the cell's type and the range in the rules list. The check table above covers the other common causes.
4. Should I use a color scale or threshold rules for a sales report?
Use threshold rules if the report has agreed targets, because the colors will mean the same thing from month to month. A color scale works better for a quick view of which items are highest or lowest in one month, since it shifts when the data changes.
📝 Summary
Use threshold rules for fixed targets, a three-color scale for relative shading, and formula rules for Yes/No text. Check the boundary values, blank cells and text numbers before you share the sheet, and add a text label so the status is clear without color.
