Excel单元格自定义格式技巧总结

第⼀部分

Excel 中的单元格格式是⼀个最基本但是⼜很⾼级的技能，说它基本是因为我们⼏乎天天都会⽤到它，会⽤它来设置⼀些简单的格式，⽐如⽇期，⽂本等等；⾼级是因为利⽤ Excel 单元格的⾃定义格式我们可以实现⼀些看起来⾮常神奇和有⽤的效果。下⾯我们就由浅⼊深的来介绍⼀下 Excel 单元格⾃定义格式的知识和技巧。

1、\"G/通⽤格式\"

以常规的数字显⽰,相当于\"分类\"列表中的\"常规\"选项。代码：G/通⽤格式。10 显⽰为 10；10.1 显⽰为 10.1

2、 \"#\"：数字占位符

只显有意义的零⽽不显⽰⽆意义的零。⼩数点后数字如⼤于\"#\"的数量,则按\"#\"的位数四舍五⼊。代码：###.##。12.1 显⽰为 12.1, 12.1263 显⽰为 12.13

3、\"0\"：数字占位符

如果单元格的内容⼤于占位符,则显⽰实际数字,如果⼩于点位符的数量,则⽤0补⾜。例如代码：00000。1234567 显⽰为 1234567, 123 显⽰为 00123代码：00.000。100.14 显⽰为 100.140, 1.1 显⽰为 01.100

4、\"@\"：⽂本占位符

如果只使⽤单个@, 作⽤是引⽤原始⽂本, 要在输⼊数字数据之后⾃动添加⽂本, 使⽤⾃定义格式为：\"⽂本内容\"@；要在输⼊数字数据之前⾃动添加⽂本, 使⽤⾃定义格式为：@\"⽂本内容\"。@符号的位置决定了Excel输⼊的数字数据相对于添加⽂本的位置。如果使⽤多个@, 则可以重复⽂本。

代码: ;;;\"集团\"@\"部\"。 财务 显⽰为 集团财务部代码: ;;;@@@。 财务 显⽰为 财务财务财务

5、\"*\"：重复下⼀次字符,直到充满列宽

代码：@*-。ABC 显⽰为 ABC-------------------可就⽤于仿真密码保护：代码 **;**;**;** 。123 显⽰为 ************

6、\：千位分隔符

代码 #,### 。12000 显⽰为 12,000；

“,”后省略000或###会省略千位符后⾯的数字，连续省略两个千位符，会省略百万位数之后的数字。如代码0,,百万，1000000显⽰为1百万

7、\"\\\": ⽤⽂本格式显⽰接下来的字符

\"⽂本\"：显⽰双引号⾥⾯的⽂本；\"\\\"：⽤⽂本格式显⽰接下来的字符。和 \"\" ⽤途相同都是显⽰输⼊的⽂本, 且输⼊后会⾃动转变为双引号表达。可⽤空格打断。

代码 \"⼈民币\"#,##0,,\"百万\" 与 \\⼈民币 #,##0,,\\百万。输⼊ 1234567890 显⽰为 ⼈民币 1,235百万

8、\"?\"：数字占位符

在⼩数点两边为⽆意义的零添加空格,以便当按固定宽度时,⼩数点可对齐,另外还⽤于对不等到长数字的分数代码: .。输⼊ 12.1212 显⽰ 12.121; 输⼊ 12.12 显⽰ 12.12

9、颜⾊：⽤指定的颜⾊显⽰字符

可有⼋种颜⾊可选：红⾊、⿊⾊、黄⾊、绿⾊、⽩⾊、蓝⾊、青⾊和洋红。

代码：[蓝⾊];[红⾊];[黄⾊];[绿⾊]。显⽰结果为正数为蓝⾊, 负数显⽰红⾊, 零显⽰黄⾊, ⽂本则显⽰为绿⾊[颜⾊N]：是调⽤调⾊板中颜⾊, N 是 0~56 之间的整数。

代码：[颜⾊1];[颜⾊2];[颜⾊3];[颜⾊4]。显⽰结果为正数为调⾊板上第1种颜⾊, 负数为调⾊板上第2种颜⾊, 零为调⾊板上第3种颜⾊, ⽂本为调⾊板上第4种颜⾊。

10、条件：可以单元格内容判断后再设置格式

条件格式化只限于使⽤三个条件, 其中两个条件是明确的, 另个是\"所有的其他\"。条件要放到⽅括号中。必须进⾏简单的⽐较。代码: [>5]\"⼤于五\";[<-5]\"⼩于负五\";\"零\"。输⼊ 12, -12, 1 显⽰为 ⼤于五, ⼩于五, 零。

11、 \"!\"：显⽰ \"

由于引号是代码常⽤的符号。在单元格中是⽆法⽤ \" 来显⽰出来 \"。要想显⽰出来,须在前加⼊ !代码：#!\"!\"。10 显⽰ 10\"\"

12、时间和⽇期代码常⽤⽇期和时间代码

\"YYYY\"或\"YY\"：按四位（1900~9999）或两位（00~99）显⽰年\"MM\"或\"M\"：以两位（01~12）或⼀位（1~12）表⽰⽉。\"DD\"或\"D\"：以两位（01~31）或⼀位（1-31）来表⽰天。例：代码：YYYY-MM-DD。2014年8⽉18⽇ 显⽰为 2014-08-18第⼀部分总结：ü 数字占位符：

