转载自百家号作者：因为专注

在使用Excel过程中，您是否碰到将单元格内常规格式（即通用格式）的数字改成文本格式后，在将该单元格改回常规格式，发现目标单元格的内容依旧是靠左对齐（注：数字在常规格式下是靠右对齐的），且左上角有一个小三角形标志，这意味着虽然单元格显示的是常规格式，但是该单元格中的内容还是文本格式。那么如何才能将这类单元格转换为真正的常规格式呢？本教程下面将介绍几种方法来应用此类情况。教程以Excel2013为例，其他版本可能有细微差异。

1、针对格式已经调整为常规格式，数字内容却仍以文本格式显示这种情况：若数据较少，推荐使用双击单元格法，即先鼠标左键双击单元格，然后在鼠标左键单击其他任一单元格即可。若数据较多，推荐使用错误检查按钮，首先选择需要处理的单元格区域，然后单击单元格旁边的错误检查按钮，选择转换为数字即可。

2、针对单元格数字处于文本格式，如果将其转变成数字格式。方法一：使用错误检查按钮，方法同上，在这里就不再过多叙述。方法二：乘1法（或除1法），首先在其他空白单元格输入数字1（默认是常规格式，即使是文本格式也行），复制单元格内容为1的单元格，然后选择需要转格式的单元格区域，单击剪贴板功能区的粘贴命令下的选择性粘贴，在选择性粘贴窗口选择运算为乘或除，然后单击确定键即可。方法三：分列法，此方法特别适用于需要转变成数字的单元格都输入同列中，且有明显的分列分隔符号。具体操作，首先选择目标列，然后单击菜单栏数据，在数据工具功能区找到分列命令，在文本分列向导第一步中将文本类型设置成固定宽度，在第二步中设置宽度，宽度设置需要包含所有内容，在第三步中设置列数据格式为常规，单击完成即可（注：此法对于没分隔符号的数字或内容不需要分成多部分的单元格无效）。方法四：将目标区域先设置成常规格式，然后就回到了第一种情况。

如果对您有点帮助的话，请帮忙点个赞或分享一下，您的支持是我最大的动力，谢谢！

一、概述

经过前面几节课的学习，大家对常规、数值、货币、日期、时间、百分比、分数、科学记数和文本都已经掌握了，现在这一节，亦凡就讲一下“特殊”和“自定义”这两个格式；

二、数据的特殊与自定义格式

1、特殊格式

右键—设置单元格格式，进入“设置单元格格式”对话框，选择“特殊”这个格式，你会看到这里面就有三个，内容有邮政编码、中文小写数字和中文大写数据，结果如下图所示，

2、自定义格式

在讲自定义之前，大家要选看一下自定义的一般结构式，一个结构式里，最多只能有三个分号，如果是有表颜色的，[颜色]放在最前面，如果[结果]不想更改，直接略过，结构式如下：

要想自定义数字格式，应从选择某一内置数字格式开始比如，选择“日期”这个，你会看到有很多的格式，如果你都不满意，可以更改该格式的任意代码部分，从而创建自己的自定义数字格式。

我们之前做过一个有关学生成绩的表格，里面的分数有高有低，如果我们想要里面分数高于或等于90分的颜色标为红色，这个表格还是小表格，一眼就可以看出所有高于或等于90分，但是如果是几百甚至是几千个人的表格时，那么自定义就有绝对的优势了。

选中区域，进入“设置单元格格式”对话框，选择“自定义”格式，填写内容为：[红色][ >=90]；[黑色]

颜色标前面，结果不更改，通过上面的结构式，这个大家应该是可以理解的，这样自定义后，表格中大于等于90分的，就会自动变为红色的；

如果想做更改结果，可以这样自定义，[红色][>=90]▲▲▲；[黑色]，结果如下所示：

其实对于自定义数字格式还是有很多知识点的，大家有空可以在Excel的“帮助”中搜索一下“创建自定义数字格式”，里面会有很全面的讲解，

