全网最全 Excel 条件格式精讲|5000字20个实战案例,从入门到高阶公式用法全覆盖(完整版)

自学相伴，共同进步，大家好，这里是 EXCEL 自习室。

我们花了 2 周时间，整理了全网最详细的 Excel 条件格式技巧，全部内容超过 5000 字的，有 20 个实战案例，从条件格式的入门到进阶用法全覆盖。

一、什么是条件格式

条件格式是 Excel 中一种「数据自动变色、自动可视化」的智能格式规则。他通过预先设定判定规则，单元格满足条件自动套用预设样式；不满足保持原有样式。

（一）核心逻辑

提前设定判断条件 ，程序会逐行、逐单元格自动校验预设条件，数据满足条件则展示自定义样式（填充颜色、字体样式、边框、数据条、图标等），不满足条件则保持表格原始样式。数据一旦更新，格式会自动同步刷新，无需人工反复修改实现全自动智能标记。

条件格式 = 判断条件 + 自定义格式（和 IF 一样）

（二） 核心目的：让数据“自己说话”

通过自动应用格式来突出关键信息，帮助用户快速洞察规律、发现异常或对比数据。它把枯燥的数字转化为可以“扫描”的图形、颜色和图标，让用户不再被困在数字里，而是能快速聚焦于真正重要的事情。具体来说，它的作用和价值体现在以下几个方面：

二、条件格式的基本功能

（一）如何进入条件格式

Excel顶部菜单栏 → 【开始】 → 【条件格式】（所有版本通用）

（二）五大内置快速规则

内置规则无需编写公式，一键设置，满足80%的日常基础需求。

1. 突出显示单元格规则

用于精准标记符合条件的单元格，快速筛查异常数据、目标数据。

支持条件：大于、小于、等于、介于、文本包含、指定日期、重复值等

基础案例 1：成绩低于60分自动标红

1. 选中需要设置的成绩数据区域
2. 条件格式 → 突出显示单元格规则 → 小于
3. 输入数值「60」，选择「浅红填充色深红色文本」
4. 点击确定，所有不及格分数自动高亮

经典案例2：文本包含关键词自动高亮

场景：人员所在省市中，标记所有包含“广东省”关键词单元格

1. 选中省市数据区域
2. 条件格式 → 突出显示单元格规则 → 文本包含
3. 输入关键词“广东省”，默认「浅红填充色深红色文本」
4. 点击确定，所有含“广东省”的单元格自动高亮

经典案例3：重复值一键标红预警

场景：员工信息表中，标记重复的姓名，避免数据录入错误

1. 选中姓名列数据区域
2. 条件格式 → 突出显示单元格规则 → 重复值
3. 选择「浅黄填充色深黄色文本」，点击确定
4. 所有重复录入姓名自动标黄，无需手动核对

经典案例 4：指定日期区间数据标记

场景：订单表中，标记2026年5月内的所有订单日期，快速筛选当月有效订单

1. 选中订单日期列数据区域
2. 条件格式 → 突出显示单元格规则 → 介于
3. 输入开始日期「2026/5/1」，结束日期「2026/5/31」
4. 选择默认「浅红填充色深红色文本」，点击确定完成

2. 最前/最后规则（排名筛选）

专门用于数据排名、均值对比，快速筛选优质/劣质数据。

支持条件：前10项、前10%、最后10项、最后10%、高于平均值、低于平均值

经典案例 5：高亮销量前3名

1. 选中销量数据列
2. 条件格式 → 最前/最后规则 → 前 10 项
3. 输入3，自定义高亮格式，确定即可

经典案例 6：高亮高于平均分的数据

1. 选中总分数据列
2. 条件格式 → 最前/最后规则 → 高于平均值
3. 自定义高亮格式，确定即可

3. 数据条（数值大小可视化）

在单元格内生成水平色块，数值越大、色块越长，直观对比数据差异，无需图表。

适用场景：销售额对比、任务进度、库存数量、得分对比

特色功能：支持自定义颜色、无边框样式、负值反向显示，可隐藏数值只显示数据条。

经典案例 7：业绩数据条可视化对比

场景：员工月度业绩表中，无需插入图表，用单元格数据条直观对比每个人的业绩高低，快速看出员工业绩差距。

1. 选中所有员工业绩数值区域
2. 点击：条件格式 → 数据条 → 任选一款实心/渐变颜色数据条
3. 自动生成长短不一的色块，数值越大色块越长，数值越小色块越短

实操效果：无需看图、无需比对数字，一眼就能分辨业绩高低，适合报表直观展示、汇报展示。

4. 色阶（热力图）

通过2-3种颜色渐变区分数值高低，形成热力效果，批量展示数据分布规律。

常用配色：红（低值）-黄（中值）-绿（高值）

适用场景：成绩分层、月度数据波动、风险等级评估、温度数据统计

经典案例 8：学生成绩色阶热力分层

场景：班级成绩单中，用红绿黄三色阶自动划分成绩等级，直观区分高分、中分、低分学生，快速掌握整体成绩分布。

