Excel 条件格式进阶:整行变色、隔行填色、公式条件,一篇讲透

做表格的人大概都经历过这个阶段：为了让"已完成"的行变绿，手动一行行刷填充色；为了斑马线，隔一行刷一次；老板改了状态，颜色没跟着变，又得重刷一遍。

条件格式就是干这个的。但大多数人只用了它最浅的一层——选中一列、点个"突出显示单元格规则"。真正让它变成生产力工具的，是公式驱动的那部分：整行跟着状态变色、隔行自动铺底色、临近截止日自动报警。

这篇把三个最实用的进阶用法拆开讲：整行变色、隔行填色、公式条件，每个都给出可直接套用的公式和操作路径，最后集中说几个必踩的坑。

适用版本：Excel 2016 及以上 / Microsoft 365 / WPS 基本通用。操作入口统一在 开始 → 条件格式 → 新建规则 → 使用公式确定要设置格式的单元格。

一、整行变色：让"状态"决定整行的颜色

场景

一张项目跟踪表，A 列任务名、B 列负责人、C 列开始日期、D 列截止日期、E 列状态（下拉：未开始 / 进行中 / 已完成 / 延期）。你想要的效果是：E 列写"已完成"，整行变绿；写"延期"，整行变红——而不是只有 E 列变色。

操作步骤

1. 选中整个数据区域，比如 A2:E1000（注意：从数据第一行开始，别把标题行选进去）。

2. 开始 → 条件格式 → 新建规则 → 使用公式确定要设置格式的单元格。

3. 公式框输入：

=$E2="已完成"

1. 点"格式"→"填充"，选一个浅绿色，确定。

为什么是 $E2 而不是 E2 或 $E$2

这是整行变色的全部秘密：

• $E 锁列：不管条件格式应用到 A 列还是 E 列，判断的永远是同一行的 E 列。

• 2 不锁行：公式会按行"相对下移"，第 3 行自动判断 $E3，第 4 行判断 $E4。

如果你写成 $E$2，整片区域都会去判断 E2 这一个单元格，结果要么全变色要么全不变；写成 E2，A 列判断 A2、B 列判断 B2，每列各判各的，整行颜色就花了。

加第二条规则：延期变红

同一个区域再建一条规则：

=$E2="延期"

填充红色。两条规则可以共存，Excel 会按规则管理器里的优先级从上到下判断。

进阶：一个状态配一个颜色，批量管理

规则多了以后，别一条条新建。在规则管理器（条件格式 → 管理规则）里：

• 把"应用于"统一设成 =$A$2:$E$1000；

• 每条规则只改公式和颜色；

• 调整上下顺序控制优先级；

• 对"已完成"这类互斥状态，勾选"如果为真则停止"，命中后不再往下判断，既省性能又避免颜色打架。

小技巧：想让"已完成"的行整行变灰 + 加删除线（视觉上"划掉"），在格式里同时设字体颜色为灰色、勾选删除线即可，比单纯变绿更有"结案"的仪式感。

二、隔行填色：斑马线不该靠手刷

场景

几百行的明细表，纯白底看串行。想要经典的"斑马线"：奇数行白、偶数行浅灰。

操作步骤

1. 选中数据区域，比如 A2:G1000。

2. 新建规则 → 使用公式。

3. 公式：

=MOD(ROW(),2)=0

1. 格式设浅灰填充。

ROW() 返回当前行号，MOD(ROW(),2)=0 表示偶数行填色。因为公式里没有 $，行号会随每一行自动变化，于是奇偶交替。

如果想让"数据第一行"是带底色的那行

上面那条公式的效果是：第 2 行（偶数）填色、第 3 行白、第 4 行填色……如果你的数据从第 2 行开始，且希望第一行数据就有底色，用：

=MOD(ROW()-2,2)=0

把行号整体偏移 2，让"第 2 行"在公式里被当成第 0 行。

隔行填色 + 整行变色，怎么共存

两个需求叠在一起时，顺序很关键：

1. 整行变色规则放上面（优先级高），并勾选"如果为真则停止"；

2. 隔行填色规则放下面，作为兜底。

这样"已完成"的行显示绿色，其余行按奇偶显示白/灰，互不干扰。

隔行填色 vs 套用表格格式（Ctrl+T）

很多人问：既然"套用表格格式"自带斑马线，为什么还要条件格式？

结论：数据会不断往下长，优先 Ctrl+T；数据区域固定、还要叠别的条件格式，用条件格式公式更可控。

三、公式条件：条件格式真正的天花板

前面两个只是热身。公式条件的价值在于：任何你能用公式表达的判断，都能变成颜色。

