EXCEL条件格式，原来数据也可以如此”色”

我们打开一个表格，密密麻麻的一篇数据，这个时候自己也许很清楚，他人查看时确实一头的雾水。我们有什么好的方法来解决这个问题呢？我们可以用条件格式给数据分类添加颜色，实现数据的可视化管理。

我们通过工具栏 开始/数据格式展开如图：

依次显示的工具为突出显示单元格规则，项目选取规则，数据条，色阶，图表集，新建规则，清除规则，管理规则；我们对其中三个为例进行介绍如下：

1、 按照一定规则显示：突出显示单元格规则

我们选取其中一列数据（工号列），然后依次点击条件格式/突出显示单元格规则/大于，可以看见符合条件的单元格变成了我们将要设定的颜色，我们根据判定规则通过选择合适的颜色，点击确定，得到想要的结果。

2、 将数据转换成数据条：

是通过在单元格中的图形长度标识数值大小的一种方法: 选择数据/条件格式/数据条/渐变填充或者实心填充

得到如下的效果，使数据更加形象化

3、 新建规则：

是一种比较灵活的给数据添”色”的方法，我们可以通过不同的规则类型或者设定公式以达到我们想要的效果。

3.1先我们设定标识重复数据的功能：选取单元格/条件格式/新建规则/仅对唯一值或重复值设置格式

在选定范围复选框中选择重复/格式/填充/选取一种颜色/确定/确定

我们发现如果该区域有重复的信息，就会显示出我们设定的颜色；

此种方法经常用来找出重复的姓名或者数据，去重效果非常好；

3.2我们通过设定公式的方法来设定条件格式：

选取其中一个单元格，比如C2单元格；点击条件格式/新建规则/使用公式确定要设置格式的单元格

我们在设置格式对话框中输入公式/设定格式/填充/选择一种颜色然后点击确定：

公式解释为：当单元格内容为1时显示我们选择的颜色如图：

我们点击G2/格式刷 可以将格式传递给其它想要设定的区域（包含1的区域都变成了设定的颜色）

注意：$G$2表示锁定单元格G2的数据，格式刷功能使用后，判断标准都是以G2单元格为标准，因此我们不能锁定单元格；公式中输入=G2=1，格式刷使用后，可以判定当前单元格为1时，显示设定的颜色。

亲，是不是发觉平时黑白的数据也可以很”色”呢？动动小手，给我们的数据化个妆吧。操作过后觉得很棒的，别忘记给小编好评哦。

大家好，这期，我和大家分享一个小技巧：工资表数据秒变工资条！

薪资部门，每个月都得制作一次工资条的，别因手慢，而，耽误了发工资，那可是要挨骂的！

闲话少叙，文末会有完整的动画演示，详细步骤如下：

第一步：插入辅助列，并填充序号

辅助列，在Excel中很常用，往往用来添加过渡数据，便于我们后续处理。

填充序号，那也是基础操作，输入开始序号1，2，然后向下拖拉鼠标填充。

效果如下所示：

第二步：复制填充的序号和标题行

这步，用+C复制和+V粘贴就可以了，把第一步填充的序号，再粘贴一遍，这样就形成了多个空白行，如下效果：

再复制标题行，如下所示：

第三步：对辅助列进行排序

好吧，这步总算知道辅助列的作用了吧，对辅助列排序，效果就相当于隔行插入空白行了，如下效果：

第四步：删除辅助列

好了，到此，辅助列的任务完成了，可以删除了，工资条数据也完成了。如下所示：

完整的动画演示

完整的动画演示如下：

好了，本期工资表数据秒变工资条，我就分享到这！

我是Excel技巧，每日分享实用的Excel小技巧。

Excel是我们职场办公必备软件之一！不仅可以帮助我们处理复杂的数据，还可以帮助我们详细的分析数据，但着实令人头痛的就是录入复杂的Excel数据，那么今天为大家分享几个Excel录入技巧！学会了可以帮你3分钟录入500条数据，职场办公必备！

