Excel の条件付き書式の数式が機能しない
数式は = で始め、適用範囲の左上のセル基準で評価され、通常の数式と同様にずれます。範囲が C2:C100 で閾値が F1 なら =C2>=$F$1 と書きます。=C1>=$F$1 でも =$C$2>=$F$1 でもありません。後者だと列全体が同じ結果になります。
目次
要点
数式は = で始め、適用範囲の左上のセル基準で評価され、通常の数式と同様にずれます。範囲が C2:C100 で閾値が F1 なら =C2>=$F$1 と書きます。=C1>=$F$1 でも =$C$2>=$F$1 でもありません。後者だと列全体が同じ結果になります。
=C2>=$F$1確認のポイント
2026年11月12日の確認では、F1 が 2000 のとき 2500 は TRUE、1800 と 1200 は FALSE でした。行全体を塗るなら適用範囲を A2:F100 にし、=$C2>=$F$1 と列だけ固定します。AND で地域が East かつ 2500 なら TRUE、1200 なら FALSE です。日付の比較には実際の日付値を使います。
注意点と互換性
先頭の = が無い、または数式全体を引用符で囲むと失敗します。引用符が必要なのは数式内の文字列だけで、=$B2="East" のようにします。規則の順序や「真の場合は停止」で後続の規則が見えなくなることがあります。まず補助列に同じ数式を置き、TRUE または FALSE が意図した行と一致するか確認します。
英語の詳細例・数式・著者メモ
Excel How-toTested 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$1That 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$1The $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.