Excel Conditional Formatting Based on an Adjacent Cell

How-to basics · Updated November 18, 2026

To format a cell based on its neighbor in Excel for Microsoft 365 on Windows, select the cells 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 adjacent cell. For example, with statuses in B2:B20 and cells A2:A20 to format, use =$B2="Done" to highlight each A cell when the status in the same row is Done.

How formula-based conditional formatting works

A conditional formatting rule applies a chosen visual style when a condition is true. A formula rule can check a different cell from the one being formatted, which makes it useful for highlighting a task name from its status, a product from its stock count, or an entire row from one column’s value. The formula does not move, copy, or change the cell’s contents; it only controls the appearance.

Excel evaluates a formula relative to the top-left cell in the range the rule applies to. When the rule covers multiple rows, Excel adjusts relative references for each row. In the opening example, the rule is applied to A2:A20. It checks B2 for A2, B3 for A3, and so on. The dollar sign before B locks the column, while the row number remains relative. This combination is often the clearest pattern for checking a neighboring column.

The formula must evaluate to TRUE or FALSE, or to a value Excel can interpret as a logical result. A comparison such as =$B2="Done" returns TRUE when the status matches. Excel’s ordinary text equality comparison is not case-sensitive, so Done and DONE match. Conditional formatting rules are recalculated as worksheet values change, though the visible result can depend on rule order when several rules affect the same cells.

Apply a rule to an adjacent cell

For a basic setup, decide first which cells should change appearance and which neighboring cells supply the condition. Then create the rule with its reference based on the first cell in the selected range.

  1. Select the cells to format. For example, select A2:A20 if the names in column A should respond to the statuses in column B.
  2. On the Windows ribbon, choose Home > Conditional Formatting > New Rule.
  3. Choose Use a formula to determine which cells to format.
  4. Enter a formula for the first selected row. With statuses in B2:B20 and A2:A20 selected, enter =$B2="Done".
  5. Choose Format, select a fill, font, border, or number format, and confirm the dialogs.
  6. Check the rule’s Applies to range in Conditional Formatting > Manage Rules. It should cover the cells you intend to format, such as =$A$2:$A$20.

Keep the formula’s row number aligned with the first row of the selected range. If the selection begins at A5, a rule meant to check column B on the same row should start with B5, not B2. The formula can be typed without an initial cell selection if you enter the correct starting reference yourself, but matching the range’s top row prevents many off-by-one errors.

Understand relative and absolute references

Dollar signs control how a reference shifts as Excel evaluates the rule across the applied range. A reference such as B2 is relative in both its column and row. The reference $B2 locks the column but lets the row change. The reference B$2 locks the row, and $B$2 locks both. For a vertical range in column A that checks the same-row value in column B, $B2 is generally the useful form.

Suppose names are in A2:A20 and status values are in B2:B20. The condition for highlighting each name when its same-row status is Done is =$B2="Done". When Excel evaluates the rule for A7, the relative row makes it check B7. If you wrote =$B$2="Done" instead, every cell in the range would check B2, so the whole range would share one result rather than responding row by row.

If you format a horizontal range and check a cell above or below it, you may need a different mix of locked references. For example, with values in A1:Z1 and the cells to format in A2:Z2, a rule can refer to the cell directly above using =A1>100. Because both parts are relative, the rule checks B1 for B2, C1 for C2, and so forth. The right reference style depends on how the selected range moves, not just on which cell is adjacent.

Useful examples for common worksheets

These examples show how to adapt the same-row pattern to different conditions. In each case, the range being formatted should start on row 2, and the formula’s row reference should match that first row.

  • Highlight a task name when its status is Done. With task names in A2:A50 and statuses in B2:B50, apply this to A2:A50: =$B2="Done".
  • Flag a product when stock is below a reorder point. With product names in A2:A100 and quantities in B2:B100, apply this to A2:A100: =$B2<10. This flags values below 10, not values equal to 10.
  • Highlight a row when a due date in column B has passed. With records in A2:F100 and due dates in B2:B100, select A2:F100 and use =AND($B2<>"",$B2<TODAY()). The blank check prevents empty date cells from being treated as overdue. TODAY returns the current date, so the result can change as days pass.
  • Format a cell when the adjacent cell is not blank. With values in B2:B30 and cells A2:A30 to format, use =$B2<>"". This checks whether the neighboring cell contains something other than an empty string.

For the overdue example, the selected range includes several columns, but the formula locks column B and keeps row 2 relative. That means every cell in row 2 checks B2, every cell in row 3 checks B3, and so on. This is the standard approach for shading a complete record based on one adjacent-column value.

Choose the right range and condition