技巧一、添加Excel下拉框

当我们在录入Excel不规则数据时候，经常需要一步步的复制粘贴操作，着实令人感到头痛，那么下面教大家一个非常简单的方法，设置Excel下拉框，即可快速帮助我们录入复杂的数据！

操作步骤：首先我们先将所以不规则数据添加到任意单元格里面，再鼠标点击【数据】—【数据验证】选择序列，将来源里面添加之前里面的不规则数据，即可完成！

技巧二、一键填充重复数据

我们在录入Excel里面重复的数据是怎么操作的呢？是直接复制粘贴，还是直接往下拉？其实还有一个更简单的方法！

操作步骤：首先选择需要填充的单元格，输入录入的内容，按住快捷键Ctrl+Enter即可完成！

技巧三、录入证件号码

在我们导入身份证号码到Excel里面，这时候你会发现并不能显示出来，那我们又该怎么办么？

操作步骤：全选导入数据，在数据的前面添加英文单引号，再按住快捷键Ctrl+Enter

技巧四、Excel添加前缀

在我们录入Excel数据时候， 有没有发现我们录入数据时候前缀都是一样的，但是就后面不一样，那么有什么办法可以帮助我们自动生成前缀内容呢？

操作步骤：首先我们需要先这是单元格的格式，鼠标右击点击【设置单元格格式】—【自定义】—【类型】，在类型里面输入前缀内容+@即可！

以上就是今天为大家分享的所有Excel录入技巧，你有学会多少呢？想要学什么Excel技巧可以在评论区给我们回复！

春光明媚，连带着小心情也开始发芽了~变成了彩色的~

马上要到踏春的日子了，这不，连Excel也开始色彩斑斓了起来~

本期话题，来自@悠悠静漪 小伙伴，目标是制作出网易上，这样的多彩条形图。

特别鸣谢小伙伴，发现美，并且整理了基础数据源，也就有了本番的教程。

关键知识点：条件格式

数据源整理

对照网易数据的数据源@悠悠静漪小伙伴将网页中的内容，整理成以下Excel表格：

很舒爽，有木有~~完美的数据源格式：

1.标题行字段清晰

2.数据源没有合并单元格，数据格式正确

3.数据源还有ID（序号）列

是的，这就是表姐一直强调的，数据源“二清合二白”的完美体现。

Excel的数据源和真正的做图数据源，还是有区别的。比如，图表展示的位置，需要用合并单元格的方式，将标题内容，进行纵向排列：

在标题单元格，我们设置了：

合并单元格的效果

文字的方向为纵向

设置了自动换行的效果

单元格内用【ALT+回车】设置强制换行

背景色设置

根据网易数读中图示的情况，用取色器，将各类颜色拾取出来：

取色器：

在PPT2013和2016中，已经自带了取色神器。然而，不得不吐槽一下微软爸爸，这么好用的功能，居然没有放到Excel当中。好在咱们有一款，取色神器：color Pix，超级好用的轻量级取色器。

使用方法：

1.在上图软件窗口中，我们可基本看到软件中的所有功能，就如工具栏中的“+”、“-”、“X”分别代表着总在最前面、最小化、关闭。

2.用户在运行软件后，当鼠标在桌面任意位置移动时，软件窗口中的数值是随着自动变化的，这也就是说，当我们运行软件后，即可立即提取桌面颜色；所需要提取的颜色数值，我们在软件窗口左侧即可看到。

3.取色点确定之后，敲击任意键（如：空格）可以锁定该点色彩，便于将光标移动到信息区也用于复制代码。前提是，ColorPix 的程序主窗口处于激活状态，而不仅仅是显示在最前。

4.ColorPix 放大镜模式：单击主窗口左下方的放大镜按钮或提示激活放大镜视图，以便精准的定位复杂颜色区域的某一像素。放大倍率可在 100% 、200%、 300% 、400% 、500% 、600% 、1200% 、2800% 中调节。（说明：高倍放大对于鼠标操作没有实际意义，因为放大镜视图中的抖动会很严重。）