1. 选中全班成绩数据区域
2. 点击：条件格式 → 色阶 → 选择「蓝-白-红」三色渐变
3. 系统自动匹配数值：高分蓝色、中分数白色、低分数红色

实操效果：整张成绩表形成热力分层，红色集中为薄弱学生，绿色集中为优等生，一键完成成绩分层分析。

5. 图标集（状态分类标识）

用系统自带图标对数据分级分类，直观展示数据状态。

常用图标：升降箭头、红绿灯、星级、进度圆点

适用场景：KPI考核、任务完成状态、满意度评级、数据涨跌对比

经典案例 9：星级图标做等级评级（考核/评分专用）

场景：员工考核、客户满意度、服务评分表，用星级图标自动划分优劣等级。

1. 选中所有评分数据区域
2. 点击：条件格式 → 图标集 → 选择「五星评级图标」
3. 分值越高星级越多，分值越低星级越少，自动完成等级分层

实操效果：评分结果可视化极强，表格高级干净，非常适合绩效考核、评优报表、学生综合素质评分。

三、新建规则详解（6类官方规则模板）

当内置规则无法满足需求时，可通过【新建规则】自定义逻辑，包含6种核心模板：

1. 基于各自值设置所有单元格格式：对应数据条、色阶、图标集，自定义可视化样式
2. 只为包含以下内容的单元格设置格式：自定义数值、文本、日期条件，拓展突出显示规则
3. 仅对排名靠前或靠后的数值设置格式：自定义排名数量（不限于前10）
4. 仅对高于或低于平均值的数值设置格式：适配整体数据均值筛选
5. 仅对唯一值或重复值设置格式：批量标记重复数据，用于数据去重核查
6. 使用公式确定要设置格式的单元格：高阶核心，支持自定义复杂多条件逻辑，自由度最高

重点答疑：五大内置规则 VS 新建规则前5类（核心区别）

很多同学容易混淆：新建规则的前5类，和前面学的五大内置规则看起来功能一致，实际核心区别如下：

1. 五大内置规则 = 傻瓜快捷模板（固定不可改）

属于系统预设一键模板，条件阈值、配色样式、区间比例全部由Excel固定，只能直接使用，无法修改规则逻辑，优点是操作快、零基础上手，适合简单场景。

2. 新建规则前5类 = 高级自定义模板（全开放可改）

和内置规则底层逻辑完全一致，但是开放了所有权限，可以手动修改系统默认参数，自由度更高：

如：经典案例 10：有负数的数据条填充，修改填充颜色

场景：员工业绩升降情况。

1. 选中所有评分数据区域
2. 点击：条件格式 →数据条 → 选择「其他规则」
3. 可以选择是否显示数字，也可以对负值和坐标轴进行修改

3. 核心使用场景区分

简单快速标记、临时筛查数据 → 用【五大内置规则】 系统默认样式不好看、判定区间不准、需要精细化定制 → 用【新建规则前5类】

四、高阶核心：公式型条件格式（万能用法）

公式规则可突破系统固定模板限制，实现整行高亮、多条件判断、动态日期预警等复杂效果，是办公进阶必备技能。

核心语法要点：选中区域后，公式以「=」开头，实现逐行匹配规则。

经典案例 11：自动添加边框

场景：输入编号，自动添加边框，删除编号，边框自动取消。

1.  选择需要设置的数据区域，然后点击条件格式新建规则，选择使用公式确定格式
2. 输入公式：=A2<>""
3. 点击格式，选择外边框，点击确定

这样的话我们输入一个数据，单元格的边框就会自动添加。

经典案例 12：重复值动态标记

场景：实时高亮重复输入的数据（如重复的订单号、电话号码）。

1.  选择需要设置的数据区域，然后点击条件格式新建规则，选择使用公式确定格式
2. 输入公式：=COUNTIF($C$3:$C$22,$C3)>1
3. 点击格式，选择填充，选择一个填充颜色，点击确定
4. 当输入内容重复的时候，就会高亮一个背景颜色提醒。

经典案例 13：动态日期预警

场景：7天内到期提标黄，已过期数据标绿

1. 选择需要设置的数据区域，然后点击条件格式新建规则，选择使用公式确定格式
2. 输入公式：=AND($E3>0,$E3<7)
3. 点击格式，选择填充，选择黄色，点击确定
4. 再次选择需要设置的数据区域，然后点击条件格式新建规则，选择使用公式确定格式
5. 输入公式：=$E3<0
6. 点击格式，选择填充，选择绿色，点击确定

经典案例 14：销量前 3 名标红旗

场景：销量前 3 名就标红旗

1. 选择需要设置的数据区域，然后点击条件格式新建规则，选择基于各自值设置单元格
2. 格式样式选择图标集，找到【旗帜】分类
3. 第一个图标选择红色旗子，类型选择数字，值输入公式：=LARGE($G$3:$G$17,3)
4. 第二个和第三个选择无单元格图标，其他无需设置.
5. 点击确定

经典案例 15：数据核对

场景：比较两列数据，如果不一致就标红色提醒

