Excel conditional formatting based on another cell?

Yes. In Excel for Microsoft 365, select the cells you want to format, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula that refers to the other cell. For example, with status values in B2:B20 and cells to format in A2:A20, use =$B2="Late" to highlight each A cell when its row’s status is Late.

What conditional formatting based on another cell does

Conditional formatting changes a cell’s appearance when a rule evaluates as true. A rule can look at the value in the cell being formatted, but a formula rule can also look at a different cell—or several cells—to decide whether to apply a fill color, font style, border, or other supported format. The underlying value is not changed, and the rule does not move or sort data.

This is useful when the information that determines a highlight is separate from the information you want to emphasize. For instance, a table might contain customer names in column A and payment status in column B. A rule can color the name when the status says “Overdue,” making important records easier to scan without adding a manual color that can become outdated.

The key idea is that the formula should return TRUE or FALSE. Excel evaluates it for each cell in the rule’s target range. A TRUE result applies the chosen formatting; a FALSE result leaves the cell’s existing appearance in place.

How to create a formula-based rule

With status values in B2:B20 and cells to highlight in A2:A20, follow these steps:

Select A2:A20. The first selected cell, A2, matters because Excel interprets relative references from that starting point.
Go to Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.
Enter =$B2="Late". This formula checks the status in column B on the same row.
Choose Format, select the appearance you want, and select OK to save the format and rule.

To verify the rule, change B2 to Late and check whether A2 receives the selected format. If the status is not Late, the rule should not apply. You can inspect or adjust the rule through Home > Conditional Formatting > Manage Rules. In the Rules Manager, check the “Applies to” range as well as the formula; a correct formula applied to the wrong range will not produce the expected result.

How relative and absolute references work

Cell references determine whether the rule follows the row, the column, or a fixed cell as Excel evaluates different parts of the target range. In =$B2="Late", the dollar sign before B locks the column, while the row number 2 is not locked. When Excel evaluates the rule for A3, it checks B3; for A4, it checks B4. That is why a single rule can work down a list.

Suppose the goal is to color every cell in a record’s row when the status in column D is “Overdue.” With statuses in D2:D20 and full records in A2:F20, select A2:F20 and use =$D2="Overdue". The locked column keeps the check in D, and the changing row lets each record use its own status. If you omit the dollar sign before D, the reference can shift across columns as the rule is applied to the other cells in the row.

A fully fixed reference uses dollar signs before both the column and row. With a target range of A2:A20 and a single control value in F1, the formula =$F$1="Yes" checks that same control cell for every target cell. This is useful for a global switch, such as a cell that turns a highlight on or off. By contrast, a reference such as F1 with no dollar signs can shift as Excel applies the rule.

When in doubt, write the formula as if you were evaluating the top-left cell of the selected range. Then decide which parts of each reference should move as the rule is applied. The F4 key in Windows Excel can cycle reference styles while editing a formula; on Mac, the equivalent key can depend on keyboard settings, and Fn+F4 may be needed.

Useful examples for everyday spreadsheets

With actual sales in C2:C50 and targets in D2:D50, select A2:D50 if you want to highlight the entire record whenever sales are below target. Use =$C2<$D2. For example, if C2 is 420 and D2 is 500, the comparison is true, so row 2 receives the format. If C3 is 610 and D3 is 600, the comparison is false, so row 3 is not highlighted by this rule.

With due dates in C2:C50 and records in A2:D50, select A2:D50 and use =AND($C2<>"",$C2<TODAY()) to highlight rows whose due date has passed while avoiding blank dates. TODAY returns the current date, so the result can change as the date changes. This rule treats a date earlier than today as overdue; it does not mark a due date of today as overdue. If today should count too, change the less-than comparison to less than or equal to.

With names in A2:A100 and the rule applied to A2:A100, use =COUNTIF($A$2:$A$100,A2)>1 to highlight names that occur more than once in the list. The counted range stays fixed, while A2 changes for each item being evaluated. This identifies repeated values, not necessarily records that are duplicates across multiple columns; for a multi-column duplicate check, the rule needs to compare the relevant fields or use a helper column.

With values in A2:A20 and comparison values in B2:B20, selecting A2:A20 and using =$A2<>$B2 highlights entries in column A that differ from their partner in column B. These examples use ordinary comparisons, but a rule can combine conditions with functions such as AND and OR. Keep the formula focused on a clear true-or-false test so it is easier to review later.

When to use a built-in rule instead