通过ColorPix工具的使用，我们能够快速参考并读取到任意颜色的色值，并将它们用于我们的商务图片配色中。

应用配色方案：

Excel中图表元素颜色的更改方法是：选中目标后，单击【开始】选项卡下填充颜色中的【自定义填充】，选择【其他颜色】:

在弹出的【颜色】对话框中选择【自定义】选项卡，【颜色模式】选择【RGB】，将【红色】、【绿色】、【蓝色】的色值更改为【取色器】所读取的数值，然后单击【确定】按钮即可。

通过取色神器和单元格填充颜色的设置，我们可以完成基础图表的准备工作。注意，在单元格边框的位置，我们可以设置，下边框为：深色、加粗、虚线的效果：

数据条美化

做好基础数据表格的填充颜色与美化后，选中每个色块的数据内容，如：C2：C6（每段数据，都要分别点击，单独设置）

通过点击【开始】选项卡--【条件格式】--【数据条】--【其他规则】：

打开【新建格式规则】对话框--将条形图的外观--设置为之前用取色器取到的目标颜色，如：深蓝色。设置完成后，点击确定。

注意：

1.条件格式--数据条的设置，是根据单元格内数值的大小，在单元格内嵌一个数据条的效果。所以它能够随着单元格数值的大小，实时变化。

2.为了统筹各个模块的数据条呈现在同一标准之下，需要为每组纵向的数据（即满意程度）构建一个统一的恒定标准，也就是最大值是多少。

3.为了美观，每个横向系列中，都要分批次使用条件格式，来设置数据条效果。

来源：E维课堂，作者：表姐凌祯

提高桌面生产力

如果您期待系统学习Excel技能，系统化提高个人财务工作效率，不妨让表姐凌祯一对一来教你！

EXCEL财务技巧初级到高级实战密训班：

看实例听干货学做表（第七期）

案例式教学，定制化服务，实战型技能

8节直播课，就能把你从EXCEL菜鸟培养成大师！

并赠合并多工作表及图表

五一拼团价仅需128元

如此优惠仅剩最后一天，赶紧来~

▼如何购买？戳原文吧！

只有数据的表格是没有表现力的，今天和大家一起分享一个数据条的设置方法。具体步骤如图示。

怎么样，简单实用吧。每天学习一点，日积月累，你一定会成为excel大神。

在 Excel 中，用条件格式制作进度条既可用 Excel 内置的数据条样式又可以自定义。用内置数据条样式有一个缺点，就是默认用选中的单元格最大值作为进度条的最大值，这通常不符合实际需要。而自定义数据条比较灵活，最小值、最大值和颜色都可以自定义，可以用制作出各种满足要求的进度条。以下就是 Excel 用数据条制作进度条的具体方法，包括制作整数、小数、百分比和正负数双色进度条实例以及修改和删除进度条的方法，实例操作所用版本均为 Excel 2016。

一、用Excel数据条内置样式制作进度条

1、假如要给服装销售情况制作进度条。框选要制作进度条的单元格，例如 B2:B6，选择“开始”选项卡，单击“条件格式”，在弹出的菜单中选择“数据条”，展开数据条样式，一种是渐变填充，另一种是实心填充，先预览每一种样式，然后选择一种合适的样式，例如选择“渐变填充”下的“紫色数据条”，则所选样式应用到所有选中单元格；操作过程步骤，如图1所示：

2、用Excel数据条内置样式制作进度条有一个缺点，就是 Excel 会自动把选中单元格中数值最大的作为进度条的最大值，即数据最大的填充满单元格，如果选中单元格中没有一个满足要求的最大值（例如演示中的“销量”要求 1000 为完成任务，但没有一个达到 1000），而把选中单元格中的最大值 859 作为最大值，这样不符合要求，因此这种情况不能简单的选择Excel内置的数据条样式，需要自定义，只有选中单元格有符合要求的最大值时才能选择Excel内置的数据条样式，如图2所示：

二、Excel自定义数据条样式制作进度条