1. 选择需要设置的数据区域，然后点击条件格式新建规则，选择使用公式确定格式
2. 输入公式：=$D3<>$E3
3. 点击格式，选择填充，选择黄色，点击确定

经典案例 16：多条件叠加判断

场景：两科成绩统计，单科不及格标黄，两科均不及格标红

1. 选择需要设置的数据区域，然后点击条件格式新建规则，选择使用公式确定格式
2. 输入公式：=and($D3<60,$E3<60)
3. 点击格式，选择填充，选择红色，点击确定
4. 重复以上操作，输入公式=or($D3<60,$E3<60),点击格式，填充选择黄色，点击确定。

经典案例 17：奇偶行隔色填充

场景：长表格隔行上色，避免看错行、提升可读性（单序号浅黄色背景，双序号浅蓝色背景）

1. 选择需要设置的数据区域，然后点击条件格式新建规则，选择使用公式确定格式
2. 输入公式： =MOD(ROW()-2,2)=0  （注意：row()-2 是为了获取第一行数据的序号）
3. 点击格式，选择填充，选择浅黄色，点击确定
4. 重复以上操作，输入公式=MOD(ROW()-2,2)=1,点击格式，填充选择浅蓝色，点击确定

经典案例 18：关键字模糊高亮（异议字样标浅黄，退款字样标深黄）

场景：客户备注、项目名称中，只要包含“退款、投诉、异常”敏感词，整行预警。

1. 选择需要设置的数据区域，然后点击条件格式新建规则，选择使用公式确定格式
2. 输入公式： =ISNUMBER(SEARCH("退款",$E3))  
3. 点击格式，选择填充，选择深黄色，点击确定
4. 其他关键字参照以上步骤设置即可

经典案例 19：根据指定内容填充颜色

场景：当标记优秀时，就标黄显示

1. 选择需要设置的数据区域，然后点击条件格式新建规则，选择使用公式确定格式
2. 输入公式： =$F3="优秀”
3. 点击格式，选择填充，选择深黄色，点击确定

经典案例 20：条件格式的综合应用

场景：一个任务清单表中，需要根据状态、截止日期、完成百分比自动改变整行样式，帮助管理者快速识别

• 已完成任务变灰加删除线（降低视觉干扰）
• 哪些任务已经过期且未完成（浅红色警报）
• 哪些任务即将在7天内到期（蓝色预警）
• 数据异常提醒（100%完成但状态未更新为“已完成”）

1. 选择需要设置的数据区域，然后点击条件格式新建规则，选择使用公式确定格式

• 已完成任务变灰加删除线：=$E3="已完成"
• 哪些任务已经过期且未完成：=AND($D3<TODAY(),$E3<>"已完成")
• 哪些任务即将在7天内到期：=AND($E3<>"已完成", $D3>=TODAY(), $D3<=TODAY()+7)
• 数据异常提醒：=AND($F3=1,$E3<>"已完成")

1. 分别针对以上公式，选择不同的格式样式，点击确定

五、条件格式规则管理

设置好的条件格式，可通过规则管理器进行管理。

1. 规则管理入口

条件格式 → 【管理规则】，可查看当前区域所有规则、编辑、删除、新建规则。

2. 条件格式规则设置

（1） 优先级核心规则

• 列表中上方规则优先级高于下方规则，优先生效
• 高优先级规则会覆盖低优先级规则的格式
• 可通过上下箭头调整规则排序

（2） 停止如果为真（高级开关）

勾选规则后的「停止如果为真」，满足当前规则后，不再执行下方所有规则，可避免格式叠加冲突，多用于多等级判断场景。

（3） 清除条件格式

• 清除所选单元格规则：仅删除选中区域格式
• 清除整个工作表规则：删除表格所有条件格式

六、常见问题及注意事项

1. 正确选择生效区域（格式生效的前提）核心规则：优先选中有效数据区域，禁止无脑选中整行、整列。 原因：选中整列会导致空白单元格也加载规则，不仅表格卡顿，还会出现大量多余格式、判断错乱。 实操标准：从数据首个单元格（如A2）选中至最后一行有效数据，精准匹配数据范围。
2. 公式引用规范（相对、绝对、混合引用） 条件格式公式错乱、全部变色、部分不生效，90%都是引用符号使用错误，三类引用用法严格区分： 

• 相对引用（无$ 例：A2）：仅单个单元格独立判断，只校验自身数据，无需整行联动，适合单单元格格式标记。 
• 混合引用（锁列不锁行 例：$A2）——最常用：固定判断列、随行逐行校验，适配所有整行高亮、跨列多条件判断场景，是高阶公式核心用法。 
• 绝对引用（全锁 $A$2）——极少用：固定锁定单个单元格，仅适用于「全员和某一个固定基准值对比」场景，普通逐行判断禁止使用，否则所有行判断逻辑统一错乱。

1. 公式起始行必须匹配选区首行 公式必须以选区第一行单元格作为基准编写，比如选区从A2开始，公式必须用A2，不能用A1，否则会出现错位、部分行不生效、规则偏移等问题。