The key to setting up a custom number format in Excel is to understand the structure and rules of the format code. The custom format consists of up to four parts, corresponding to positive numbers, negative numbers, zero values and text, separated by semicolons, such as: positive numbers format; negative numbers format; zero values format; text format. Common symbols include: 0 (forced display of numbers), # (display of significant numbers), (millionite separator), % (percentage), . (decimal point), and @ (representative text). The operation steps are: select the cell → press Ctrl 1 to open the format window → select "Customize" → enter the code → click OK. Note that the custom format only changes the display method and does not affect the actual numerical calculation. Practical examples include hiding zero values, displaying units (such as tens of thousands of yuan), adding text prefix/suffixes, setting phone number formats, etc. After mastering these, users can flexibly combine multiple display effects.

Setting up a custom number format in Excel is actually not difficult. The key is to understand the structure and rules of the format code. You only need to master a few basic symbols to display numbers, currency, date and other formats according to your needs.

Basic structure of custom digital formats

Excel's custom numeric format consists of up to four parts, corresponding to positive, negative, zero, and text. They are separated by semicolons, in the following format:

 Positive number format; negative number format; zero value format; text format

For example, this format code:

 #,##0.00;(#,##0.00);0.00;"Sales are"@

It says:

• Positive numbers are displayed as tensiles retaining two decimals
• Negative numbers are displayed with brackets
• Zero is displayed as 0.00
• Add "Sales Yes" before text

If you only write one part, such as 0.00 , then all values will apply this format uniformly.

Common symbols and usage methods

Here are some common symbols and their meanings:

• 0 : Forced display of numbers, not enough to make up for 0
• # : Display valid numbers, no 0
• , : the thousandth participle separator
• % : Percentage
• . : decimal point
• @ : Representative text content

For example:

• 0.00 → Displayed as two decimal places, such as 1 → 1.00
• #.## → Display up to two decimal places, such as 1.234 → 1.23
• #,##0 → Millite format, such as 1234567 → 1,234,567

If you want to display the numbers in percentage form, you can write it like this: 0% or 0.00% , the system will automatically multiply by 100 and add the % sign.

How to set a custom format

The operation steps are as follows:

1. Select the cell or region you want to format
2. Press Ctrl 1 to open the "Format Cells" window
3. Click the "Number" tab → select "Custom"
4. Enter your format code in the input box
5. Click "OK" to save the settings

Note: The custom format only changes the display method and will not affect the actual value. The formula calculation is still based on the original data.

Several practical examples

• Hide zero value : 0.00;-0.00;;@
  In this way, zeros will not be displayed, which is suitable for keeping them neat when making reports.

• Display unit (such as ten thousand yuan) : 0!.0,萬
  The actual value is 10,000, which will be displayed as 10,000.

• Add prefix/suffix text : "單價："0.00元
  Add "unit price:" before the number and "yuan" after the number, the display effect is similar: unit price: 12.34 yuan.

• Phone number format : (000) 000-0000
  Enter 1234567890 and will be displayed as (123) 456-7890

Basically that's it. After mastering these, you can combine various formats according to your specific needs. Although it looks a bit like code, once you get familiar with it, it is actually very fast to set it up.

The above is the detailed content of How to create a custom number format in Excel. For more information, please follow other related articles on the PHP Chinese website!