Excel 條件格式：公式、色階與整行突出顯示

Tested in: The sample table and the helper columns below were evaluated in Google Sheets on 12 November 2026. The helper columns use the same formulas as the rules, so you can see TRUE/FALSE next to each row. Excel 365 menu names are the current desktop labels; I did not re-run the clicks in Excel this session.

A small sales table

Six rows, with a header in row 1. Sales are numbers; close dates are real dates. The average of B2:B7 is 1,290.

Built-in rules: greater than, duplicates, color scales

Not every rule needs a formula. In Excel, Home > Conditional Formatting > Highlight Cells Rules > Greater Than and enter 1000 will format B2, B4 and B6 on this table (1,200, 2,500 and 1,800). Duplicate Values is the same menu one item lower; for highlighting matches across two columns, use the dedicated walkthrough in Highlight Duplicates Across Two Columns.

Color scales (Excel: Home > Conditional Formatting > Color Scales; Sheets: a Color scale rule on the numeric range) paint a gradient. On this list, 450 would sit at the low end and 2,500 at the high end, with 1,200 and 1,800 somewhere in between. Use a scale when you want a quick sense of high vs low, and a formula when you want a hard cutoff or a condition that involves more than one column.

Icon sets work the same way as scales — they need a numeric range — and they become cluttered once you have more than a couple of columns. I would not put icons and a color scale on the same cells.

Formula rule: sales of 1,000 or more

Select B2:B7, add a formula rule, and use:

=$B2>=1000

Pick a fill (light green is the usual choice) and save. The helper column I ran in Sheets, with that exact formula in each row, returned TRUE, FALSE, TRUE, FALSE, TRUE, FALSE — matching 1,200, 2,500 and 1,800.

Why =$B2 and not =B2? If the rule applies only to column B, both work. The moment you widen the applies-to range so a whole row can pick up the color, B without the $ starts sliding: C2 would test C2, not B2, and the close date would be compared to 1,000. Lock the column; leave the row number relative.

Highlight the entire row

Select A2:C7 (not the header), and use a formula written for the top-left cell of that selection, A2:

=AND($A2="East",$B2>=1000)

On the test table this is TRUE for rows 2 and 4 (East with 1,200 and East with 2,500) and FALSE for everyone else, including South’s 1,800 — the sales clear 1,000, but the region is not East. That is the point of AND.

In Excel, this is still New Rule > Use a formula. In Sheets, set the apply range to A2:C7 and paste the same custom formula. One rule formats all three columns because each cell in the row evaluates $A2 and $B2, not “the cell I happen to sit in.”

Above average, and a duplicate-region check

A cutoff that moves with the data is often more useful than a fixed 1,000. This flags values above the average of the sales column:

=$B2>AVERAGE($B$2:$B$7)

Lock the average range with both dollar signs, or a new row at the bottom will be left out of the average (or a header will be pulled in). Against this data, the average is 1,290, so 2,500 and 1,800 are TRUE; 1,200 is not, even though it cleared the 1,000 rule.

To mark a region that appears more than once in A2:A7:

=COUNTIF($A$2:$A$7,$A2)>1

East and West each appear twice, so their rows are TRUE; North and South are FALSE. This is the same COUNTIF idea used for duplicates, just applied to a label column. If you only want to color the second occurrence and leave the first alone, use >1 on a expanding range like $A$2:$A2, which is a different rule and easy to get backwards — check it on a helper column first.

How to write the formula so it stays put

Write the formula as if it will be evaluated in the first cell of the applies-to range. Then ask, for each reference:

• Should this column stay the same in every cell of the row? Put a $ before the letter ($B2).
• Should this row stay the same? Put a $ before the number (B$2). That is rare in a “each data row” rule.
• Is this a fixed block, like the average range? Lock both ($B$2:$B$7).

A rule that “works on the first row and then looks random” is almost always a missing $. The helper-column method above is the fastest way to confirm: paste the formula next to the data, fill down, and read TRUE/FALSE before you trust the color.

Sheets vs Excel, and a few limits

The formulas on this page are the same in both programs. The menus are not. Excel groups built-in tests under Highlight Cells Rules and puts formula rules behind New Rule. Sheets puts everything in the Conditional formatting sidebar and calls the formula option Custom formula is.

Rules are evaluated in order. In Excel you can open Conditional Formatting > Manage Rules and tick Stop If True. Sheets applies all matching rules, and later rules can stack on earlier ones. If two fills fight, check the order before rewriting the formula.

Conditional formatting is not a filter. Hidden rows still hold their values; a color does not remove them from SUM. For a count of the highlighted rows, write the same condition in a cell with COUNTIFS or SUMPRODUCT — see COUNTIF vs COUNTIFS vs SUMPRODUCT — rather than trying to count colors.

Dates in column C only format correctly if they are real dates, not text. A rule such as =$C2<DATE(2026,1,1) will skip a text date or treat it as something else. Convert those first with the steps in Dates stored as text.

And one practical limit: a sheet with thousands of formula rules, each pointing at entire columns (B:B), will feel slow. Point the rule at the used range, or at a table, and delete rules you no longer need.