在Excel中使用条件格式(Ultimate指南+示例)

条件格式是Excel电子表格中最简单而强大的功能之一。

顾名思义,您可以在Excel中使用条件格式在突出显示符合指定条件的单元格时。

它使您能够通过数据集快速添加可视分析层。你可以创建热图,显示增加/减少图标,哈维泡沫并且在Excel中使用条件格式更多的更多更多。

在Excel中使用条件格式(例如)

在本教程中,我将向您展示Excel中使用条件格式的七个惊人示例:

1.快速识别重复

Excel中的条件格式可用于识别数据集中的重复项。

以下是您如何执行此操作:

  • 选择要突出显示重复的数据集。Excel的条件格式 - 重复值对话框
  • 转到首页 - >条件格式化 - >突出显示单元格规则 - >重复值。Excel中的条件格式 - 重复
  • 在“重复值”对话框中,确保在左下拉时选择重复。您可以指定使用右下滴应用要应用的格式。您可以使用自定义格式选项使用一些现有格式或指定自己的格式。Excel的条件格式 - 重复值对话框
  • 单击确定。Excel中的条件格式 - 重复值对话框OK

这将立即突出显示在所选数据集中具有副本的所有单元格。您的数据集可以是单列,多列或在非连续的单元格范围内。

Excel中的条件格式 - 单个多列

也可以看看在Excel中查找和删除重复的最终指南
2.突出显示值更大/少于数字的单元格

您可以在Excel中使用条件格式化以快速突出显示包含值更大/小于指定值的单元格。例如,突出显示销售价值小于1亿的所有单元,或者突出显示小于通过阈值的标记的单元格。

以下是执行此操作的步骤:

  • 选择整个数据集。
  • 回到家 - >条件格式化 - >突出显示单元格规则 - >大于/小于..Excel的条件格式 - 大于小于
  • 基于您选择的哪个选项(大于或小于),将打开一个对话框。假设,选择“大于”选项。在对话框中,在左侧的字段中输入数字。意图是突出显示比此指定数字大的单元格。Excel的条件格式 - 大于5
  • 指定要在右侧的下拉下拉下来符合条件的单元格的格式。您可以使用自定义格式选项使用一些现有格式或指定自己的格式。Excel的条件格式 - 大于5格式
  • 单击确定。Excel中的条件格式 - 大于5 OK

这将立即突出显示数据集中具有大于5的值的所有单元格。Excel的条件格式 - 大于5个突出显示注意:如果要突出显示大于等于5的值,则应使用条件“等于”来应用条件格式。

可以遵循相同的过程来突出显示具有小于指定值的值的单元格。

3.突出显示顶部/底部10(或10%)

Excel中的条件格式可以通过数据集快速识别前10项或前10%。这可能有助于在您希望通过销售数据中的分数或顶级交易值快速查看顶级候选人的情况。

同样,您还可以在数据集中快速识别底部10项或底部10%。

以下是执行此操作的步骤:

  • 选择整个数据集。
  • 回到家 - >条件格式 - >顶/底规则 - >前10项(或%)/底部10项(或%)。Excel的条件格式 - 前10名
  • 根据您选择的内容,它将打开对话框。让我们说你选择了前10个项目,那么它将打开一个对话框,如下所示:Excel中的条件格式 - 前10名对话框
  • 指定要在右侧的下拉下拉下来符合条件的单元格的格式。您可以使用自定义格式选项使用一些现有格式或指定自己的格式。Excel的条件格式 - 前10名格式
  • 单击确定。Excel中的条件格式 - 前10个OK

这将立即突出显示所选数据集中的前10项。请注意,这仅适用于具有其中数值的单元格。

此外,如果您在数据集中有少于10个单元格,并且您选择选项以突出显示前10项/底部10项,则将突出显示所有单元格。

以下是有条件格式如何工作的一些示例:

Excel中的条件格式 - 顶部最重要的示例

4.突出显示错误/空白

如果您在Excel中使用大量的数值数据和计算,您可以知道识别和处理具有错误或空白的单元格的重要性。如果在进一步计算中使用这些细胞,它可能导致错误的结果。

Excel中的条件格式可以帮助您快速识别并突出显示具有错误或空白的单元格。

假设我们有一个数据集,如下所示:

Excel中的条件格式 - 错误数据集

该数据集具有空白单元格(A4)和错误(A5和A6)。

以下是突出显示空的单元格的步骤或在其中有错误:

  • 选择要突出显示带有错误的空白单元格和单元格的数据集。
  • 回到家 - >条件格式化 - >新规则。Excel中的条件格式 - 新规则
  • 在“新格式化规则”对话框中,“使用公式”以确定要格式化的单元格。Excel的条件格式 - 使用公式
  • 在“编辑规则描述”部分中输入以下公式:
    = OR(ISBLANK(A1),ISERROR(A1))

    • 上述公式检查两个条件的所有单元格 - 无论是空白的还是没有,是否存在错误。如果任何条件为真,则返回true。Excel中的条件格式 - 公式
  • 设置要应用于空白或具有错误的单元格的格式。要执行此操作,请单击“格式”按钮。它将打开“格式单元格”对话框,您可以在其中指定格式。Excel的条件格式 - 公式格式
  • 单击确定。Excel中的条件格式 - 公式OK

