Excel: Make Cells Turn Red or Green Automatically

Yes. In Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting and create rules that apply red or green formatting when a value meets a condition. For example, rules can color a status cell red when it says “Late” and green when it says “Paid”; the formatting updates automatically when the cell value changes.

What makes an Excel cell change color?

The feature is called conditional formatting. It applies a visual format—such as a fill color, font color, or icon—when a cell’s contents meet a rule. Excel checks the rule against the cell or range, then displays the format if the condition is true. When the value changes and no longer meets the condition, the conditional format stops displaying.

This is different from manually choosing a fill color with the paint-bucket button. A manual fill stays until someone changes or removes it. Conditional formatting is rule-driven, so a workbook can show changing statuses, risks, or results without requiring someone to recolor cells by hand. The rule does not change the stored value; a red cell still contains its original text, number, date, or formula result.

Conditional formatting is useful for tasks such as:

Highlighting overdue tasks in red and completed tasks in green.
Flagging negative balances, failed checks, or values below a target.
Showing whether a date is past due or falls within a chosen period.
Drawing attention to duplicates, blanks, or unusually high and low numbers.

Set up red and green rules for text

For a simple status list, built-in text rules are usually the quickest option. Suppose cells A2:A100 contain statuses such as Late, Paid, and Pending. Select A2:A100, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter Late, choose a red fill style, and confirm. Repeat the steps for Paid and select a green fill style. Excel applies each rule to the selected range.

Use a condition that identifies the complete status when possible. A “Text that Contains” rule matches text containing the entered characters, so a rule for “Paid” could also match a longer entry such as “Unpaid” depending on the text. If the statuses are consistent and you need an exact match, formula-based rules provide more control.

To create an exact-match rule, select A2:A100 and choose Home > Conditional Formatting > New Rule. Select “Use a formula to determine which cells to format,” enter the formula below, choose a red fill using Format, and confirm. The layout assumed is status text in A2:A100, with A2 as the top cell of the selected range. For A2 containing Late, this formula evaluates to TRUE; for Paid, it evaluates to FALSE.

=A2="Late"

Create a second rule for the green condition. With the same A2:A100 layout, the formula below evaluates to TRUE when A2 contains Paid and FALSE when it contains another status.

=A2="Paid"

The relative reference A2 matters: Excel adjusts it for each row, checking A3 for the second row, A4 for the third, and so on. If the selected range begins at A5 instead, write the rule using A5 as the reference. Choosing the wrong starting row is a common reason a rule appears to color the row above or below the intended one.

Color cells based on numbers

Numeric rules work the same way, but the condition compares values instead of text. For example, select B2:B100 and choose Home > Conditional Formatting > Highlight Cells Rules > Less Than. Enter 0 and choose a red format. Add a Greater Than rule for 0 with a green format if positive values should be green. The built-in Highlight Cells Rules menu also includes options such as Equal To, Between, and Greater Than or Equal To.

Formula rules are helpful when the comparison is more specific. Suppose B2:B100 contains account balances. Select that range and create a formula rule with the layout starting at B2. If B2 contains -12, the formula evaluates to TRUE because -12 is below zero; if B2 contains 25, it evaluates to FALSE.

=B2<0

For the same B2:B100 layout, a green rule for positive balances can use the formula below. With B2 equal to 25, it evaluates to TRUE; with B2 equal to 0, it evaluates to FALSE. This leaves zero uncolored, which may be preferable to treating it as either a loss or a positive result.

=B2>0

Think through boundary values before setting the rules. If zero should be green, use a “Greater Than or Equal To” built-in rule or make the formula condition inclusive. If zero should be red, use a less-than-or-equal comparison instead. Clear boundaries prevent gaps or overlapping meanings in financial and performance reports.

Use a formula to check more than one condition

A formula rule can combine conditions, allowing a cell to change color only when multiple facts are true. For example, suppose column A holds a task status and column B holds its due date, with records starting on row 2. To mark a task red only when it is Open and its due date is before today, select the range you want to color—such as A2:B100—and create a formula rule using the formula below. It assumes the first record is in row 2. If A2 is Open and B2 is yesterday, the rule evaluates to TRUE; if either condition is not met, it evaluates to FALSE.

=AND($A2="Open",$B2<TODAY())

The dollar signs lock the column references A and B while leaving the row number relative. That matters because the selected range spans two columns: when Excel evaluates the rule for a cell in column B, it should still check the status in column A and the date in column B for that same row. If you want to color only the status cell, select A2:A100 instead. The TODAY function returns the current date, so the condition can change as the calendar date advances and the workbook recalculates.

Use AND when every condition must be true, and OR when at least one condition may be true. Keep the reference layout aligned with the top-left cell of the “Applies to” range; conditional-formatting formulas are evaluated relative to that range.

Choose the right rule type