接下来会持续更新有关Office的知识，欢迎大家关注哦，有什么Excel问题也可以留言哦！

转载自百家号作者：Excel函数公式

实际的工作中，我们有时候需要用Excel来记录项目的完成进度，但是又不想手动操作，这时候我们应该怎么办了？其实，我们可以利用Excel的【自定义格式】和【条件格式】来完成。

一、效果图。

1、从效果图中我们可以看出，如果每个阶段已开始工作，用√来标识，如果所有阶段全部完成，“总体情况”为“√已完成”；如果所有阶段都未开始：“总体状态”为“×未开始”；如果开始了部分，“总体情况”为“！进行中”。

2、而且随着工程进度的变化，“总体情况”也随着发生变化。这样的图表是不是也很方便呢？

二、步骤。

1、判断工程进度情况，如果已完成，显示1，未开始，显示-1，进行中，显示0。

方法：

在目标单元格中输入公式：=IF(COUNTIF(D3:K3,"√")=8,1,IF(COUNTIF(D3:K3,"√")=0,-1,0))。

解读：

利用IF和COUNTIF函数判断D3:K3区域中√的个数，如果为8，则返回1，如果为0，返回-1，否则返回0。

2、自定义格式。

方法：

1、选中目标单元格。

2、快捷键：Ctrl+1打开【设置单元格格式】对话框。

3、选择【分类】中的【自定义】，并在【类型】中输入：已完成;未开始;进行中（注：类型中输入的分号为英文状态下的分号）。

4、【确定】。

3、图标设置。

方法：

1、选中目标单元格。

2、【开始】-【条件格式】-【新建规则】。

3、单击【选择规则类型】中的【基于各自值设置所有单元格的格式】；选择【格式样式】中的【图标集】；选择【图标样式】中的目标图标。

4、选择左下角第一行【类型】中的【数字】，【值】中输入：1；选择第二行【类型】中的【数字】并【确定】。

结束语：

本示例中主要应用了IF和COUNTIF函数，以及自定义格式和条件格式的内容，如果单个知识点拿出来应用，相信大家都会使用，但是如果组合使用，那就需要下点儿功夫了。

如果在学习过程中，遇到任何可疑之处，欢迎大家在留言区留言讨论哦！如果没有可疑之处，还可以发表敢想敢言哦！

上期和大家分享了自定义条件格式的数字版 。本期继续和大家分享文本的自定义单元格格式！

继续强调啰嗦两件事情！！！

1、自定义单元格格式只改变显示内容，不改变实际值

2、没有，同第一件事！

噢，想起来了，基本规则看上一期，链接在前面！

自定义单元格文本版！开始！！！

一、基础知识分享

1、"@"文本占位符

基本用法：@其实代表着原始的单元格内容，@与其它文本结合，可以让单元格加上某个固定的文本。

代码介绍1：@"最帅"（所有输入单元格的文本后面加上最帅两个字）

代码介绍2："中国"@"省"（所有输入单元格文本前后加上字）

代码介绍3："讨厌你"@"、喜欢你"@"、离不开你"@@"!!!"（你喜欢就好，想加多少加多少，还可以加标点）

2、"*"重复 直至填满至列宽

基本用法：记得Excel中的通配符用法没，通配符的*代表任意长度任意字符。条件格式中的*代表重复*后面接的字符，直至填充至整个单元格。*后面不可以加汉字哦！！

代码介绍1：@*$（所有输入单元格的文本后面加上$符号，填满单元格）

代码介绍2：**;**;**;**（无论什么，单元格显示都是*）（还记得最初的约定，不！记得最初分享的规则了没，不记得看上一期。

不想看上一期我重复一遍。那就是条件格式的用法，正数显示什么；负数显示什么；0显示什么；文本显示什么，所以这种写法，无论单元格内有什么东西，都会显示*）

3、"!"显示无法正常显示的字符

基本用法：比如双引号啦，逗号啦在自定义单元格格式中是有特殊用法的，无法直接显示出来，这个时候需要！的帮忙啦！

