Excel自定义格式详解（Excel custom format detailed）"G/ general format": regular numeric display, which is equivalent to the "general" option in the list of categories.Example: Code: G/ general format". 10 shows 10 and 10.1 shows 10.1.2, "#": digital placeholder. A meaningful zero without showing meaningless zero. The number of decimal numbers such as "more than #", according to the "#" number four to five homes.Example: Code: "###.##", 12.1 showed 12.10; 12.1263 shows: 12.133, 0, digital placeholders. If the content of the cell is greater than the placeholder, the actual number is displayed; if less than the number of point characters, the 0 is used to make up.Example: Code: "00000."". 1234567 shows 1234567 and 123 shows 00123Code: "0". 100.14 shows 100.140 and 1.1 shows 1.1004, @ @: text placeholder, if you use only a single @, the effect is to refer to the original text,To automatically add text in the input digital data, using a custom format: "text" @; to automatically add text before the input digital data, using a custom format: @ "text". The @ symbol position determines the location of the numeric dataentered by the Excel relative to the added text.If you use more than one @, you can repeat the text.Example: Code ";"; "group" @ Department "; financial display: Group Finance DepartmentCode;;; @ @ @ ", shown as: financial financial financial financial5, "*": repeat the next character, until full width.Example: "@*- code"." "ABC" is shown as"ABC-------------------""It can be used for simulation of password protection: "* * * * * *; code;; * *, 123 display: Government6, "" ": thousand separatorExample: "# code," ###, 12000 shows: 120007 \ \: displays the next character in this format. "Text" shows text inside double quotes.\ \: displays the next character. And "" "use the same, is to display the input text, and after the input will automatically change to double quotation mark expression.Code example: "RMB" #, ##0, "million", with the RMB, ##0, # \, \ million,Enter 1234567890 shows: RMB 1 billion 235 million"8"?" Digital placeholders. Add spaces to meaningless zero on both sides of the decimal point so that at the fixed width, the decimal point is aligned, and also for fractions that do not wait for long digitsExample: set cells in the format ",", ",", ",", ",", ",", and "," and the alignment results are as follows:Enter 12.1212 to display 12.12, 12.1219, color: use the specified color to display characters. There are eight colors: red, black, yellow, green, white, blue, cyan and magenta.Example: [cyan]; [red]; [yellow]; [blue]". The display is positive for cyan, negative for red, zero for yellow, and text for blue[color N]: the color is called in the palette, and N is an integer between 0~56.Example: code [color '3]'". The color displayed by the cell is third colors on the palette. ,10: conditions: you can determine the contents of the cell before setting the format. Conditional formatting is limited to three conditions, two of which are explicit and the other all". The conditions should be put in square brackets. A simplecomparison must be made.Example: Code "[>0]" positive number; [=0]; "zero"; negative number". Display results are cell values greater than zero, indicating positive numbers equal to 0, showing zero, less than zero, showing negative numbers".11,"!" Display""". Because quotes are symbols commonly used in code. Cannot be displayed with '' 'in cells""". To display, you must join in before"!"Example: "# code!"". "10" shows "10"""The code: "#!"!"". "10" shows "10""""12, time and date code, common date and time code"YYYY" or "YY": displays by four bits (1900~9999) or two bits (00~99)"MM" or "M": "two" (01~12) or "1~12" means the month."DD" or "D": "two" (01~31) or "one" (1-31) to represent the day.Example: Code "YYYY-MM-DD"". January 10, 2005 shows:"2005-01-10.""Excel cell custom format 1Code structure, code code is divided into four parts, the middle with "" "" "" "" ", as follows:Positive number format; negative format; zero format; text formatImplications of each parameter"G/ general format": regular numeric display, equivalent to the "general" option in the category list.Example: Code: G/ general format". 10 shows 10 and 10.1 shows 10.1."0": numeric placeholders. If the content of the cell is greater than the placeholder, the actual number is displayed; if less than the number of point characters, the 0 is used to make upExample: Code: 00000". 1234567 shows 1234567 and 123 shows 00123Code: 0". 100.14 shows 100.140 and 1.1 shows 1.100"#": digital placeholder. A meaningful zero without showing meaningless zero. Decimal numbers such as "# is greater than the number, then press the" # "number four to five homes.Example: Code: "###.##", 12.1 showed 12.10; 12.1263 shows: 12.13"?" Digital placeholders. Add spaces to meaningless zero on both sides of the decimal point so that at the fixed width, the decimal point is aligned, and also for fractions that do notwait for long digitsExample: the cells are set as ".", ",", ",", ",,", and "," the result is as follows:Example: set the cell custom format "# /". "1.25" is displayed as "1 1/4""":" decimal point. If double quotation marks are added, characters are added.Example: Code "0.#"". "11.23" is displayed as "11.2""%: percentage.Example: "#% code". "0.1" is displayed as "10%""":" thousand separator ". The digit uses the thousand bit delimiter. If in the code "," after "empty", then the original figure reduced by 1000 times.Example: Code: "#, ###". "10000" is displayed as "10000""The code: "#,". 10000 is shown as 10.The code: "#,,". "1000000" is displayed as "1""./ /: displays the next character. And "" "use the same, are obviously input text, and after the input will automatically change to double quotation mark expression. The different "/" is the text behind the display, and the double quotation is thetext that shows the middle of the double show.Example: Code "\ABC" or "ABC""". Both are displayed as "ABC"" "*": repeat the next character, until full width.Example: "@*- code". "ABC" is displayed as"ABC-------------------"""_" (underlined): leave one and the next character of the same width space "text": display text in double quotes.Example: Code "China" @ show "" Henan, China ""[color]: displays characters in the specified color. There are eight colors: red, black, yellow, green, white, blue, cyan and magenta.Example: [cyan]; [red]; [yellow]; [blue]". The display is positive for cyan, negative for red, yellow for zero, and for textThis is shown in blue[color N]: the color is called in the palette, and N is an integer between 0~56.Example: code [color '3]'". The color displayed by the cell is third colors on the palette.[condition]: you can set the format after you have judged thecontents of the cell. Conditional formatting is limited to three conditions, two of which are explicit and the other all". The conditions should be put in square brackets. A simple comparison must be made.Example: Code "[>0]" positive number; [=0]; zero";Negative numbers". Display results are cell values greater than zero, indicating positive numbers equal to 0, showing zero, less than zero, showing negative numbers"."!" Display""". Because quotes are symbols commonly used in code. Cannot be displayed with '' 'in cells""". To display, you must join in before"!"Example: "# code!"". "10" shows "10"""The code: "#!"!"". "10" displays the 10 "" "2.1.1.2" time and date code, the common date and time code"YYYY" or "YY": displays by four bits (1900~9999) or two bits (00~99)"MM" or "M": "two" (01~12) or "1~12" means the month."DD" or "D": "two" (01~31) or "one" (1-31) to represent the day.Example: Code "YYYY-MM-DD"". January 10, 2005 shows:"2005-01-10.""Code: "YY-M-D"". October 10, 2005 shows: "05-1-10."""AAAA": the date is shown as week."H" or "HH": one hour (0~23) or two bit (01~23) display hours "M" or "MM": a minute (0~59) or two bit (01~59) display minutes"S" or "SS": displays seconds with one bit (0~59) or two bit (01~59)Example: Code "HH:MM:SS"". "23:1:15" is displayed as"23:01:15""[H] or [M] or [SS]: displays an hour greater than 24 hours or shows greater than 60 minutes or seconds.Here are some examples of instances that are frequently encounteredShow 12345 to 1.2Code: 0,"Set 1000 yuan display, and four to five, retain two decimal requirements: show the "12345" as: 12.35Code: "#.00","Insert characters arbitrarily in numbers: set 20050512 to: 2005-05-12Code: "0000-00-00""Add characters before text or numbers: "Zhengzhou" is shown as "Zhengzhou, Henan.""Code: @ Henan @ @"Requirement: "1035010121" is displayed as "mobile phone number 1034010121""The code: "mobile phone number" @ "or" mobile phone number "#"Another date format display method"E": displays four bit years, version 2003 is valid."BBBB": four calendar year, i.e. in 543 BC for the calendar year, on 1900 after the effective date."MMM": abbreviation for English month."Mmmm": the full name of the English month."DDD": abbreviation for English week."Dddd": the full name of the week in english.About the display of special numbersChinese lowercase digital [DBNum1][$-804]G/ general formatExample: Code: [DBNum1][$-804]G/ general format". "1" is displayed as "one""Code: [DBNum1][$-804]G/ general format". "13" is displayed as "Thirteen""Chinese lowercase digital [DBNum2][$-804]G/ general formatExample: Code: [DBNum2][$-804]G/ general format". "1" is displayed as "one""Code: [DBNum2][$-804]G/ general format". "13" is shown as "one at three""Chinese lowercase digital [DBNum3][$-804]G/ general formatExample: Code: [DBNum3][$-804]G/ general format". "123" is displayed as "123""Special instructionsBecause of the specificity of the parameter, the custom parameter is also a keyword. If the function =TEXT ("A1", "b0000") will display an error. Because "B" is reserved keyword, in the custom format input "B", the system will automatically fill in "BB"". BB is the calendar year, i.e. in 543 BC for the calendar year, on 1900 after the effective date. "BBBB" is the four calendar year. To solve the =TEXT (A1, b0000) error problem, you need to define the function =TEXT (A1, ',' B ',' 0000 '). In a custom format, the definition is "B" 0000". Other keywords, such as: "d", "e".......Cell custom format 2Custom numeric formatting in ExcelAlthough Excel provides a digital format for users, but there are still many users because of special requirements of work and study, need to use some Excel digital format is not provided, then we need to use a custom numeric format function Excel to help realize the special requirements of users.First, the method of creating custom digital formats in Excel1, select the cell or cell area to format.2, click the cell menu in the format menu, and then click the numbers tab.3, in the category list, click customize.4, in the type box, edit the numeric format code to create the desired format.In Excel, custom numeric formats use the following expressions:The format of a negative number; the format of zero; the format of text#, ##0.00 [Red]-#, ##0.00 "TEXT" @ 0In the format code of the Excel custom digital format, the user can specify at most four nodes; each section is separated by a semicolon; the four sections define the positive, negative, zero sum text in the format in sequence. If the user specifies only two nodes in the expression, the first part is used to represent the positive number and the zero, and the second part is used to indicate negative numbers. If the user specifies only one section in the expression, all the numbers will use the format. If you want to skip a section in the expression, you can use only a semicolon for that section.Two, custom digital format instance(1) automatically add text(1) automatically add text after entering data; use the custom format as @ @ text content; automatically add text before entering data; use the custom format as: @ text content @ @. The @ symbol position determines the number of Excel input data relative to the added text. Double quotes must be in english.Example one:Add the word "two days (2) classes" before the student's name, and you can create it:"Two days (2) class" @(2) add text automatically after you enter the number.Example two:For example, in daily financial work, you often need to add a unit "Yuan" after the amount of money, and then you can use it:0 yuan"or"0 yuan"(two) use colors in custom digital formatsTo set the color of a part of the format, type the color name or color number with square brackets at the corresponding part of the section. Excel can use the color name [black], [] [], blue, cyan, magenta [green] [], [red], [white], [yellow] eight different colors, Excel also can use color X] to set the color, which is between 1-56 X the numbers represent 56 different colors.For example: when the user needs to be in the cells of negative numbers indicated by a blue, as long as the use of "#, ##0.00; [blue]-#, ##0.00 custom digital format, user input in a cell negative, digital Excel will be shown in blue.(three) use conditional formatting in custom digital formatsIn the Excel custom digital format, users can set conditional formatting. When the number in the cell satisfies the specified condition, the Excel automatically applies the conditionalformat to the cell. In the Excel custom digital format, you can use the following six standard comparison operators:Operator meaning= equal to> greater than< less than <> = greater than or equal to< = less than or equal to< > is not equal toFor example: in the student achievement worksheet,When we want to display in red font is greater than or equal to a score of 90, showed less than 60 score in blue font, the rest of the results are shown in black font, then only the custom numeric format is set to "][>=90] [[red; blue][<60]; [black]".It is worth noting that, when you need to continue to use the results just created a custom numeric format, you will find in the "cell format" "custom" types cannot be found in the "][>=90] [[red; blue][<60]; [black]" format, this is because you will automatically Excel create a "][>=90]][<60] [[red; blue" to "red][>=90]G/" format format; [[black blue][<60]G/ common format;]G/ universal format, you only need to select the formatyou can achieve the same effect.Example one:In the worker bonus list, when we want to show less than 50 "low", between 50 and 100 show "medium", greater than 100 shows "high"". You just need to set the custom digital format to:[<50] "low"; [>100] "high"; "medium""Example two:Replace "90" or "superior" in "Mathematics" or "Chinese"":[>=90] "excellent""(four) hide the value in the cellIn the Excel table, sometimes in order to form the appearance or other factors, we hope that the numerical cell in hidden, then we use ";;;" (three semicolon) custom digital format can achieve this purpose. In this way, the values in the cell appear only in the edit bar, and the values in the hidden cells are not printed yet, but the values in that cell can be referenced by other cells. In Excel, if you want to set the format that satisfies the specified number of conditions, you must add the condition with parentheses in the custom numeric format code. The conditions are composed of two parts: the comparison operator and the numeric value.。
![excel自定义格式[1]](https://img.taocdn.com/s1/m/65ae2c67f5335a8102d22087.png)