（一）用数据条给小数制作进度条

1、框选要制作进度条的单元格 C2:C6，确保当前选项卡为“开始”，单击“条件格式”，在弹出的菜单中依次选择“数据条”→ 其他规则，打开“新建格式规则”窗口，单击“最小值”下拉列表框，在弹出的选项中选择“数字”，单击“最大值”下拉列表框，选择“数字”，输入 1；单击“颜色”下拉列表框，选择一种颜色，例如“绿色”，单击“确定”，则所有选中单元格根据数值大小标出绿色进度条；操作过程步骤，如图3所示：

2、如果要把小数转为百分比，选中单元格后，可以按快捷键 Ctrl + Shift + %，则小数转为百分比，如图4所示：

提示：按快捷键 Ctrl + Shift + % 把小数转为百分比不保留小数，如果要保留小数，可以按 Ctrl + 1（需要关闭中文输入法），打开“设置单元格格式”窗口，选择左边的“数值”，在右边“小数位数”后输入要保留的小数位数即可。

（二）用数据条给百分数制作进度条

1、框选要制作进度条的单元格 C2:C6，按住 Alt，按顺序依次按 H、L、D、M，打开“新建格式规则”窗口；单击“最小值”下拉列表框，在弹出的选项中选择“数字”，单击“最大值”下拉列表框，选择“数字”，输入 1；单击“填充”下拉列表框，选择“渐变填充”，单击“颜色”下拉列表框，选择“浅蓝”，单击“确定”，则所有选中单元格根据百分数大小用浅蓝渐变填充；操作过程步骤，如图5所示：

2、提示：即使单元格中的数字为百分数，在“新建格式规则”窗口中，“最小值和最大值”也不能选择“百分比”，因为这样制作出的进度条有问题，一方面可能有些单元格无法显示进度条，另一方面会出现与用Excel内置样式制作进度条同样的问题。

（三）用数据条制作正负数双色进度条

1、框选要制作正负数双色进度条的单元格 C2:C6，按住 Alt，按顺序依次按 H、L、D、M，打开“新建格式规则”窗口；单击“最小值”下拉列表框，在弹出的选项中选择“数字”，输入最小值 -1；单击“最大值”下拉列表框，选择“数字”，输入最大值 1；单击“颜色”下拉列表框，选择“金色”，单击“确定”，则所有选中单元格根据数值大小正数用金色填充、负数用红色填充；操作过程步骤，如图6所示：

2、如果只想把数值显示在虚线一边，可以用调整对齐方式实现，例如把数值显示在虚线右边，只需选择单元格 C2:C6 后，单击“开始”选项卡下的“水平对齐方式”中的“右对齐”图标，则所有数值右对齐，如图7所示：

三、Excel修改进度条

1、假如要修改用 Excel 内置数据条样式制作的进度条默认最大值 859 为 1000。选中 B2 单元格，按住 Shift 键，单击 B6 单元格，选中 B2:B6；确保当前选项卡为“开始”，单击“条件格式”，在弹出的菜单中选择“管理规则”，打开“条件格式规则管理器”窗口，选中“数据条”行，单击“编辑规则”，打开“编辑格式规则”窗口；单击“最小值”下拉列表框，在弹出的选项中选择“数字”，再单击“最大值”下拉列表框，选择“数字”，输入最大值 1；按回车返回“条件格式规则管理器”窗口，再次按回车，则修改成功，859 的单元格紫色已经没有占满单元格；操作过程步骤，如图8所示：

2、提示：打开“条件格式规则管理器”窗口也可用快捷键 Alt + H + L + R，按键方法为：按住 Alt，分别按一次 H、L、R。

四、Excel删除进度条

1、选中 B2 单元格，按住 Shift 键，单击 B6，选中 B2:B6；确保当前选项卡为“开始”，单击“条件格式”，在弹出的菜单中依次选择“清除规则”→ 清除所选单元格的规则，则所有选中单元格规则被清除；操作过程步骤，如图9所示：