The cells in the Applies to range and the cells referenced by the formula have different jobs. The range determines where Excel displays formatting; the formula determines when that formatting appears. To color only a task name, apply the rule to the name cells. To color all details in each matching record, apply it across the row’s columns. A correct formula attached to the wrong range can look like a formula problem even when its logic is sound.

Choose a condition that matches the data type. Text comparisons work for labels such as Done or Review. Numeric comparisons are appropriate for quantities and scores. Date comparisons work when the cells contain actual Excel dates, which are stored as serial numbers, rather than text that merely looks like a date. If a date condition does not behave as expected, check the underlying cell values and number formats; changing the display format alone does not convert text into a date.

For a rule with more than one requirement, use a logical function such as AND or OR. For example, a row could be highlighted only when a status is Open and a priority is High. With statuses in B2:B50 and priorities in C2:C50, apply the rule to A2:D50 and use =AND($B2="Open",$C2="High"). Both tests must be true. With OR, either test can be true. If a condition becomes long, test it in a spare worksheet cell first so you can see whether it returns TRUE or FALSE for representative rows.

Common problems and how to fix them

The most frequent mistake is using a fixed row reference by accident. A formula like =$B$2="Done" checks one status cell for every formatted row. Remove the dollar sign before the row number if each row should be checked independently. The opposite issue can occur when a rule spans several columns: if the referenced column is not locked, Excel may shift the column reference as it evaluates cells to the right. Use $B2 when all cells in a record should check column B.

Another common cause is a mismatch between the formula’s first row and the Applies to range. If the range begins at A3 but the formula begins with B2, Excel’s relative evaluation may check the preceding row. Make the row numbers correspond, then verify the range in Manage Rules. Also check whether another rule applies to the same cells and gives a competing fill or font. In the Rules Manager, rules can be reordered; the Stop If True option can prevent lower-priority rules from being evaluated when an earlier rule is true.

If the rule seems correct but nothing changes, confirm that the chosen format is visible, that the formula starts with an equals sign, and that the referenced values meet the condition exactly. A trailing space in a status cell, for example, can make a text comparison fail. Use a test cell to inspect a condition separately. With the status for a particular row in B2, typing =B2="Done" in an ordinary worksheet cell shows TRUE or FALSE for that one comparison. This test does not apply formatting, but it helps separate logic problems from rule-range problems.

Manage rules and extend them safely

To review or change rules, choose Home > Conditional Formatting > Manage Rules. Set the display scope to the current selection or the worksheet, as appropriate, and inspect each rule’s formula and Applies to range. If you copy formatted cells, Excel may copy or adjust conditional formatting too; review the resulting rules after pasting, especially when copied ranges overlap existing ones. The Format Painter can also carry formatting rules, so it is worth checking the range after using it.

For a list that grows regularly, you can apply a rule to a larger planned range, such as A2:A500, while keeping the formula aligned to row 2. Another option is to format an Excel table’s relevant data column; table rows expand as data is added, though conditional formatting ranges should still be checked after structural edits. Avoid applying a rule to an entire worksheet column unless that breadth is needed. Large ranges and numerous complex rules can make a workbook harder to maintain and may affect recalculation or responsiveness.

On Microsoft 365 for Windows, use the ribbon path given above. Excel for Mac also provides Conditional Formatting and a New Rule option on the Home tab, though dialog layouts and some controls can differ. Excel for the web has conditional-formatting controls as well, but available rule-editing options can vary; if a formula rule cannot be created or adjusted in the browser version, open the workbook in the desktop app. The cell-reference logic itself remains the same.

Frequently Asked Questions

Can Excel format one cell based on the cell immediately to its right?
Yes. Select the cell or range to format and write the formula using a reference to the right-hand neighbor on the first row. For example, if A2:A20 should respond to B2:B20, use =$B2="Done". Adjust the column and starting row to match your layout.

How do I highlight an entire row based on one adjacent cell?
Select the full record range, such as A2:F20, then make the formula refer to the condition column with its column locked and row relative. If statuses are in B2:B20, use =$B2="Done". The formula checks B2 for every cell in row 2, then B3 for row 3.

Why does every row use the same adjacent-cell result?
The formula may have an absolute row reference, such as $B$2, which keeps pointing to one cell. If each row needs its own test, lock only the column and leave the row relative, as in $B2. Also confirm that the selected range and formula begin on corresponding rows.

Can conditional formatting compare a cell with a number or date next door?
Yes. Use a comparison such as =$B2<10 for a numeric threshold or a date comparison such as =$B2<TODAY() for a past date. The referenced cells need to contain real numbers or Excel dates for those comparisons to work as intended.

Does conditional formatting change the cell’s value?
No. It changes the display only; the value and formulas in the worksheet remain unchanged. If you need to write a result into a cell, use a worksheet formula or another data-editing method rather than conditional formatting.

Related

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.