Create or delete a custom number format

Excel provides many options for displaying numbers as percentages, currency, dates, and so on. If these built-in formats don't meet your needs, you can customize a built-in number format to create your own. To learn more about how to change number format codes, you may want to review the guidelines for customizing a number format.

What do you want to do?

Create a custom number format

  1. Open the workbook in which you want to create and store a custom number format.

  2. On the Home tab, click the Dialog Box Launcher in the lower-right corner of the Number group.

    Dialog Box Launcher in Number group

  3. In the Category box, click Custom.

  4. In the Type list, select the number format that you want to customize.

    The number format that you select appears in the Type box above the Type list.

    Note: When you select a built-in number format in the Type list, Excel creates a copy of that number format that you can then customize. The original number format in the Type list cannot be changed or deleted.

  5. In the Type box, make the necessary changes to the selected number format.

    Tip: For more information about the changes that you can make, see Customize a number format later in this article.

Note: A custom number format is stored in the workbook in which it was created and will not be available in any other workbooks. To use a custom format in a new workbook, you can save the current workbook as an Excel template that you can use as the basis for the new workbook.

Top of Page

Delete a custom number format

  1. Open the workbook that contains the custom number format that you want to delete.

  2. On the Home tab, click the Dialog Box Launcher in the lower-right corner of the Number group.

    Dialog Box Launcher in Number group

  3. In the Category box, click Custom.

  4. In the Type list, select the custom number format that you want to delete.

    Note: Built-in number formats in the Type list cannot be deleted.

  5. Click Delete.

    Note: Any cells in the workbook that were formatted with the deleted custom format will be displayed in the default General format.

Top of Page

Customize a number format

To create a custom number format, you begin by selecting one of the built-in number formats. You can then change any one of the code sections of that format to create your own custom number format.

A number format can have up to four sections of code, separated by semicolons. These code sections define the format for positive numbers, negative numbers, zero values, and text, in that order.

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

For example, you can use these code sections to create the following custom format:

[Blue]#,##0.00_);[Red](#,##0.00);0.00;"sales "@

With that custom format, if you entered the values of 12.34, 1234567, -12.34, and -1234567, they would appear as shown below. The phrase "Text Input" is included for example purposes only to indicate what would happen if you entered text. In the example custom format string, “sales “@ means that any text you enter will be preceded by the word “sales “.

Shows how the values entered using the example specified custom number format render in a column in an Excel spreadsheet.

You don't have to include all code sections in your custom number format. If you specify only two code sections for your custom number format, the first section is used for positive numbers and zeros, and the second section is used for negative numbers. If you specify only one code section, it is used for all numbers. If you want to skip a code section and include a code section that follows it, you must include the ending semicolon for the section that you skip.

The following guidelines explain how to customize the number format code sections.

Include text and add spacing

  • Display both text and numbers To display text and numbers in a cell, enclose the text characters in double quotation marks (" ") or precede a single character with a backslash (\). Include the characters in the appropriate section of the format codes.

    For example, type the format $0.00" Surplus";$-0.00" Shortage" to display a positive amount as "$125.74 Surplus" and a negative amount as "$-125.74 Shortage." Note that there is one space character before both "Surplus" and "Shortage" in each code section.

    The following characters are displayed without the use of quotation marks.

    Character

    Description

    $

    Dollar sign

    +

    Plus sign

    (

    Left parenthesis

    )

    Right parenthesis

    :

    Colon

    ^

    Circumflex accent (caret)

    '

    Apostrophe

    {

    Left curly bracket

    }

    Right curly bracket

    <

    Less-than sign

    >

    Greater-than sign

    =

    Equal sign

    -

    Minus sign

    /

    Slash mark

    !

    Exclamation point

    &

    Ampersand

    ~

    Tilde

    Space character

  • Include a section for text entry If included, a text section is always the last section in the number format. Include an "at" character (@) in the section where you want to display any text that you type in the cell. If the @ character is omitted from the text section, text that you type will not be displayed.

    If you want to always display specific text characters with the typed text, enclose the additional text in double quotation marks (" "). For example, "gross receipts for "@

    If the format does not include a text section, any non-numeric value that you type in a cell with that format applied is not affected by the format. In addition, the entire cell is converted to text.

  • Add a space To create a space that is the width of a character in a number format, include an underscore character (_), followed by the character that you want to use.

    For example, when you follow an underscore with a right parenthesis, such as _), positive numbers line up correctly with negative numbers that are enclosed in parentheses.

  • Repeat a character To repeat the next character in the format to fill the column width, include an asterisk (*) in the number format.

    For example, type 0*- to include enough dashes after a number to fill the cell, or type *0 before any format to include leading zeros.