2、“清除规则”分为“清除所选单元格的规则”和“清除整个工作表的规则”，除上面演示的清除方法外，还可以用快捷键清除；清除所选单元格的规则快捷键为：Alt + H + L + C + S；清除整个工作表的规则快捷键为：Alt + H + L + C + E；按键方法都为：按住 Alt 后，分别按一次后面的键。

Excel不仅仅只是一个数据录入工具，而是非常强大的数据处理工具，而Excel中针对数据处理有一套自己的规则，按照他的规则去做，虽然在录入数据时可能会稍微麻烦点，但却能给我们以后的数据处理提供很大的便利，只要配合简单的技巧及函数，就能轻松完成各种统计汇总。请看下面的一张表格：

可能很多人再做报表的时候都会采用上面的格式，但是这种表格在做各种计算及筛选的时候就会很麻烦。下面我就给大家介绍一下数据规范化的10条建议，让你做出规范的数据表。

1.不要合并单元格

合并单元格给公式的拖拽,排序等操作带来意想不到的麻烦。

2.合计放在第二行

大部分表格将合计放在了最后一行，我的建议是放在2行(针对数据源，表格不加大标题，第一行为标题行)，这样做的好处在于可以使用统一的sum公式(从3到1048576)。另外数据透视表的辅助公式,建议放在表格的左边，因为透视表的宽度有可能发生大幅度的变化，导致右边的公式被覆盖,放在左边就不会有问题了。

3.内容统一

每列数据一个表头，若干数据，确保结构统一。比如一个学校的表格,班级列，一会儿用“二班”，一会儿又变成“2班”，搞到后面会晕死的。统一的数据可以使用筛选,分类汇总等功能获得带有小计的表格,但不要在制表的时候仿照它的样式,把"小计"插在表格的中间位置，这种东西最终会把表格的使用者给害了。同时在字与字之间及每一个字前后都不要有空格，即信息库中所有填写内容都不要有空格，更不能出现无规律的中英文标点符号混在(全角字符和半角字符)。

4.严格遵守时间、日期格式的输入规范，有时候时间格式参与运算，规范化很重要。

5.“只输入一次”原则

数据只输入一次，需要的时候使用函数（如VlOOKUP）对其引用，而不是再次输入相同的内容。

6.仅用颜色来区分数据，必须避免

修改单元格颜色，仅仅是辅助标记，但你想指望颜色来对数据进行处理，必须掌握vba才能实现。这会把简单的问题弄复杂，即便求助也不见得有人搭理，当然了,在表格里面使用不同的颜色，还是许可的。

7.用公式进行拖拽,可以避免出错

规范化的表格，公式应该由拖拽形成，量越大，对此操作的要求越高，否则极易出错。这就要求正确设置相对引用、绝对引用、混合引用（搞不清楚的请看我的另一篇文章“Excel相对引用、绝对引用和混合引用，你确定懂了吗？”）。完全由拖拽现成的公式组，你只要有2个数据计算是正确的，即可确保全部结果都正确。遇到过公式掌握欠佳的同事，公式是单独输入的(实际上可做成拖拽输入)，两个不同的表，数据完全相同，可结果就是错的。

8.改变“一日一表”的习惯

一个月31天，就在一个xls文件内建立31张工作表，初学者很容易出现这种问题，这是大忌。EXCEL有个好东西叫做“数据透视表”，使用他可以很容易的进行汇总，但是如果你是“一日一表”，那就只能慢慢先合并了。

9.不同属性的数据不要放在一个单元格。

不同属性的数据合起来非常容易，使用&连接就可以了，但分开就困难的多。比如:单位:20元/斤,把它分到4个单元格当中:20 元 / 斤。

10.使用统一的表格样式

比如各部门的考勤计划表，由人资设计下发，各部门填写完成后收回，对于统一的样式，可以方便的合并，集中处理。同时，对于其他部门统一下发的表格，我们不能对表格结果做任何更改。

