Conditional Formatting Based on Another Cell

Yes. In Excel for Microsoft 365, you can make a cell or range change format based on another cell by creating a formula-based conditional formatting rule. Select the cells to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format; the formula should test the relevant reference and return TRUE or FALSE.

How a Reference-Based Rule Works

Conditional formatting applies a visual style—such as a fill color, font color, or border—when a rule’s condition is met. A formula rule lets the condition look at cells beyond the one being formatted. Excel evaluates the formula for the cells in the rule’s “Applies to” range, adjusting relative references as it evaluates each cell.

For example, suppose column A contains task names and column B contains their status. You can format a task name in column A according to the status in the same row of column B. The formatting is still applied to column A; column B supplies the condition. That distinction is important: selecting the cells to format and writing references to the cells to check are separate steps.

A formula rule does not copy a value or format from the reference cell. It evaluates a condition and applies the format you chose to the selected range. If you want the target cell to display the other cell’s value, use a normal cell formula instead.

Build a Rule for a Cell in the Same Row

Assume task names are in A2:A20, and each task’s status is in B2:B20. To highlight a task name when its status is “Overdue,” select A2:A20, open Home > Conditional Formatting > New Rule, and choose “Use a formula to determine which cells to format.” With the first row of the selected range at row 2, enter:

With task names in A2:A20 and statuses in B2:B20, use this formula for the selected range A2:A20: =$B2="Overdue"

Choose Format, select the appearance you want, and confirm the rule. The dollar sign before B keeps the check in column B, while the row number remains relative. Thus, the rule checks B2 for A2, B3 for A3, and so on. If you instead select A2:F20 to highlight each full task row, use the same formula and set the rule’s “Applies to” range to A2:F20.

Before creating a rule, check the upper-left cell of the intended “Applies to” range. The row number in the formula should usually correspond to that first row. If the range starts at row 5, for example, a formula referring to row 2 may shift the test away from the intended record.

Relative, Absolute, and Mixed References

Reference symbols control how Excel adjusts a reference across the formatting range. A relative reference such as B2 can shift in both column and row; an absolute reference such as $B$2 stays fixed; and a mixed reference such as $B2 fixes the column but lets the row change. The right choice depends on whether every formatted cell should check its own row, a particular column, or one fixed cell.

Same-row check: Use a fixed column and changing row, such as $B2, when each row’s formatting depends on a value in column B.
One fixed control cell: Use an absolute reference such as $H$1 when every cell in the selected range should respond to one setting.
Column-by-column check: Use a fixed row with a relative column, such as B$1, when the condition should move across columns but always check row 1.

For example, suppose sales figures are in C2:C30 and a minimum target is entered in H1. To highlight sales below that same target, select C2:C30. With sales in C2:C30 and the threshold in H1, use: =C2<$H$1. The reference to C2 adjusts down the selected range, while $H$1 remains fixed. This comparison highlights values below the target; it does not change the sales figures.

Useful Examples for Rows, Dates, and Duplicates

Formula-based rules can handle conditions that are awkward to express with the built-in Highlight Cells Rules. In each case, first select the cells that should receive formatting, then adapt the references to match the top row and columns in your own sheet.

Highlight an entire row by status: With records in A2:F100 and status values in column C, select A2:F100. Use =$C2="Late". The fixed column keeps the test in C while the row changes for each record.
Flag a date that has passed: With due dates in B2:B100, select that range. Use =AND($B2<>"",$B2<TODAY()). The blank check prevents empty cells from being flagged, and TODAY() supplies the current date when Excel recalculates.
Mark repeated entries: With identifiers in A2:A20, select A2:A20. Use =COUNTIF($A$2:$A$20,A2)>1. The absolute range stays fixed while the final A2 reference changes for each selected cell.

These examples test text, a date comparison, and a count. They are rules rather than cell formulas: the formula’s TRUE or FALSE result decides whether the selected cells receive the chosen format. For text conditions, make sure the spelling and extra spaces in the data match what the rule expects. For dates, make sure the entries are stored as Excel dates rather than text that only looks like a date.

Reference Cells on Another Worksheet

Excel’s conditional formatting formula rules generally cannot use a direct reference to a different worksheet in the ordinary form used in a cell formula. If the value that controls the rule is on another sheet, a reliable option is to create a defined name for that cell and use the name in the rule. For example, create a workbook-level name such as StatusFlag that refers to the controlling cell on the other sheet, then compare that name in the conditional formatting formula.

With the target cells in A2:A20 and a workbook-level name StatusFlag referring to the other sheet’s control cell, the rule can use: =StatusFlag="Overdue". Create or manage defined names from Formulas > Name Manager in Excel for Windows. Check that the name refers to the intended cell and has workbook scope if the rule needs to use it from multiple worksheets.