⽤空格补⾜未有的数字 .0 ⽤0补⾜格式 00.000

# 不产⽣实际格式没有的数字 ##.###ü 特殊记忆：；；；隐藏

如；；；@@@，隐藏原⽂本，显⽰三个原⽂本

如；；；**，隐藏原⽂本，⽤*重复⾄填完表格（⽤于仿真密码跋扈）

第⼆部分

⼀、另外⽇期格式的显⽰⽅法

E: 显⽰四位年份，2003版本有效。

BBBB: 显⽰四位佛历年份，即以公元前543年为纪年元年，对1900年以后的⽇期有效。MMM: 显⽰英⽂⽉份的简称。MMMM: 显⽰英⽂⽉份的全称。DDD: 显⽰英⽂星期⼏的简称。DDDD: 显⽰英⽂星期⼏的全称。

Monday DDDD；Mon DDD；November MMM；Nov MMM

⼆、关于特殊数字的显⽰

1、中⽂⼩写数字： [DBNum1][$-804]G/通⽤格式

代码：[DBNum1][$-804]G/通⽤格式。13 显⽰为 ⼀⼗三2、中⽂⼤写数字: [DBNum2][$-804]G/通⽤格式代码：[DBNum2][$-804]G/通⽤格式。13 显⽰为 壹拾叁3、中⽂⼩写数字 [DBNum3][$-804]G/通⽤格式代码：[DBNum3][$-804]G/通⽤格式。13 显⽰为 １⼗３

三、在⾃定义数字格式中使⽤颜⾊

要设置格式中某⼀部分的颜⾊，只要在该部分对应位置⽤⽅括号键⼊颜⾊名称或颜⾊编号即可。Excel中可以使⽤的颜⾊名称有 [⿊⾊]、[蓝⾊]、[青⾊]、[绿⾊]、[洋红]、[红⾊]、[⽩⾊]、[黄⾊] ⼋种不同的颜⾊，此外Excel还可以使⽤[颜⾊X]的⽅式来设置颜⾊，其中X为1-56之间的数字，代表了56种不同的颜⾊。

例如：当⽤户需要将单元格中的负数数字⽤蓝⾊来表⽰，只要使⽤ #,##0.00;[蓝⾊]-#,##0.00⾃定义数字格式，⽤户在单元格中录⼊负数时，Excel就会将数字以蓝⾊显⽰。

四、在⾃定义数字格式中使⽤条件格式

在Excel⾃定义数字格式中⽤户可以进⾏条件格式的设置。当单元格中数字满⾜指定的条件时，Excel可以⾃动将条件格式应⽤于单元格。Excel⾃定义数字格式中可以使⽤如下六种标准的⽐较运算符：=、> 、<、>= 、<= 、<>，分别表⽰等于、⼤于、⼩于、⼤于等于、⼩于等于和不等于

例如：在学⽣成绩⼯作表中，当我们想以红⾊字体显⽰⼤于等于90分的成绩，以蓝⾊字体显⽰⼩于60分的成绩时，其余的成绩则以⿊⾊字体显⽰，这时只需将⾃定义数字格式设置为 [红⾊][>=90];[蓝⾊][<60];[⿊⾊] 即可。

值得注意的是，当你在以后需要继续使⽤刚才所创建的成绩条件⾃定义数字格式时，你会发现在“单元格格式”的“⾃定义”分类类型中找不到[红⾊][>=90];[蓝⾊][<60];[⿊⾊] 格式，这是因为Excel⾃动将你所创建的 [红⾊][>=90];[蓝⾊][<60] 格式修改成 [红⾊][>=90]G/通⽤格式;[蓝⾊][<60]G/通⽤格式;[⿊⾊]G/通⽤格式，你只需选择此格式即可达到同样的使⽤效果。

实例：在⼯⼈奖⾦列表中，当我们想以⼩于50的显⽰“低”，50⾄100间显⽰“中等”，⼤于100的显⽰“⾼”。这时只需将⾃定义数字格式设置为代码：[<50]\"低\";[>100]\"⾼\";\"中等\"

五、隐藏单元格中的数值

在Excel⼯作表中，有时为了表格的美观或者别的因素，我们希望将单元格中的数值隐藏起来，这时我们使⽤ ;;; (三个分号)的⾃定义数字格式就可达到此⽬的。这样单元格中的值只会在编辑栏出现，并且被隐藏单元格中的数值还不会被打印出来,但是该单元格中的数值可以被其他单元格正常引⽤。特殊说明

因为参数的特殊性，所以⾃定义的参数也是有关键字的。如函数 =TEXT(A1,\"b0000\") 就会显⽰错误。因为 b 就是保留的关键字，在⾃定义格式输⼊ b 系统就会⾃动填⼊ bb 。bb 就是佛历年份，即以公元前543年为纪年元年，对1900年以后的⽇期有效。bbbb 就是四位佛历年份。要解决 =TEXT(A1,\"b0000\") 的错误问题，需要这样定义函数 =TEXT(A1,\"\"\"b\"\"0000\")。在⾃定义格式中定义就是 \"b\"0000。其它的关键字⾃⼰体会如： d、e............