代码介绍：@!"!"（单元格文本后面加两个双引号）

二、案例自查

1、如何在销售额后面加上元字

代码：@"元"

哇！怎么没有反应？？？？

原来Excel认为我的销售额是数字形式存储的，而@这种文本占位符只对文本单元格有效，所以不认可我的条件格式哇！

怎么强制转换为文本形式存储呢？就是进行一次文本运算，强制转换为文本形式存储！

然后再重新设置条件格式！bingo！！

2、如何在学员名字前加上3年（二）班

代码："3年（二）班"@ 这个比较简单，没啥解释哈！

3、想隐藏单元格，无论什么都不显示

代码：;;;

记得之前说的单元格全部用*显示没，思路一样的，什么都不输入，那就什么都不显示啦！自己试着玩一下！

4、如何将销售额用万元显示

代码：0!.0,"万元"

好好解释一下这个代码，

0!.0,"万元"首先后面接个万元很好理解哈！

0!.0,"万元" 这个逗号是嘛意思呢？记得不，不记得看上一期。

他是数字除以1000的意思

我先不说叹号是什么意思哈！

假如代码是00,这样的话是什么意思呢？是不是只是原有的数字除以1000。

那！.是什么意思呢？是强制加一个字符进去。相当于在原有数字除以1000后，强制加一个点进去。

1235678 用00, 显示的结果就是1236 除以1000后四舍五入

1235678 用0!.0, 显示的结果就是123.6 除以1000后四舍五入后，倒数第二位加了个点。

下期不见不散！

图文作者 阿忠凯

设置单元格格式时最后一个数字分类是“自定义”，这个选项很强大，就像魔术师一样，很擅长伪装，使用了它，你只需要正常输入数据，数据会自动化装成别的样子，或消失不见了，或多了尾巴，或完全变样了。可以设置的自定义格式非常多，不过最常用的是以下几个，掌握它们日常工作就基本够用了。

添加单位

格式为：“0 "单位"”

这个输入数字后，数字会自动长出个尾巴（单位）。注意“0”和单位之间应该保留空格，否则数字和单位会挤在一起，非常难看。用这种方式设置单位，不影响公式计算。

设置数字为保密格式

格式为：“**;**;**;**”或“;;;”

设置之后数字全都隐身了，看上去只有“*”或空白，不过用鼠标点击单元格，仍然可以在输入栏上看到数字。打印表格时看到也是星号或空白，虽然保密性不是很好，仍然有一定的用处。

设置手机号分段显示

格式为：“000 0000 0000”或“000-0000-0000”

设置之后数据就会自动变形。这种方式只能用于设置长度少于16位的数字，16位及以上的数字用这种方式设置使会出错。

快速输入性别和对错

格式为：[正数];[负数];[0];文本

因此当你设置为“[=1]"男";[=2]"女"”后，输入数字“1”就会显示“男”，输入数字“2”就会显示女，类似的你也可以用来输入“√”和“×”。传说中“输入和回报完全不成比例”说的就是这个吧。

零值不显示

格式为：“G/通用格式;G/通用格式;;G/通用格式”

其中“G/通用格式”表示保持原样，第三个选项没有内容，因此可以实现零值不显示。类似的，你也可以将第二个“G/通用格式”去掉，变成负数不显示。这正是“学了本事，你想让谁隐身，就可以让谁隐身”。

设置日期相关格式

格式如下表红色字体所示，可以将一个日期设置为星期、英文星期，可以获取它的年份、月份、日期。哇，这种自定义格式“整容”整得太彻底了，数据完全没有原来的容貌。

这些自定义格式代码也可以和TEXT函数一起使用，效果相同哦。

非常重要的注意事项

设置了自定义格式之后，虽然你输入数据后，看到数据变了样，但也仅仅是外表变了样，实际上并没有变化，就像你改了名字，名字虽然不同了，但你还是你。