Top of Page

Use decimal places, spaces, colors, and conditions

  • Include decimal places and significant digits To format fractions or numbers that contain decimal points, include the following digit placeholders, decimal points, and thousand separators in a section.

    In the following table, "insignificant zeros" refers to a number that has fewer digits than there are zeros in the format.

    To display

    Use this placeholder

    Insignificant zeros

    For example, if you type 8.9, and you want it to display as 8.90, use the format #.00.

    0 (zero)

    Insignificant zeros

    Doesn't display extra zeros when the number that you type has fewer digits on either side of the decimal than there are # symbols in the format.

    For example, if the custom format is #.##, and you type 8.9 in the cell, the number 8.9 is displayed.

    #

    Insignificant zeros

    Adds a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column.

    For example, the custom format 0.0? aligns the decimal points for the numbers 8.9 and 88.99 in a column.

    ?

    The decimal point in a number.

    . (period)

If a number has more digits to the right of the decimal point than there are placeholders in the format, the number rounds to as many decimal places as there are placeholders. If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed. If the format contains only number signs (#) to the left of the decimal point, numbers less than 1 begin with a decimal point; for example, .47.

To display

As

Use this code

1234.59

1234.6

####.#

8.9

8.900

#.000

.631

0.6

0.#

12
1234.568   

12.0
1234.57

#.0#

44.398
102.65
2.8

  44.398
102.65
    2.8
(with aligned decimals)

???.???

5.25
5.3

5 1/4
5 3/10
(with aligned fractions)

# ???/???

  • Display a thousands separator To display a comma as a thousands separator, or to scale a number by a multiple of 1,000, include a comma (,) in the number format.

    Excel separates thousands by commas if the format contains a comma enclosed by number signs (#) or by zeros. A comma that follows a digit placeholder scales the number by 1,000.

    For example, if the format is #.0,, and you type 12,200,000 in the cell, the number 12.200.0 is displayed.

    To display

    As

    Use this code

    12000

    12,000

    #,###

    12000

    12

    #,

    12200000

    12.2

    0.0,,

  • Specify a color To specify the color for a section of the format, type the name of one of the following eight colors, enclosed in square brackets, in the section:

    [Black], [Green], [White], [Blue], [Magenta], [Yellow], [Cyan], [Red}

    The color code must be the first item in the section.

  • Specify a condition To specify number formats that will be applied only if a number meets a condition that you specify, enclose the condition in square brackets. The condition consists of a comparison operator and a value.

    For example, the following format displays numbers that are less than or equal to 100 in a red font and numbers that are greater than 100 in a blue font.

    [Red][<=100] ;[Blue][>100]

    To apply conditional formats to cells (for example, color shading that depends on the value of a cell), on the Home tab, in the Styles group, click Conditional Formatting.

Top of Page

Use currency, percentages, and scientific notation format

  • Include a currency symbol To type one of the following currency symbols in a number format, press Num Lock and use the numeric keypad to type the ANSI code for the symbol.

    To display

    Use this code

    ¢

    Alt+0162

    £

    Alt+0163

    ¥

    Alt+0165

    euro

    Alt+0128

    Note: Custom formats are saved with the workbook. To have Excel always use a specific currency symbol, you must change the currency symbol that is selected in the Regional Options in Control Panel before you start Excel.

  • Display percentages To display numbers as a percentage of 100,  include the percent sign (%) in the number format.

    For example, include the percent sign to display .08 as 8% or 2.8 as 280%.

  • Display scientific notation Use the following exponent codes in a section: E (E-, E+, e-, e+)

    Excel displays a number to the right of the "E" or "e" that corresponds to the number of places that the decimal point was moved.

    For example, if the format is 0.00E+00, and you type 12,200,000 in the cell, the number 1.22E+07 is displayed. If you change the number format to #0.0E+0, the number 12.2E+6 is displayed.

Use date and time formats

  • Display days, months, and years Use the following codes in a section.

    To display

    As

    Use this code

    Month

    A number without a leading zero

    m

    Month

    A number with a leading zero

    mm

    Month

    An abbreviation (Jan to Dec)

    mmm

    Month

    A complete word (January to December)

    mmmm

    Month

    A single letter (J to D)

    mmmmm

    Day

    A number without a leading zero

    d

    Day

    A number with a leading zero

    dd

    Day

    An abbreviation (Sun to Sat)

    ddd

    Day

    A complete word (Sunday to Saturday)

    dddd

    Year

    A two-digit number

    yy

    Year

    A four-digit number

    yyyy

    To display

    As

    Use this code

    Months

    1–12

    m

    Months

    01–12

    mm

    Months

    Jan–Dec

    mmm

    Months

    January–December

    mmmm

    Months

    J–D

    mmmmm

    Days

    1–31

    d

    Days

    01–31

    dd

    Days

    Sun–Sat

    ddd

    Days

    Sunday–Saturday

    dddd

    Years

    00–99

    yy

    Years

    1900–9999

    yyyy

  • Display hours, minutes, and secondsUse the following codes in a section.

    To display

    As

    Use this code

    Hour

    A number without a leading zero

    h

    Hour

    A number with a leading zero

    If the format contains AM or PM, the hour is based on the 12-hour clock. Otherwise, the hour is based on the 24-hour clock.

    hh

    Hour

    As if using a 12-hour clock

    Excel displays AM, am, A, or a for times from midnight until noon. It displays PM, pm, P, or p for times from noon until midnight.

    AM/PM, am/pm, A/P, a/p

    Minute

    A number without a leading zero

    Note: The m or mm code must appear immediately after the h or hh code or immediately before the ss code. Otherwise, Excel displays the month instead of minutes.

    m

    Minute

    A number with a leading zero

    Note: The m or mm code must appear immediately after the h or hh code or immediately before the ss code. Otherwise, Excel displays the month instead of minutes.

    mm

    Second

    A number without a leading zero

    s

    Second

    A number with a leading zero

    If you want to display fractions of a second, use a number format like h:mm:ss.00.

    ss

    Elapsed time

    In seconds

    If you're working with a formula that returns a time in which the number of seconds exceeds 60, use a number format like [ss].

    [s]

    Elapsed time

    In minutes

    If you're working with a formula that returns a time in which the number of minutes exceeds 60, use a number format like [mm]:ss.

    [m]

    Elapsed time

    In hours

    If you're working with a formula that returns a time in which the number of hours exceeds 24, use a number format like [h]:mm:ss.

    [h]

    To display

    As

    Use this code

    Hours

    0–23

    h

    Hours

    00–23

    hh

    Minutes

    0–59

    m

    Minutes

    00–59

    mm

    Seconds

    0–59

    s

    Seconds

    00–59

    ss

    Time

    4 AM

    h AM/PM

    Time

    4:36 PM

    h:mm AM/PM

    Time

    4:36:03 P

    h:mm:ss A/P

    Time

    4:36:03.75

    h:mm:ss.00

    Elapsed time (hours and minutes)

    1:02

    [h]:mm

    Elapsed time (minutes and seconds)

    62:16

    [mm]:ss

    Elapsed time (seconds and hundredths)

    3735.80

    [ss].00

Top of Page

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.

See Also

Split text into different cells with Data > Text to Columns

Split text into different cells with functions

Overview of formulas in Excel

How to avoid broken formulas

Use error checking to detect errors in formulas

Keyboard shortcuts in Excel 2016 for Windows

Excel functions (alphabetical)

Excel functions (by category)

Was this information helpful?

How can we improve it?

How can we improve it?

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!