Yes. In Excel for Microsoft 365, conditional formatting can check the value in another cell and change the appearance of the selected cells when a condition is met. For example, to format an entire row when its status is Overdue, select the data rows and create a formula rule such as =$B2="Overdue".
How conditional formatting checks another cell
A formula-based conditional formatting rule returns a result that Excel evaluates as true or false. When the result is true for a cell in the rule’s applied range, Excel applies the formatting you chose. The formula does not need to return a color or other formatting instruction; it only determines whether the formatting should appear.
The key is that Excel evaluates the formula relative to the upper-left cell in the range where the rule applies. In the example above, column B contains the status and the rule applies to rows 2 through 100. The dollar sign before B fixes the status column, while the row number remains relative. As Excel checks each row, it evaluates B2, then B3, then B4, and so on. That is why one rule can format many rows based on each row’s status.
This differs from a built-in rule such as Highlight Cells Rules, which is designed for conditions on the selected cells themselves. When the value that controls the formatting is somewhere else, a formula rule gives you control over the relationship between the cells.
Create a rule in Excel for Windows
For example, suppose a task list has task names in column A, owners in column C, due dates in column D, and statuses in column B. To shade every task row whose status is Overdue:
- Select the rows you want the formatting to cover, such as
A2:D100. Do not include the heading row unless you also want the rule applied to it. - Choose Home > Conditional Formatting > New Rule.
- In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
- With the status values in B2:B100 and the selected range beginning at A2, enter
=$B2="Overdue". - Choose Format, select a fill, font, or border style, and confirm the dialogs.
Each row in A2:D100 will be formatted when its corresponding cell in column B contains the text Overdue. If you want to format only the status cells rather than the entire row, select B2:B100 before creating the rule. The formula can stay the same because the first selected cell is still on row 2.
To inspect or adjust the range later, use Home > Conditional Formatting > Manage Rules. Check the Applies to field as well as the formula. A correct formula applied to the wrong range can look like a formula problem.
Choose the right cell references
Dollar signs control which parts of a reference move as Excel evaluates the rule across the applied range. The most common pattern for formatting a row based on a value in one column is a fixed column and changing row, such as $B2. The column stays B, but the row changes. This is especially useful when the formatting range stretches across several columns.
$B2: Keep checking column B while allowing the row to change. Use it for a row-by-row condition.B$2: Keep checking row 2 while allowing the column to change. This can suit a rule that compares cells across columns against one fixed row.$B$2: Always check the one fixed cell B2. Use this when a single control cell should determine formatting throughout the range.B2: Let both the row and column shift. This is sometimes useful, but it can cause unexpected results when a rule covers multiple columns.
For a fixed control cell, assume a value in F1 should decide whether values in A2:A100 are highlighted. Select A2:A100, then use the formula =A2>$F$1. The comparison cell A2 moves down for each row, while the threshold remains anchored at F1. For instance, if F1 contains 50, values greater than 50 meet the condition.
For row-based rules, make sure the row number in the formula matches the first row of the applied range. If the range starts at row 5 but the formula starts with row 2, Excel will evaluate the wrong row for each selected cell. When troubleshooting, compare the formula’s starting row with the first row shown in the Applies to range.
Useful examples for common conditions
A status column is only one possible trigger. The same approach can compare dates, numbers, or text, as long as the formula produces a logical result. First decide what should cause the formatting, then make sure the formula refers to the right row and the right control cell.
- Flag dates before today: With due dates in C2:C100 and the rule applied to A2:D100, use
=AND($C2<>"",$C2<TODAY()). The first test avoids treating blank due-date cells as overdue; the second checks whether a nonblank date is earlier than today. - Highlight values that differ from a target: With actual amounts in B2:B50 and targets in C2:C50, apply the rule to B2:B50 using
=B2<>C2. Each value is compared with the target on the same row. - Highlight a row when a checkbox-linked cell is TRUE: If linked checkbox results are in E2:E50 and the formatting range is A2:E50, use
=$E2=TRUE. The checkbox control itself must be linked to a cell that displays TRUE or FALSE. - Find text containing a word: With notes in B2:B100 and a formatting range of A2:D100, use
=ISNUMBER(SEARCH("urgent",$B2)). SEARCH is not case-sensitive, so it finds urgent whether the cell uses uppercase or lowercase letters.
These formulas assume the stated layouts and that dates are stored as actual Excel dates, not text that merely looks like a date. If a date condition behaves oddly, check the cell’s underlying value and number format. Formatting changes how a value appears; it does not necessarily change the value’s type.
Manage overlapping rules and priorities
A cell or range can have more than one conditional formatting rule. For example, a row might meet both a rule for an overdue task and a rule for a high-priority task. Excel evaluates rules in the order shown in Manage Rules, and the formatting from multiple rules can interact. If two rules set the same property, such as fill color, the higher-priority applicable rule generally determines the visible result.
Use the rule manager to review rule order and the Stop If True option. That option prevents lower-priority rules from being evaluated for a cell once a rule is true. It is useful when rules are designed as mutually exclusive categories, but it can hide formatting from later rules if enabled without considering their purpose.
Rules can also accumulate when users repeatedly apply formatting to overlapping selections. If the result seems inconsistent, open Manage Rules and choose to show rules for the current selection or the worksheet. Remove duplicates, correct the Applies to ranges, and reorder rules deliberately rather than creating a new rule each time.
Other worksheets and workbook limits
Excel’s conditional-formatting formula rules have restrictions on direct references to another worksheet. A formula that directly refers to a different sheet may be rejected when entered in the rule editor. A practical workaround is to bring the needed value onto the same worksheet in a helper cell or helper column, then base the conditional-formatting rule on that local cell.
For a single control value on another sheet, a defined name can sometimes provide a usable reference: create a workbook-level name that refers to the control cell, then use that name in the conditional-formatting formula. This requires care if the name is sheet-scoped or if the workbook is shared, so a same-sheet helper cell is often easier to understand and maintain. Avoid adding a complicated reference workaround if a simple helper formula will do.
Rules that depend on another workbook can be especially fragile if the source file is closed, moved, or renamed. Keep the inputs in the same workbook where possible, and confirm the rule after moving or sharing a file. Conditional formatting is intended to display a result, not to replace the underlying data or serve as the only record of an important status.
Common problems and how to fix them
If nothing changes, check the rule formula, the first row of the applied range, and whether the condition is actually true for the test data. Temporarily choose an obvious fill color and test a row with a known matching value. Text comparisons can fail when cells contain extra spaces or different wording; dates can fail when stored as text; and a formula can be valid while its Applies to range excludes the cells you expected.
- Every row follows the same result: A row reference may have been locked accidentally. For row-by-row logic, use a reference such as
$B2, not$B$2. - The wrong column controls the color: Check the dollar signs and the formula’s column letter. When formatting across a row, the condition column is usually fixed, as in
$B2. - The first row is offset: Match the formula’s row number to the first row in Applies to. A range beginning on row 3 commonly needs a relative reference beginning on row 3.
- Blank cells are highlighted: Add a test that excludes blanks, as in the date example, or check whether the comparison treats an empty cell as zero.
- The format appears unchanged: Another rule may have higher priority, or the chosen format may be too subtle to notice. Review Manage Rules and test with a contrasting style.
When a formula is more complicated than expected, test its logic in an ordinary worksheet cell first. For instance, enter the condition in a spare column and confirm which rows return TRUE. Then transfer the same logic into the conditional-formatting rule, adjusting references so they are relative to the applied range’s first cell.
Excel for Mac and Excel for the web
In Excel for Microsoft 365 on Windows, the standard route is Home > Conditional Formatting > New Rule. Excel for Mac also provides conditional formatting from the Home tab, though dialog layout and some controls may differ by release. The formula principle—use a formula that evaluates as true or false and align its references with the applied range—remains the same.
Excel for the web includes conditional-formatting controls, but its rule editor and available options can differ from the desktop version. If you cannot create or edit the particular formula rule you need in the browser, open the workbook in desktop Excel and manage the rule there. After editing in any version, review the rule’s range and formula to make sure the behavior is preserved.
Frequently Asked Questions
Can conditional formatting format an entire row based on one cell?
Yes. Select the full row range you want to format, then use a formula that fixes the condition column and leaves the row relative. For a status in column B starting on row 2, the pattern is =$B2="Overdue".
Does the formula go in the cell or in the rule?
Enter it in the formula box for a new conditional-formatting rule. It does not need to be entered in the worksheet cell itself, although testing the logic in a spare cell can help with troubleshooting.
Can the condition come from another worksheet?
Direct cross-sheet references may not be accepted in a conditional-formatting formula. Use a helper cell on the same sheet, or consider a defined name that refers to the other sheet’s cell.
Why does the rule work on one row but not the others?
Check whether the formula uses a fixed row such as $B$2 when it should use a changing row such as $B2. Also make sure the formula begins with the same row number as the first row in the Applies to range.
Can one rule compare two cells on the same row?
Yes. A formula can compare corresponding cells, such as an actual value against a target in the same row. Select the cells to format and use relative references that shift together as Excel evaluates each row.
Related
- Excel Repeat Action Shortcut: When to Use Ctrl+Y or F4
- Excel Shortcut to Resize Cells to Fit Text and Data
- Excel Shortcut to Remove Gridlines: Windows and Mac
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Page Break Dotted Lines: How to Turn Them OffTo hide page-break dotted lines in Excel for Microsoft 365 on Windows, select the affected worksheet, then go…
- How to Insert a Signature in Word on Windows and MacTo insert a signature in Word, add a picture of your handwritten signature, draw one with Word’s ink…
- Excel Group Shortcut: Group Rows, Columns, and DetailsIn Excel for Microsoft 365 on Windows, select the rows or columns you want to collapse and press…
- Google Sheets: How to Add Up a Column of NumbersIf your numbers are in B2:B10, enter =SUM(B2:B10) in an empty cell to add them; Google Sheets displays…
- How to Create a Bookmark Template in Word You Can ReuseTo create a reusable, printable bookmark template in Word, set up a page with bookmark-sized table cells, design…
- How to Create Columns in a Word Document: A Simple GuideTo create columns in a Word document, open the Layout tab, select Columns, and choose a layout such…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.