基于自定义格式不会改变数据的本质，因此不宜过度使用，虽然它还可以实现条件格式、计算等功能，不过我不建议使用这些功能，除非你的数据只是用来看的，或者你要炫耀你的Excel水平。因为这样做了之后，数据在计算时很容易出错，而且在别人的电脑上打开你的表格时，可能完全没有你需要的样子。

因此，自定义格式，学会上面那些基本就够了。

谢谢阅读，每天学一点，省下时间充实自己。欢迎点赞、评论、关注和点击头像。

有人问在Excel中，条件格式的图标集是固定的，没有自己想要的效果，想要指定的符号来代表数字的谈何情况，有没有解决的办法？

例如要在如下表格中，用▲代表增长，用红色的▼代表负增长。

我们可以用Excel自定义格式来解决，今天带给大家这个小技巧。选中C2:C8数据区域，组合键，选择“自定义”，在文本框中输入代码：▲* 0.00%;[红色]▼* 0.00%，点击确定。

完成后效果

代码解析：

▲* 0.00%;[红色]▼* 0.00%

;号前面是的正数的格式，后面的为负数的格式。

0.00%，代表保留两位小数的百分数。

*后面有一个空格，代表重复空格，直至内容充满列宽，在这里作用是为了让▲和▼居左对齐。

[红色]代表负数以红色表示。

Excel提供了大量的格式和样式，用于设置单元格数据格式，但仍然不能满足部分高级用户的需要，于是就需要使用自定义数字格式来处理问题，自定义格式有个突出的特点就是你看到不等于真实的，下面将分两期来开启全面的自定义格式之旅。

1.创建自定义格式

例如：单元格中内容显示为“张三”，要求将“张三”显示为“李四”。

操作步骤：选取”张三”的单元格，打开【单元格格式】对话框，选取【数字】选项卡，在【分类】列表框中选取【自定义】选项，在右侧的【类别】框中输入“;;;“李四”，数字120要显示为“李四”，可以直接在框中输入“李四”（可不含引号）。

2.自定义格式代码基本结构

人们可以指定一系列代码作为数字格式来创建自定义格式，如上面的例题，“;;;“李四””就是一组代码。代码分为四部分，中间用“;”号分隔，具体如下：

技巧：如果四部分不写代码只写分隔符，如;;;，那么将隐藏不显示所有的数据。

3.自定义格式常用的代码

代码是设置自定义格式必不可少的部分，主要有数字和文本代码，以及日期和时间代码两种。

1）数字和文本代码

“G/通用格式”：以常规的数字显示，相当于【分类】列表中的【常规】选项。如输入10.001就显示10.001.

“0”：数字占位符。如果单元格的内容大于占位符，则显示实际数字，如果小于占位符，则用0补位。如代码“000”时，12则显示为012.可用于序号的输入；

“#”：数字占位符。只显示有意义的零而不显示无意义的零。如代码为“###”显示12，而不是显示012，此处12前的0即为无意义的零。小数点后数字大于“#”的数量，则按“#”的位数四舍五入。

“？”：数字占位符。在小数点两边为无意义的零添加空格，以便当按固定宽度是小数点可对齐。

“，”：千位分割符号。数字使用千位分割符号。如果在代码中“，”后，则把原来的数字缩小1000倍。如设置代码：“0，”，1000则显示为1.

“文本”：显示双引号中的文本。

[颜色]：用指定的颜色显示字符。有八种颜色可选，红色、黑色、黄色、绿色、白色、蓝色、青色和洋红。

[条件]：可以先判断单元格内容，然后再设置格式。条件格式化只限于使用三个条件，其中两个条件是明确的，另一个是除了这两个条件外的其他情况，条件要放到方括号中。

如代码设置：“[>100]”“超额完成”；[=100]“基本完成”；“未完成””,意义为大于100显示“超额完成”，等于100显示为“基本完成”，其他的显示为“未完成”。

如当数字小于0时红色，0-100之间蓝色，大于100显示绿色；

2）日期和时间代码

下面是常用的日期和时间代码。

“YYYY”或“YY”：按四位（1900~9999）或两位（00~99）显示年。

“MM”或“M”：以两位（01~12）或（1~12）显示月

