Excel 條件格式公式沒有生效

Tested in: The TRUE/FALSE checks cited 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 Rules Manager in Excel this session.

The general feature walkthrough is in Conditional Formatting in Excel. Highlighting from a threshold in another cell is in conditional formatting based on another cell. This page is about a rule you already wrote that is not doing what you expect.

Write the formula for the first cell in the range

Open Home > Conditional Formatting > Manage Rules, select the rule, and look at Applies to. The formula is evaluated for that range’s first cell, then copied down and across with relative references shifting and $ references staying put.

If you apply the rule to C2:C100 and the threshold is in F1:

=C2>=$F$1

That is the form that worked in the test workbook: with F1 at 2000, a sales figure of 2500 was TRUE, and 1800 and 1200 were FALSE. Changing this to =$C$2>=$F$1 would test every row against C2 only, so the whole column lights up or stays empty together. Changing it to =C1>=$F$1 aims one row too high and will read the header or a blank.

To color the entire row when column C is over the threshold, apply the rule to A2:F100 (or however wide the table is) and lock the column:

=$C2>=$F$1

The $C keeps every cell in the row looking at column C; the 2 is allowed to become 3, 4, and so on.

The formula is rejected, or nothing changes

The formula must begin with =. A rule entered as C2>=$F$1 is not a formula in this dialog. Quotes around the whole formula are also wrong; only text values inside the formula get quotes, as in =$B2="East".

AND and OR work the same way they do on the sheet. In the test workbook, =AND("East"="East",2500>=$F$1) was TRUE, and the same test with 1200 was FALSE. If you combine a region in column B with a amount in column C, the rule for a range starting at A2 is:

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

A #VALUE! or #N/A result is treated as “do not format,” so a broken comparison looks like a rule that does nothing. Numbers stored as text fail >= in a way that is easy to miss; convert them as in convert text to numbers. Dates that are text fail a date comparison for the same reason; see dates stored as text. 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 — both sides were real dates.

The rule is fine, but another rule wins

In Excel, rules at the top of the list are applied first. In older workbooks, Stop If True prevents later rules from running. If a “highlight blanks” rule sits above your formula and already colors the cell, your rule may be working and still be invisible. Reorder the rules, or make the fill on your rule more specific.

Google Sheets applies all matching rules, and later rules can overwrite earlier fills. If two formulas are both TRUE, the one that landed last in the list is the fill you see.

A helper column is the fastest check: put =C2>=$F$1 in an empty column on row 2, fill down, and confirm the TRUE/FALSE pattern matches the rows you wanted. If the helper is right and the format is not, the problem is the applied range or rule order, not the expression.