如何根據另一個單元格設定 Excel 條件格式

Tested in: The threshold and comparison formulas below were evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not click through the rule dialogs in Excel this session.

This page is about rules that look at a different cell than the one being painted. For color scales, built-in “greater than” rules, and the basics of formula rules, see Conditional Formatting in Excel.

One threshold cell, many rows

Put the cutoff in its own cell so you can change it without editing the rule. On 12 November 2026 I used 2000 in F1 and tested three sales figures:

=2500>=$F$1
=1800>=$F$1
=1200>=$F$1

Those returned TRUE, FALSE, and FALSE. So 2500 would be formatted; 1800 and 1200 would not. If you write =$B2>=2000 instead, you have to open the rule every time the cutoff moves.

In Excel 365

1. Type the threshold in F1.
2. Select B2:B10 (the sales column, not F1).
3. Open Home > Conditional Formatting > New Rule.
4. Choose Use a formula to determine which cells to format.
5. Enter =$B2>=$F$1. Write it as it should read for the active cell, usually the top-left cell of the selection.
6. Set a fill or a font color, then click OK.

In Google Sheets

1. Select B2:B10.
2. Open Format > Conditional formatting.
3. Set Format cells if to Custom formula is.
4. Enter =$B2>=$F$1.
5. Choose a color and click Done.

If the colors land on the wrong rows, the formula was probably written for a different active cell than the one Excel or Sheets is using. Select B2 first, then extend the range down, then write the formula with B2 in it.

Highlight a whole row from another column

Select A2:C10, not just column B, and lock the column you want to test:

=$A2="East"

Every cell in row 2 now looks at A2; row 3 looks at A3. In the same Sheets check, "East"="East" was TRUE and "West"="East" was FALSE.

To require both a region and a threshold, combine the tests:

=AND($A2="East",$B2>=$F$1)

With F1 still at 2000, AND("East"="East",2500>=$F$1) returned TRUE, and AND("East"="East",1200>=$F$1) returned FALSE. East with sales of 1200 does not pass, because 1200 is below the cutoff. A missing $ on A, such as =A2="East" applied to A2:C10, will test B2 and C2 as they walk right, and only the first column will behave.

The same pattern works for a quantity target in another cell: =$E2>=$G$1. A 40-unit order against a target of 35 returned TRUE in the 12 November 2026 check (=40>=35).

Dates, and a cell on another sheet

To format rows whose date is on or after a start date in F1 (a real date, not text), use:

=$A2>=$F$1

In the test workbook, =DATE(2024,3,4)>=DATE(2024,3,1) was TRUE and =DATE(2024,2,5)>=DATE(2024,3,1) was FALSE. If F1 is text that only looks like a date, the comparison will mis-fire; see dates stored as text.

A threshold on another sheet is just a qualified address:

=$B2>=Settings!$A$1

Keep Settings!$A$1 absolute so every row uses that one cell. If an older Excel build rejects a cross-sheet reference in a conditional formatting formula, define a name that points at Settings!$A$1 and use the name in the rule, or place a copy of the threshold on the same sheet.

The $ signs, and things that look like a broken rule

Write the formula for the active cell, then decide what should stay put. A threshold is almost always absolute ($F$1). A column that supplies the test for a whole row is mixed ($A2). Two columns compared in the same row, such as “actual vs target on this row,” are usually relative in the row and locked in the column: =$B2>=$C2.

Conditional formatting does not change the value in the cell, and you should not count colors with a formula. If you need a count of rows that meet the same test, use COUNTIFS or SUMPRODUCT with that logic, as in COUNTIF vs COUNTIFS vs SUMPRODUCT.

A drop-down for the region, from How to Add a Drop-Down List in Excel, keeps the text in column A consistent so =$A2="East" does not miss "east " with a trailing space. Existing values that were typed before the drop-down was added still need a cleanup.

Stop after you have one working rule and a threshold you can edit. A pile of overlapping formula rules, each pointing at a slightly different cell, is harder to debug than a helper column that holds the TRUE/FALSE test and a single format rule that looks at that helper.