这将立即突出显示空白或具有错误的所有单元格。

Excel中的条件格式 - 突出显示的错误笔记:您不需要在条件格式中使用公式中的整个范围A1:A7。上述公式仅使用A1。将此公式应用于整个范围时,Excel一次检查一个单元格并调整参考。例如,当它检查A1时,它使用公式=或(ISBLANK(A1),ISError(A1))。当它检查单元A2时,它然后使用公式=或(ISBLANK(A2),ISError(A2))。根据正在分析的小区,它自动调整参考(因为这些是相对参考)。所以你不需要为每个单元格写一个单独的公式。Excel足够智能,可以自行改变小区参考

也可以看看:使用iferror.iseror.在Excel中处理错误。
5.创建热图

热图是数据的视觉表示,其中颜色表示小区中的值。例如,您可以创建一个热图,其中具有最高值的细胞是彩色的,并且随着值减小而导致红颜色的转变。

如下图所示:

Excel的条件格式 -  Heatmap

上述数据集具有1到100之间的值。基于其中的值突出显示单元格。100获得绿色,1获得红色。

以下是使用Excel中的条件格式创建热图的步骤。

  • 选择数据集。
  • 回到家 - >条件格式化 - >颜色尺度,并选择其中一个颜色方案。Excel的条件格式 - 热线图颜色

只要您单击HeatMap图标,它将将格式应用于数据集。您可以选择多种颜色梯度。如果您不满意现有颜色选项,则可以选择更多规则并指定所需的颜色。

注意:以类似的方式,您也可以应用数据BARD和图标集。

6.突出显示其他每行/列

您可能希望突出显示备用行以增加数据的可读性。

这些称为斑马线,如果您打印数据,可能会特别有用。

现在有两种方法可以创造这些斑马线。最快的方法是将表格数据转换为Excel表。它会自动将颜色应用于备用行。您可以阅读更多关于它的信息这里

另一种方法是使用条件格式。

假设您有一个数据集,如下所示:

Excel  - 备用数据中的条件格式

以下是使用Excel中使用条件格式突出显示备用行的步骤。

  • 选择数据集。在上面的示例中,选择A2:C13(排除标题)。如果要包含标题,请选择整个数据集。
  • 打开条件格式化对话框(主页 - >条件格式化 - >新规则)[键盘快捷 - ALT + O + D]。Excel中的条件格式 - 备用行新规则
  • 在“对话框”框中,选择“使用公式来确定要格式化的对话框的单元格”。Excel的条件格式 - 使用公式
  • 在“编辑规则描述”部分中输入以下公式:
    = ISODD(Row())
  • 上述公式检查所有单元格,如果单元格的行号为奇数,则返回true。指定的条件格式将应用于返回true的所有单元格。Excel的条件格式 - 备用行公式
  • 设置要应用于空白或具有错误的单元格的格式。要执行此操作,请单击“格式”按钮。它将打开“格式单元格”对话框,您可以在其中指定格式。Excel的条件格式 - 备用行格式
  • 单击确定。Excel中的条件格式 - 备用行确定

就是这样!数据集中的备用行将突出显示。

Excel的条件格式 - 备用行决赛

在许多情况下,您可以使用相同的技术。您需要做的就是在条件格式中使用相关公式。这里有些例子:

  • 突出显示备用偶数:= iseven(())
  • 突出显示备用添加行:= ISODD(Row())
  • 每3行突出显示:=摩擦(行(),3)= 0
7.使用条件格式搜索和突出显示数据

这一个有点高级使用条件格式。它会让你看起来像一个Excel Rockstar。

假设您有一个如下所示的数据集,其中包含产品名称,销售代表和地理。该想法是在单元C2中键入一个字符串,如果它与任何单元格中的数据匹配,那么应该突出显示。如下图所示:

Excel的条件格式 - 搜索和突出显示演示

以下是创建此搜索和突出显示功能的步骤:

  • 选择数据集。
  • 回到家 - >条件格式 - >新规则(键盘快捷键 - ALT + O + D)。Excel中的条件格式 - 备用行新规则
  • 在“新建格式化规则”对话框中,选择“使用公式”选项以确定要格式化的单元格。Excel的条件格式 - 使用公式
  • 在“编辑规则描述”部分中输入以下公式:
    =($ C $ 2 <>“”,$ C $ 2 = B5)Excel中的条件格式 - 搜索突出显示公式
  • 设置要应用于空白或具有错误的单元格的格式。要执行此操作,请单击“格式”按钮。它将打开“格式单元格”对话框,您可以在其中指定格式。Excel的条件格式 - 搜索和突出显示格式
  • 单击确定。Excel的条件格式 - 搜索并突出显示OK

就是这样!现在,当您在单元格C2中输入任何内容并按Enter键时,它将突出显示所有匹配的单元格。

这是如何运作的?