最后还要提醒大家避免引用其它工作表的数据，这条应该不能算做规范，但是由此引发的问题是有的，比如把工作表发给别人，打开的时候总会提问是否更新，这些都让人不爽。根据上面的规则对开头的表格进行整理如下：

原来的合计项我们可以通过分类汇总或者数据透视表轻松得到，如图：

只有数据的报表是没有什么表现力的，今天小编和大家一起分享一个数据条的设置方法。具体的步骤如图所示：

图片来源于网络

step1：在C2中输入公式=B2，将B列的数据引到C列来

step2：选中C列，点击开始——条件格式——数据条——其他规则

step3：在新建格式规则对话框中，勾选仅显示数据条，选择数据条的颜色

step4：将数据条所在列适当调整宽度，对数值排序

step5：如果B列数据发生变化，数据条也会随之变化

（是不是觉得表格瞬间高大上了呀？如果喜欢我的文章，欢迎转发、收藏。如果有疑问，请留言，小编一定会在第一时间回复，谢谢！）

生活中为了完成某项任务，我们通常情况下制作项目完成情况的进度计划，为了更直观的反映完成的进度情况，可以采用动态进度条的形式，下面就分享制作动态进度条的方法和过程。

启动excel2013，调整单元格整体的大小，在单元格a1中输入文字内容，表示xx项目完成进度条，设置字体为方正姚体，字号为36，并设置居中对齐。

选择单元格a2，接着输入公式=datedif（date（2018,1,1）,today（），“D”）/datedif（date（2018,1,1），date(2018,12,31)，“D”）,回车确定。

选择单元格a2，执行开始菜单，选择格式中数据条，接着再次点击条件格式，选择管理规则选项，在弹出的窗口中点击编辑规则按钮，设置最小值和最大值的类型均为数字，并设置最大值为1，点击确定。

执行开始菜单，选择格式中的设置单元格格式选项，接着在设置单元格格式对话框中，切换到数字选项卡，选择百分比，点击确定。这样就完成设置，每过一天，进度条就会自动添加。

条件格式为数据添加数据条

操作示例：将销售业绩用数据条显示

方式一

1、选中销售业绩的所有数字（如果数据量很大可以直接选中整列），依次点击开始菜单中的【条件格式】——【数据条】——按自己喜好选择一个【渐变填充】，完成以上操作，销售业绩就会以渐变数据条显示了；

2、也可以将数据条用纯色填充，只需在【数据条】下从【纯色填充】选择一个即可；

方式二

1、选中销售业绩数据，依次点击开始菜单下的【条件格式】——【新建规则】——【基于各自值设置所有单元格格式】——【格式样式】选择【数据条】，条形图外观里选择一个【填充】颜色，点击【确定】即可，默认是纯色填充即【实心填充】；

2、如果想渐变填充，可在条形图外观里将【填充】修改为【渐变填充】，选择个颜色，然后将【边框】下拉选择【实心边框】，选择一个合适的边框颜色，确定即可。渐变默认是选中的颜色到白色渐变，如果不添加边框颜色，视觉效果就会稍差一点，所以建议添加边框并设置颜色。

关注我们，推送每日更新，查看更多Excel小技巧：

Excel表格实用技巧01、将阿拉伯数字快速批量转换成中文小写或大写

Excel表格实用技巧02、单元格合并及取消合并的三种方式

Excel表格实用技巧03、粘贴如何去除格式？粘贴只保留内容的方法

Excel表格实用技巧04、巧用格式刷快速复制表格样式

Excel表格实用技巧05、快速给单元格添加边框，原来边框可以画出来

Excel表格实用技巧06、利用文字旋转让宽度较窄的单元格容纳更多文字

Excel表格实用技巧07、将数字快速转换成货币格式的两种方法

Excel表格实用技巧08、如何将满足条件的数据突出标记显示

Excel表格实用技巧09、利用条件格式给包含特定文字的单元格设置格式

Excel表格实用技巧10、如何在一堆数据中找出重复的数据

Excel表格实用技巧11、如何快速从数据中找出最大值或最小值