A formula rule is flexible, but it is not always necessary. If you are highlighting values based only on their own contents, built-in options are quicker and easier to maintain. For example, Home > Conditional Formatting > Highlight Cells Rules includes options for values greater than, less than, equal to, or containing particular text. The Top/Bottom Rules and Data Bars, Color Scales, and Icon Sets can also suit common analysis tasks.

Use a formula when the condition depends on another cell, combines several conditions, or needs to format a wider range based on one column. Use a built-in rule when it expresses the condition directly and clearly. A simpler rule is usually easier for another person to understand, and it reduces the chance of a reference mistake.

Common problems and how to fix them

The wrong row is highlighted: Check the row number in the formula against the first row of the “Applies to” range. If the range starts at row 2, a row-relative reference usually begins with 2.
The highlight shifts across columns: Lock the reference column with a dollar sign, as in $B2, when every target cell should check column B.
Nothing happens: Confirm the formula begins with an equals sign, the text matches the cell contents, and the “Applies to” range includes the cells you expect. Also check that the chosen format is visible against the existing fill or font.
Blank cells are highlighted unexpectedly: Comparisons can treat a blank like a zero or an empty value. Add an explicit nonblank check when blanks should not qualify, as in the date example above.
One rule seems to override another: Open Manage Rules and review rule order, the “Applies to” ranges, and whether Stop If True is selected. When multiple rules apply to one cell, rule order and the specific formats can affect the visible result.

Text comparisons in Excel are generally not case-sensitive, so a rule checking for “Late” also matches “late.” If capitalization or inconsistent spaces are causing confusion, clean the source values or add a helper column with a deliberate comparison. Also remember that conditional formatting evaluates the cell’s stored value or formula result, not merely what the cell looks like after number formatting.

Rules that depend on another worksheet

A direct reference to a different worksheet in a conditional-formatting formula may be rejected by Excel. A practical workaround is to bring the comparison value onto the same worksheet with a helper cell, then make the conditional-formatting rule refer to that helper. Another option is to define a name that refers to the other worksheet’s value and use that name in the rule. Named ranges can make a rule more readable, but they add something else to maintain.

For example, if a control value is on a separate sheet, place a reference to it in a clearly labeled helper cell on the data sheet and point the rule at the helper. This keeps the conditional-formatting formula straightforward and makes it easier to see what value controls the highlighting. Avoid using a more complicated formula simply to hide where the condition comes from.

Copying, expanding, and maintaining rules

When you add rows to a table, Excel may extend conditional formatting, but the result depends on how the range and table were created and how the new data is added. Check Manage Rules after expanding a list, especially if new rows do not receive the expected format. A rule with a fixed “Applies to” range will not automatically cover cells outside that range.

Copying a cell can also copy its conditional formatting. If copied cells bring unwanted rules, inspect the destination range in Manage Rules and remove or adjust the extra rule. For a spreadsheet used by several people, use descriptive column headers, keep the rule range limited to relevant cells, and document unusual control cells. These habits make it less likely that someone will overwrite a format manually and mistake it for the rule’s result.

In Excel for Mac, the Conditional Formatting commands are available from the Home tab, though the dialog layout can differ from Windows. Excel for the web supports many common conditional-formatting tasks, but some rule-management details and dialog options differ from the desktop application. If a workbook’s rule is difficult to inspect or modify in the web interface, open it in desktop Excel and review the Rules Manager there.

Frequently Asked Questions

Can Excel format an entire row based on one cell?
Yes. Select the full row range you want affected and use a formula that locks the condition’s column but leaves its row relative. For instance, a status check in column D can control formatting across A:F for the matching row.

Can the rule compare two cells in the same row?
Yes. Use a formula that compares the two references, such as sales against a target or a current value against a prior value. Set the “Applies to” range to the cells you want formatted, and use dollar signs only where a row or column should stay fixed.

Does conditional formatting change the cell’s value?
No. It changes the cell’s appearance when a rule is true. It does not replace the value, edit the formula, or change the data used in calculations.

Can a conditional-formatting rule use a value from another sheet?
Often not as a direct worksheet reference in the rule formula. A helper cell on the same sheet or a defined name referring to the other sheet is a common workaround.

Why does a rule work for one cell but not the rest?
The formula may use the wrong mix of relative and absolute references, or the “Applies to” range may be incomplete. Check the formula as it relates to the top-left cell of the range, then confirm the range includes every intended cell.

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.