“DD”或“D”：以两位（01~31）或一位（1~31）来显示天

“H”或“HH”：以一位（0~23）或两位（01~23）来显示小时

“M”或“MM”：以一位（0~59）或（01~59）显示分钟

“S”或“SS”： 以一位（0~59）或（01~59）显示秒。

日期的年月日间的符号可以更换成自定义的符号，如 .和-等，yyyy-mm-dd,yyyy.mm.dd;

年月日部分的代码位数也对日期的显示格式有影响，当三位以上显示为英文；

输入代码aaaa可以将日期显示为星期形式。（待续）

编写自定义数字格式，不仅能增强报表的可读性，还能设置一些特殊的自定义格式，简化数据录入。下面介绍自定义数字格式的经典应用实例。

一、不显示零值

在【Excel选项】对话框中单击【高级】选项卡，取消勾选【在具有零值的单元格中显示零】的复选框，单击【确定】按钮，关闭对话框，可以不显示在当前工作表中出现的零值，如图1所示。

图1在【Excel选项】中取消显示零值

设置自定义数字格式，也可以实现相同的效果。

格式代码如下。

G/通用格式;G/通用格式 ;

代码解析：第1区段和第2区段中对正数和负数使用“G/通用格式”，即正常显示；在第3区段留空，即可使零值单元格显示为空白。

二、快速缩放数值

处理较大的数值时，往往希望按千位或万位来显示数值，以增加数据的可读性。使用用自定义数字格式，可以实现这样的快速缩放显示。

图2显示了以“百万”、“万”、“千”、“百”缩放数值的实例。

图2快速缩放数值

提示：自定义单元格数字格式仅改变数字的显示方式，而不改变单元格中数据的实际值，不用担心自定义后的数据不能再参与计算处理。

三、智能显示百分比

当单元格中的数字小于1时，数字按“百分比”格式显示，大于等于1的数字按标准格式显示，同时让所有数字按小数点位置排列整齐，如图3所示。

图3智能显示百分比

格式代码如下。

[1]0.00%;#.00_%

代码解析：第1区段使用了条件值判断，对应数值小于1时的格式为显示保留两位小数的百分比格式，第2个区段对应不小于1时的数字和文本的格式。第2个区段中，百分号前使用了一个下划线，目的是保留一个与百分号等宽的空格，使应用数字格式后的单元格数值能够按照小数点位置对齐。

注意：注意在单元格数值全部输入完成后再设置单元格数字格式，因为设置完此代码后，单元格将具有百分比样式，即输入其中的数值都将缩小100倍，例如输入12，则返回12%。但如果数字是由公式返回的，则不受此影响。

四、显示分数

Excel中内置了一些分数的数字格式，通过自定义可以得到更多的显示形式，比如在显示的时候加上“又”字，或加上表示单位的符号，或者使用一个任意的数字作为分母，如图4所示。

图4更多的分数显示形式

五、隐藏某些类型的数据

可以使用自定义数字格式隐藏某些类型的数据，或者把某些类型的数据用特定的字符串代替，如图5所示。

图5隐藏某些类型的数据

其中，代码“**”的含义是使用“*”来填充，类似地，代码“-”的含义是使用“-”填充，第一个*是功能字符，其后跟随的是填充字符。

注意：使用代码格式“；；；”时，可以隐藏单元格中的数值、文本内容，但如果单元格中的内容为错误值，如#N/A，则仍然被显示出来。

六、简化录入操作

在某些情况下，使用带条件判断的自定义格式可以简化录入操作。

（1）用数字1和0代替“ü”、“”的输入

格式代码如下。

[=1]” ü”;[=0]” ”;;

只要在单元格中输入1或0，设置了该格式代码的单元格就会相应地显示出“ü”、“”，而如果录入的数字不是1或0，就会显示空白。对于类似不方便录入的符号，可使用此方法。

（2）用数字代替复杂文本输入

格式代码如下。

“通过”;;”未通过”