3.1 截止日期临近自动报警

D 列是截止日期，想让"7 天内到期且未完成"的行变黄，"已过期且未完成"的变红：

# 7 天内到期（含今天）=AND($E2<>"已完成", $D2>=TODAY(), $D2-TODAY()<=7)

# 已过期=AND($E2<>"已完成", $D2<TODAY(), $D2<>"")

TODAY() 每次重算都会更新，第二天打开文件颜色自动变——这就是条件格式比手刷强的地方。注意加 $D2<>"" 排除空日期，否则空单元格会被当成 0，判成"过期"。

3.2 高亮重复项 / 找出唯一值

选中 A2:A1000：

# 重复值整行标色（假设配合整行变色，区域为 A2:E1000）=COUNTIF($A$2:$A$1000,$A2)>1

# 只出现一次的唯一值=COUNTIF($A$2:$A$1000,$A2)=1

比"突出显示重复值"内置规则强的地方在于：它可以整行高亮，而不只是 A 列那个单元格。

3.3 模糊匹配：包含关键字就变色

=ISNUMBER(SEARCH("紧急",$B2))

SEARCH 不区分大小写，找不到会返回错误值。虽然条件格式通常把错误当 FALSE 处理，但套一层 ISNUMBER 更稳，也更好读。要区分大小写就用 FIND。

3.4 多条件组合：AND / OR 混用

# 负责人是张三 且 状态未完成 → 整行浅蓝=AND($B2="张三", $E2<>"已完成")

# 状态是延期，或（进行中且已过期）→ 整行红=OR($E2="延期", AND($E2="进行中", $D2<TODAY()))

公式条件的表达力上限，基本就是你 Excel 公式的上限。

3.5 引用表头单元格做"动态阈值"

不想把"7 天"写死在公式里？在表格右上角放一个单元格，比如 J1 填 7，公式改成：

=AND($E2<>"已完成", $D2>=TODAY(), $D2-TODAY()<=$J$1)

改 J1 的值，全表预警范围跟着变。做仪表盘、给同事用的模板，这个技巧特别实用。

3.6 彩蛋：用条件格式画甘特图

这是混合引用最漂亮的实战。A 列任务、B 列开始日期、C 列结束日期，D1 到 Z1 是一排日期（D1 写起始日，往右拖填充）。

选中 D2:Z100，新建规则，公式：

=AND(D$1>=$B2, D$1<=$C2)

填充一个颜色，一张甘特图就出来了。

拆解引用：

• D$1 锁行不锁列：往右拖时列变（E1、F1…），往下时永远看第 1 行的日期；

• $B2、$C2 锁列不锁行：往右拖时始终看 B/C 列，往下时跟着行变。

一个公式同时用到了两种混合引用，理解了它，前面所有规则都不在话下。

四、必踩的五个坑

坑 1：区域选错，公式锚点错位。
条件格式公式是相对于"应用于"区域的左上角单元格写的。区域选 A2:E1000，公式里的 $E2 就是以 A2 为基准；如果你区域选的是 B2:E1000，同样的公式会整体错一行。先选区域，再写公式，公式按左上角那个格子来想。

坑 2：整列引用拖慢文件。
=$E:$E="已完成" 看着省事，实际会让 Excel 对 100 多万行做判断，文件卡到转圈。老老实实写具体区域 $A$2:$E$10000，预留足够行数即可。

坑 3：复制粘贴把规则带跑偏。
带条件格式的单元格复制到别处，规则会跟着过去，但引用的列可能完全不对。跨表搬运数据，用选择性粘贴 → 数值，或者粘完去规则管理器里检查一遍"应用于"。

坑 4：插行、加行后区域不自动扩展。
条件格式的区域是"死"的。数据往下长，要么一开始就把区域设大（A2:E10000），要么转成 Ctrl+T 表格配合表格样式，要么用整列（性能换便利，慎用）。

坑 5：多条规则颜色打架。
同一个格子命中多条规则时，按规则管理器里的从上到下顺序，先命中的说了算（勾了"如果为真则停止"就到此为止）。颜色不对，先去看规则顺序，而不是怀疑公式写错。

一句话总结

条件格式的公式，本质就是给每一个单元格出一道判断题：$ 决定"看哪一列/哪一行不变"，相对引用决定"跟着谁往下走"。把这两个符号想明白，整行变色、隔行填色、日期预警、甘特图，都只是同一套逻辑的不同写法。

下次再想手动刷颜色之前，先问自己一句：这个颜色，能不能用一个公式描述出来？能，就交给条件格式。