Select the cells you want to format.
Click Cells on the Format menu, and then click the Numbers tab.
In the category list, click a category with a similar format, and then set a built-in format similar to the desired format (you can create a copy to customize it without changing the built-in format).
In the category list, click Customize.
In the Type box, edit the number format code to create the desired format.
You can specify up to four parts in the format code. These format codes are separated by semicolons, and they define positive numbers, negative numbers and zero-sum text in the format in order. If only two parts are specified, the first part is used to represent positive numbers and zeros, and the second part is used to represent negative numbers. If only a part is specified, it can be used for all numbers. If you want to skip a part, just replace it with a semicolon.
You can use format codes to describe how to display numbers, dates or times, currencies, percentages or scientific counts, and text or spaces.
format code
Text and spaces
Display text and numbers To display both text and numbers in a cell, you can enclose text characters in double quotation marks ("") or put a backslash (\) before a single character. Characters should be placed in the corresponding part of the format code. For example, type the format "$0.00 remaining"; $-0.00 "Shortage" to show positive value "$ 125.74 surplus" and negative value "$- 125.74 shortage". The following characters do not need quotation marks: $,-,+,/,(),:,! 、^、&； ,' (left single quotation mark),' (right single quotation mark), ~, {}, =, greater than, = greater than or equal to,
[red] [<; = 100]; [blue] [> 100]
Apply conditional formatting to cells (Conditional formatting: When the specified condition is true, Excel automatically applies formatting to cells, such as cell shading or font color. (For example, color shading depends on cell values), use the Conditional Format command on the Format menu.
Currency, percentage and scientific counting
Currency Symbol To enter one of the following currency symbols in numeric format, first open Num Lock, and then use the numeric keypad to enter the ANSI code of the currency symbol.
To enter, hold down the Alt key and type this code.
0 162
0 163
0 165
0 128
The annotation custom format is saved in the workbook. To make Microsoft Excel always use a specific currency symbol, please change the selected currency symbol in Regional Settings in Control Panel before starting Excel.
Percent To display numbers in percentage format, include a percent sign (%) in the number format. For example, the number 0.08 means 8% and 2.8 means 280%.
Scientific notation To display numbers in scientific notation, please use the following index codes in the corresponding parts: "e-", "e+", "E-" or "E+". In the format code, if the right side of the index code contains zero (0) or number symbol (#), Excel will display the number according to the scientific counting method and insert e or e ... The number of codes with zero or number symbol on the right side determines the number of digits in the index. "e-" or "E-" will add a negative sign to the index. "e+" or "E+" adds a positive sign when the index is positive and a negative sign when the index is negative.
Date and time
Day, month and year If "M" is followed by "H" or "hh" code, or followed by "ss" code, Microsoft Excel will display minutes instead of months.
Use this code.
Displays the month as1–12m.
Displays the month as 01–12mm.
Displays the month as1-1February mmm.
Displays the month as1-1February mmmm.
Displays the month as the first letter mmmmm of the month.
Display the date as1–31day.
Display the date as 01–31DD.
Displays the date as Sunday–Saturday.
Displays the date as dddd from Sunday to Saturday.
Displays the year as 00–99 YY.
Displays the year as1900–9999yyyy.
Hours, minutes and seconds to use this code.
Displays hours as 0–23 hours.
Displays the hours as 00–23 hours.
Displays minutes as 0–59 minutes.
Displays minutes as 00–59mm.
Displays seconds as 0–59 seconds.
Displays seconds as 00–59 seconds.
Make the hour display similar to 4 am am am/pm.
Make the time display similar to 4:36 PM h:mm AM/PM.
Make the time display similar to 4 4: 36: 03 p h: mm: SSA/p/p.
Displays the running time in hours, such as 25.02 [h]: mm
Displays the running time in minutes, for example, 63:46 [mm]:ss.
Displays the running time [ss] in seconds.
Fraction of second h: mm: ss.00.
AM and PM If the format contains "AM" or "PM", it will be calculated in 12 hour clock mode, where "AM" or "A" means the time from midnight to noon, "PM" or "P" means the time from noon to midnight, and if the format does not contain "AM" or "PM", it will be calculated in 24 hours. "M" or "mm" code must be followed by "H" or "hh" code or "ss" code; Otherwise, Microsoft Excel will display the month instead of the minute.
You can use&to make comments; (and symbol) Text operator (operator: A flag or symbol that specifies the type of calculation performed in an expression. There are mathematics, comparison, logic and reference operators. ) concatenates two values.