当在单元格中输入大于零的数字时，显示“通过”，等于0时，显示“不通过”，小于零时，显示空。

（3）快速录入特定前（后）缀编码

当输入大量有规则的编码时，可设置相同的前（后）缀编码，进行数据录入是，只需要输入不同部分即可。

格式代码如下。

“14Q3”-0000

特定前缀为“14Q3”，后面为4为的流水号。设置完成后，只需要录入流水号，即可显示出全部内容。

（4）手机号码分段显示

格式代码如下。

000-0000-0000

该操作按大多数人读取手机号码的习惯进行分段显示。

图6位简化录入操作中自定义格式显示的效果图。

图6通过自定义数字格式简化录入操作

七、简化录入操作

大多数情况下，自定义数字格式应用于数值型数据的显示处理上，用户也可以对文本型数据进行自定义格式设置，如为文本型数据添加附加信息显示。

（1）简化文本输入

格式代码如下。

;;;”集团公司”@”部”

代码解析：格式代码分为4段，前3段区域禁止非文本数据显示，第4段为文本数据增加了附加信息。当某些录入操作有一定规律或是固定样式，就可以试着用此方法简化录入操作。

（2）文本数据右对齐

格式代码如下。

;;;* @

文本数据通常在单元格中左对齐，设置这样的格式可以在文本左边填充足够多的空格，使得文本内容显示为右对齐。

（3）预留手写文字位置

格式代码如下。

;;;@*_

此格式在文本内容的右侧填充下划线“_”，形成类似签字栏的效果，可用于一些需要打印后手动填写的文件。图7位文本内容附加显示中自定义格式的效果显示。

图7文本内容的附加显示

Excel自定义数字格式是一项非常灵活的操作，它能满足我们各种各样的数字变形的需求，但又不改变单元格实际值的形态，今天分享给大家八例经典应用。

自定义数字格式如何操作：按组合键，【自定义】，在文本框中输入代码。

1、以万元为单位显示

代码：0!.0,

2、电话号码分段

代码：000-0000-0000

3、输入1显示正确，输入0显示错误

代码：[=1]正确; [=0]错误;

4、显示数据增减情况

代码：▲* 0.00%;[红色]▼* 0.00%

5、判断成绩

60分以下为不及格，60分以上85分以下为及格，85分及以上为优秀。

代码：[<60]"不及格";[<85]"及格";"优秀";输入有误< p="">

6、给数字加单位

代码：#万元

7、统一给文本加内容

代码：宏意公司@

8、快速输入员工编号

代码："HY"0000

“自定义格式”允许用户创建符合一定规则的数字格式，应用自定义格式的数字并不会改变数值本身，只改变数值的显示方式。

在需要自定义格式的区域单击右键（Ctrl+1），点击“自定义”，在类型下的代码窗口输入代码，见下图。

自定义的格式代码具有以下结构：

正数;负数;零值;文本（分号为英文符号）

以分号隔开的四个区域组成一个完整的自定义格式代码，在每个区域的代码对相应的数值产生作用。

同时也可以用运算符的方式表示条件值，如>0,<0表示正数和负数，>60,<60表示成绩的及格与否等。

EXCEL还可以用颜色代码编写自定义格式，使其显示不同的颜色。

可供选择的颜色代码有：[黑色]、[白色]、[蓝色]、[红色]、[黄色]、[绿色]、[洋红]，颜色的两边必须加上英文方括号。

还可以直接用：[颜色1]、[颜色2]等颜色代码，颜色数值范围为1～65。实例五与实例四两种代码的效果完全一样。

这样我们就可以对实例二、实例三改变颜色了，这样是不是更直观了？

如果大于60的显示红色，小于60的显示蓝色，可以加入“>”、“<”等条件代码，见实例六。

在实际代码应用中，只能在前两个区域中使用“>”、“<”等条件代码，第三个以除此外作为条件值，见实例六最后一列。

利用条件格式4个区域的定义，在分号间不输入代码，用户可以对各区域内容如：正数、负数、零值或文本进行隐藏，见实例七。