Another option is to bring the needed value into the current sheet with a helper cell, then have the conditional formatting rule refer to that helper cell. A helper column is often easier to audit than a more complex rule: readers can see the value being tested, and ordinary formulas can perform the cross-sheet lookup. INDIRECT can also construct a reference from text, but it is volatile and more difficult to maintain, so it is usually not the first choice for a simple status check.

Create, Edit, and Remove Rules

In Excel for Microsoft 365 on Windows, select the formatting range and use Home > Conditional Formatting > New Rule. Choose the formula-based rule type, enter the formula, select Format to set the appearance, and confirm. To review a rule later, use Home > Conditional Formatting > Manage Rules. The rule manager lets you inspect its formula, change its “Applies to” range, edit its format, or delete it.

When managing rules, use the “Show formatting rules for” control to choose whether you are viewing rules for the current selection, the current worksheet, or another available scope. This matters when a rule appears to be missing: it may apply to a different range or be hidden by the current view. If more than one rule applies to a cell, the order of the rules and the “Stop If True” setting can affect which formats appear.

In Excel for Mac, the Conditional Formatting commands are also on the Home tab, although dialog layouts can differ slightly. Excel for the web supports common conditional formatting tasks, but some rule-management options can differ from the desktop app. If you cannot create or fully edit a particular formula rule in your browser, open the workbook in the desktop version of Excel and manage it there.

Common Problems and How to Fix Them

A rule that formats the wrong row or column is usually a reference or range issue. The formula is interpreted relative to the first cell in the “Applies to” range, so verify both together. If you start formatting at A2 but the formula is written as though it starts at A1, the condition may be offset by one row.

Nothing changes: Confirm the formula begins with an equals sign, the “Applies to” range includes the cells you expect, and the condition is actually true for at least one row. Also verify that the chosen format is visually distinct from the existing cell style.
Every row uses the same status: Check whether the row number was accidentally fixed, as in $B$2, when you meant to check each row. For a same-row test, $B2 is typically appropriate.
The wrong column controls the format: Check the dollar signs and the first selected cell. A reference such as B2 can shift sideways when the rule applies across multiple columns; $B2 keeps the test in column B.
Blanks are highlighted unexpectedly: Add an explicit nonblank condition where appropriate, or check whether the cells contain formulas returning an empty string. A cell that looks blank may not be truly empty.
A value does not match the text test: Check for leading or trailing spaces, spelling differences, and whether the status is stored as text. A rule that looks correct can fail when the actual cell content differs.

Use Manage Rules to look for overlapping rules as well. A cell may meet multiple conditions, and a higher-priority rule can affect the visible result. If the logic is getting difficult to understand, test it in a helper column first and inspect the TRUE/FALSE result for representative rows.

Formula Rules or Built-In Conditional Formatting?

Built-in rules are often the quickest choice for straightforward conditions. Home > Conditional Formatting includes options such as Highlight Cells Rules and Top/Bottom Rules, as well as data bars, color scales, and icon sets. These are useful for simple comparisons, duplicate values, rankings, and visual summaries without writing a formula.

Use a formula rule when the condition depends on a different cell, combines multiple tests, or needs specific row-by-row logic. For instance, “highlight this task name when the status in the same row says Late” is a natural formula-rule problem. A built-in rule may be easier to maintain when it already expresses the condition directly, while a formula provides more control when it does not.

Conditional formatting is best treated as a visual cue, not as a substitute for clear data or a calculation that other formulas must use. If a downstream calculation needs to know whether a task is late, calculate that status in a normal worksheet cell; do not try to read the cell’s fill color as the source of truth.

Frequently Asked Questions

Can conditional formatting change one cell based on another cell?
Yes. Select the cell or range to format, create a formula-based rule, and write a condition that refers to the controlling cell. The rule applies a format when its formula evaluates to TRUE.

Can I highlight a whole row based on one cell?
Yes. Select the full row range you want formatted, then write the condition with the controlling column fixed and the row relative. For example, $C2 checks column C for each row when the first selected row is row 2.

Can a rule use a cell on another worksheet?
Usually not as a direct cross-sheet reference in the rule formula. A defined name referring to the other-sheet cell or a helper cell on the current sheet is a practical workaround.

Why does my rule apply to the wrong cells?
Check the “Applies to” range and the formula’s starting row. Excel adjusts relative references based on the range’s top-left cell, so a mismatched starting reference can shift the condition.

Does conditional formatting copy the reference cell’s color?
No. A rule tests a condition and applies the format specified in that rule. To display a value from another cell, use a worksheet formula; to match a color, set the desired format in the conditional formatting rule.

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.