条件格式化中使用的公式评估数据集中的所有单元格。让我们说你在Cell C2中进入日本。现在Excel将评估每个单元的公式。

当满足两个条件时,该公式将返回一个单元格:

  • 细胞C2不是空的。
  • 单元C2的内容与数据集中的单元格的内容完全匹配。

因此,包含文本日本的所有单元格都会突出显示。

下载示例文件
下载文件

您可以使用相同的逻辑,以创建变体,例如:

如何删除Excel中的条件格式

应用后,除非手动删除它,否则条件格式仍然存在。作为最佳实践,将条件格式保留仅适用于您需要它的那些单元格。

自此易挥发的,它可能会导致一个慢速excel.工作簿。

删除条件格式:

  • 选择要从中删除条件格式的单元格。
  • 转到首页 - >条件格式化 - >清除规则 - >来自所选单元格的清除规则。
    • 如果要从整个工作表中删除条件格式,请从整个工作表中选择清除规则。Excel中的条件格式 - 明确规则
关于Excel中有条件格式的重要事项
  • 有易失性的条件格式。它可以导致慢速工作簿。只在需要时使用它。
  • 当你复制包含条件格式的粘贴单元格,条件格式也会复制。
  • 如果在同一组单元格上应用多个规则,则所有规则都会保持活动状态。在任何重叠的情况下,返回最后应用的规则是偏好的。但是,您可以通过从“管理规则”对话框中的顺序更改顺序来更改订单。

您也可能喜欢以下Excel教程:

Excel电子书订阅

免费Excel书籍

获得51个Excel提示电子书来飙升您的生产力,并更快地完成工作

33关于“在Excel中使用条件格式(终极指南+示例)”的思考“

  1. 如何突出显示即将到来的日期即,我希望空白单元格将颜色更改为当今日期的目标日期

  2. 谢谢,良好的内容!

    是否有一种方法可以通过PowerQuery将数据加载到Excel时进行条件格式稳定?
    问题是我可以添加或删除自定义列到加载的数据,条件格式化保持正常,但如果我将/删除/删除列在PowerQuery中源数据,则条件格式化公式适用于意外列。

    换句话说,如果配方用公式绘制红色“=($ p13”中国“; $ o13”),申请“国家”($ p)和“销售”($ o),添加/删除/重新排序columns in PowerQuery and reload data, coloring applies to i.e. “Product” (now in $P) and “Population” (now in $O), instead of “Country” (now in $Q) and “Sales” now in $B), breaking the whole thing.

    我尝试=(MyTable [Country]“China”; MyTable [销售]“”)但条件格式化公式似乎并没有接受这样的东西作为基于公式的列。

    谢谢!

  3. 我想使用具有任何类型的VBA编码的密码保护Excel文件

  4. 在这里,我需要一些有条件格式的帮助;

    如果在该列中小于特定数量的Charters,我可以使用的公式规则我可以​​用来改变整个列的颜色?例如,当我在列中的字母“D”小于6或超过6次时,我需要眼睛捕手或注意力抓取(将列的颜色变为粗体)?

    在此先感谢您的帮助,

    萨米

  5. 如何将有条件格式化的列表转换为只包含突出显示的单元格的单独列表?

  6. 任何人都可以帮助我解决毫无疑问。

    如果特定值进入单元F5-F17,则需要以不同颜色突出显示单元D5-D17。

  7. 您好 - 我刚刚划分数据(a,a,a,b,c,d d等),例如,单元格K3到K99。我想基于K5中的诸如分隔项数量来格式化另一个相应的单元格。示例,将红色分配给H5如果K5有10个项目,则为K5有5个项目等,如果K5有5项等,则任何人都可以帮助VB代码在Excel中执行此操作请?

  8. 有没有办法将条件格式重叠为一个字段?如,如果我有一行为gannt图表中的项目任务显示一系列日期的一行,但当我输入%完成时,这些行的范围可能会突出显示(或替换)原始颜色所示的原始颜色通过开始和结束日期。

  9. 爱你在这里做了什么。我进一步迈出了一步,并使它肘击升序与当前选择的内容降序。最终有一种黑客工作,但没有你的帮助就无法完成它。谢谢!

  10. 我想基于在前一周的价值是否增加或减少。(少于上周=绿色,大于上周=红色)

  11. Hii我想为我的任务列表申请格式。这个想法是用待处理任务突出细胞红色,并突出显示含有绿色的细胞。是否有可能在Excel中做到

  12. 你好,
    我在搜索示例中完成了什么
    ($ C $ 2“”,Isnumber(搜索($ C $ 2),B5))
    就像这个我有一个部分单词。

  13. 这些例子非常有用。我想将条件格式应用于范围,具体取决于该范围内的值。我有几个类型的类型,并希望复制粘贴那个紧张的形式,以休息所有的各个范围。PLSS PLSS帮助我。

    • 感谢您的评论Mariana .. Nth可能是任何数字。因此,如果要突出显示每第5行,则可以使用条件= mod(行(),5)= 1在条件格式中。

评论被关闭。