There is no single best way to set up automatic colors. The simplest approach is usually easiest to maintain, while formula rules handle more complex logic. Consider these options:

Highlight Cells Rules: Best for straightforward text, number, date, duplicate, or blank checks. They are quick to create and easy to understand later.
Formula-based rules: Best for exact text matches, multiple conditions, comparisons across columns, and rules that depend on another cell.
Color Scales: Best for showing relative values across a range, such as low-to-high sales. A scale gradually varies color; it does not simply assign one fixed color to each status.
Data Bars and icon sets: Useful when you want a visual comparison inside or beside a value rather than a simple red-versus-green fill.

For a simple “Late” and “Paid” status, use two separate rules. For a range of measurements where red should mean the lowest values and green the highest, a color scale may communicate the pattern more clearly. If readers need an unambiguous status, retain the text as well as the color. Color alone can be difficult to interpret and is not equally accessible to everyone.

Manage rule order and the Applies to range

Rules can overlap. If two rules both apply to one cell, their order and formatting options can affect what appears. Choose Home > Conditional Formatting > Manage Rules to inspect the rules and their “Applies to” ranges. In the Rules Manager, set “Show formatting rules for” to the current selection or the relevant worksheet so you can find the rule you need. You can edit, delete, or reorder rules there.

Check the “Applies to” range whenever a rule works in some rows but not others. A rule intended for A2:A100 may have been created while only A2:A20 was selected. You can correct the range in Manage Rules rather than recreating the rule. Also check that the formula’s first reference matches the top row of the range.

Excel offers a “Stop If True” option for rule ordering in supported rule-management dialogs. When enabled for a rule, Excel does not evaluate lower-priority rules for a cell after that rule is true. This is useful when rules are deliberately layered, but it can hide a later format if used unexpectedly. If a cell has the wrong color, inspect all rules that apply before assuming the formula itself is wrong.

Why a cell may not turn red or green

When conditional formatting seems not to work, check the rule and the cell values in a logical order:

Confirm the value. A cell that looks like a number or date may actually contain text. Text such as “10” is not always treated like the numeric value 10 in a comparison. Check the underlying entry and the formula result.
Check the exact text. Extra spaces, spelling differences, and longer phrases can affect text rules. A cell containing “Paid ” with a trailing space may not match an exact formula comparison with “Paid.”
Verify the selected range. Open Manage Rules and check “Applies to.” Make sure the intended cells are included and that the first formula reference corresponds to the range’s first row.
Look for competing formatting. Another conditional rule may apply a different format, or a manual fill may show when no conditional rule is active. Review the rules and remove obsolete ones if appropriate.
Check the formula result. Test the logic in an unused cell if needed, adjusting references to the same row. Confirm that the conditions produce the expected TRUE or FALSE result.

To remove a rule, select the affected cells and choose Home > Conditional Formatting > Clear Rules. The menu offers clearing rules from the selected cells or from the entire sheet. Be careful to choose the intended option: clearing the sheet’s rules removes more than the formatting in the currently selected cells.

Excel on Mac and Excel for the web

Conditional formatting is available in Excel for Microsoft 365 on Mac and in Excel for the web, although the layout and wording of dialogs can differ from the Windows desktop steps above. On Mac, use the Home tab’s Conditional Formatting controls to create or manage rules. In the web version, conditional-formatting options are available from the Home tab, but some advanced editing tasks or dialog choices may be presented differently. If you cannot find a particular control, check the version’s ribbon and rule-management pane rather than assuming the workbook cannot use conditional formatting.

Formatting rules are stored in the workbook, so a workbook with conditional formatting can display its colors when opened in supported Excel versions. If a file is opened in another spreadsheet program, the appearance or rule support may differ. For a shared workbook, test important rules in the applications your collaborators actually use, especially if the rules rely on complex formulas.

Frequently Asked Questions

Can Excel automatically turn a cell red when a date is overdue? Yes. Use a date-based conditional-formatting rule, or a formula rule that compares the due date with TODAY(). Make sure the dates are real Excel date values, not text that merely looks like a date.

Can one cell turn red or green based on another cell? Yes. Create formula-based rules and use a reference to the other cell. For example, a status cell can be formatted based on a related due date or approval field. Match the formula references to the top-left cell of the selected range.

Does conditional formatting change the cell’s value? No. It changes the cell’s displayed formatting when a condition is met. The text, number, date, or formula in the cell remains unchanged.

Can a cell automatically change color based on a formula result? Yes. Conditional formatting evaluates the displayed result of a cell formula. If that result meets the rule—for example, a formula returns the text Late—the corresponding format can appear.

How do I remove automatic red or green formatting? Select the cells and choose Home > Conditional Formatting > Clear Rules, then clear rules from the selected cells. Use Manage Rules if you want to delete or edit just one rule instead of removing all rules in that selection.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.