Although Excel provides users with a large number of digital format, but there are still many users because of work, the special requirements of learning, you need to use some Excel did not provide a digital format, then we need to use custom number formats of Excel function to help realize the special requirements of users,
, a way to create a custom number formats in Excel
1, choose to set up the format of the cell or cell area,
2, click on the "cell" within the "format" menu command, and then click the "digital" TAB,
3, in the "category" list, click the "custom" option,
4, in the "type" box, edit digital format code to create the required format,
Second, custom formatting a:
"G/common format" : in the usual figures show that the equivalent of "classification" "regular" option on the list,
Example: code: "G/common format," 10 shows for 10; Display is 10.1, 10.1
2, "#" : digital placeholder, only show the zero of meaningful and not meaningless zero, digital decimal places such as greater than the number of "#", then press "#" digit rounded,
Example: code: "# # # # #", display of 12.10 12.1; 12.1263 is shown as: 12.13
3, "0" : digital placeholder, if the content of the cell is greater than the placeholder, according to the actual number, if less than the number of point operator, with 0 complement,
Example: code: "00000", 1234567 shows for 1234567; 123 is shown as 00123
Display code: "00.000", 100.14 to 100.140; 1.1 according to 01.100
4, "@" : text placeholder, if only use a single @, refers to the original text,
Want to automatically add text, after input digital data using a custom format for: "text" @; Before in the input digital data automatically add text, use the custom format for: @ "text", the location of the @ symbol determines the number of Excel input data relative to add the location of the text,
If you use multiple @, can repeat the text,
Example: "high school grade @" code, enter "a" is shown as: high school grade one
@ @ @ "code", input: finance, shown as: financial financial
5, "*" : repeat the next character, until the column width,
Example: code: "@ * -", "ABC" input is shown as "ABC -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -"
Password protection can be used in the simulation: code "* *; * *; * *; ", 123 is shown as: * * * * * * * * * * * * * *
6, ", ": one thousand separator
Example: the code "# # # #", 12000 is shown as: 12000
7, \ : use this format shows next character, "text", display of the text within double quotes,
"" : show the next character, and "" "" USES the same text input is displayed, and input will be automatically converted into the double quotes express,
Example: code "renminbi" # # # 0,, "million", and \ RMB #, # # 0,, \ millions,
Input is shown as: 1234567890 yuan 1.235 billion
8, "?" : digital placeholder, for meaningless zero in on both sides of the decimal point to add Spaces, so that when a fixed width, the decimal point can be aligned, also used for long range to digital score
Example: set the cell format for "?????? .?? "And"??? .????? ", align the results are as follows:
Enter 12.1212 shows 12.12 12.121
9, color: with specified color display character, can have eight kinds of color can choose: red, black, yellow, green, white, blue, cyan and magenta,
Example: code: "[blue]; [red]; (yellow), [blue] ", shows the result is positive for cyan, negative shows red, zero shows yellow, text is displayed as blue
[N] color: is to call the palette color, N is an integer between 0 ~ 56,
Example: code: "color [3]," cell showed on the color palette of 3 kinds of color,,
10, the condition: judgment can cell contents and then set the format, conditional formatting to use three conditions, two of these conditions is clear, the other is "all of the other," conditions in square brackets, must carry out simple comparison,
Example: code: "[& gt; 0]" positive "; [=0];" Zero "; Negative ", shows the result is to show positive cell number greater than zero, equal to 0 shows zero, less than zero shows "negative",
11, "!" : according to "", because the quotation is commonly used code symbol, is unable to use within the cell" "" "" to show ", if you want to display, to be in before joining "!"
Example: code: "#!" ", "10" display "10" "
Code: "#!"! ""," 10 "display" 10 "" "
12, the time and date code commonly used date and time
"YYYY" or "YY" : according to the four (1900 ~ 9999) or two (00-99) shows that year
"MM" or "M" : the two (01 ~ 12) or a (1 ~ 12) months,
"DD" or "D" : the two (01 ~ 31) or a (1-31) to represent a day,
"AAAA" : the date shown for weeks,
"H" or "HH" : in a (0 ~ 23) or two (01 ~ 23) shows hours
"M" or "MM:" to a (0 ~ 59) or two (01 ~ 59) minutes
"S" or "SS" : as a (0 ~ 59) or two (01 ~ 59) display second
Example: code: "HH: MM: SS", "23:1:15" is shown as "23:01:15
"[H] or [M] or [SS] : display greater than 24 hours of hours or more than 60 minutes or seconds,
Example: code: "YYYY - MM - DD", January 10, 2005 is shown as: "the 2005-01-10
"Another date format of displaying methods of
"E" : according to the four years, the 2003 version,
Four "BBBB" : display a calendar year, namely in the first year for the calendar in 543 BC, 1900 years after the date of the effective,
"MMM" : short for display in English,
"MMMM" : display in English, the full name of the
Short for "DDD" : according to the English week,
The full name of "DDDD" : according to the English week,
13, "%", percentage,
Example: the code "# %", "0.1" is shown as "10%"
14, about the special digital display
Chinese small Numbers [DBNum1] [$804] G/common format
Example: code: "[DBNum1] [$804] G/common format", "1" is shown as "a"
Code: "[DBNum1] [$804] G/common format", "13" is shown as "13
"Chinese small Numbers [DBNum2] [$804] G/common format
Example: code: "[DBNum2] [$804] G/common format", "1" is shown as "one"
Code: "[DBNum2] [$804] G/common format", "13" is shown as "one pick up 3
"Chinese small Numbers [DBNum3] [$804] G/common format
Example: code: "[DBNum3] [$804] G/common format", "123" is shown as "120"
Special description
Because of the particularity of the parameters, so the parameters of the custom is a keyword, such as function=TEXT (A1, "b0000") will display an error, because the "b" is a reserved keyword, in a custom format input "b" system will automatically fill in the "bb", bb is a calendar year, namely in the first year for the calendar in 543 BC, in 1900, after the effective date, "BBBB" is four years, be to solve=TEXT (A1, "b0000") error problem, need to define functions=TEXT (A1, "" "b ", "0000"), is defined in a custom format "" b" 0000 ", the other their experience of key word such as: "d", "e"...
Three, use conditional formatting in the custom number formats
Users in the Excel custom number formats can be conditional formatting Settings, when cell number to meet the specified conditions, Excel automatically apply conditional formatting to cell, Excel custom number formats can be used in the following six standard comparison operators:
Meaning operator
=equal to
Greater than
<
=is greater than or equal to
<=less than or equal to
<> Is not the same as
In student achievement in the worksheet, for example: when we want to show with red font is greater than or equal to 90 points, with blue font shows less than 60 minutes, the rest of the results are displayed in black font, then only need to set a custom number formats to "[red] [& gt;=90]. [blue] [60] <; [black] can,
"It is important to note that when you need to continue to use performance conditions custom number formats created just now, you will find that in "cell format" "custom" classification type can't find "[red] [& gt;=90]. [blue] [60] <; (black) "format, this is because Excel automatically created you" [red] [& gt;=90]. [blue] [60] <"format is modified into" [[red] [& gt;=90] G/general format; [blue] [60]
At the worker's bonus list, when we wanted to show "low", in less than 50 showed "moderate" between 50 to 100, more than 100 shows that "high", then only need to set a custom number formats to:
[50] <"low"; [100] & gt; "high";" Medium "
Example 2:
To make the "mathematics", "Chinese" scores more than 90 points in replacing "optimal" :
[& gt;=90] "optimal"
Four, hide the value in cell
In the Excel worksheets, sometimes in order to form beautiful, or other factors, we want to hide the value in the cell, then we use ";;; "(semicolon) three custom number formats can achieve this purpose, so that the values in the cell will only appear in the edit field, and hidden the value in the cell can't be printed, but the value in the cell can be referenced by other cells to normal, in Excel the digital format, if you want to set the specified conditions in custom number formats code must join with the conditions of the brackets, conditions by comparison operator and value of two parts,