Number format codes - Microsoft Support (2023)

You can use number formats to change the appearance of numbers, including dates and times, without changing the actual number. The number format does not affect the cell value that Excel uses to perform calculations. The actual value is displayed in the formula bar.

Excel provides several built-in number formats. You can use these built-in formats as is, or you can use them as a basis for creating your own custom number formats. When you create custom number formats, you can specify up to four sections of format code. These sections of code define the formats for positive numbers, negative numbers, zero values, and text, in that order. The sections of code must be separated by semicolons (;).

The following example shows the four types of format code sections.

Number format codes - Microsoft Support (1)

Number format codes - Microsoft Support (2)Format for positive numbers

Number format codes - Microsoft Support (3)Format for negative numbers

Number format codes - Microsoft Support (4)Format for zeros

Number format codes - Microsoft Support (5)Format for text

If you specify only one section of format code, the code in that section is used for all numbers. If you specify two sections of format code, the first section of code is used for positive numbers and zeros, and the second section of code is used for negative numbers. When you skip code sections in your number format, you must include a semicolon for each of the missing sections of code. You can use the ampersand (&) text operator to join, or concatenate, two values.

Create a custom format code

  1. On the Home tab, click Number Format Number format codes - Microsoft Support (6), and then click More Number Formats.

    (Video) Microsoft Access Number Formatting

  2. In the Format Cells dialog box, in the Category box, click Custom.

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

    The number format that you select appears in the Type box at the top of the list.

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

Format code guidelines

To display both 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, you could 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."

You don't have to use quotation marks to display the characters listed in the following table:

Character

Name

$

Dollar sign

+

Plus sign

-

Minus sign

/

Forward slash

(

Left parenthesis

)

Right parenthesis

:

Colon

!

Exclamation point

^

Circumflex accent (caret)

&

Ampersand

'

Apostrophe

~

Tilde

{

Left curly bracket

}

Right curly bracket

<

Less than sign

>

Greater than sign

=

Equal sign

Space character

To create a number format that includes text that is typed in a cell, insert an "at" sign (@) in the text section of the number format code section at the point where you want the typed text to be displayed in the cell. If the @ character is not included in the text section of the number format, any text that you type in the cell is not displayed; only numbers are displayed. You can also create a number format that combines specific text characters with the text that is typed in the cell. To do this, enter the specific text characters that you want before the @ character, after the @ character, or both. Then, enclose the text characters that you entered in double quotation marks (" "). For example, to include text before the text that's typed in the cell, enter "gross receipts for "@ in the text section of the number format code.

(Video) How to Add Merge Field Switch for Currency Formatting | Microsoft Word

To create a space that is the width of a character in a number format, insert an underscore (_) followed by the character. For example, if you want positive numbers to line up correctly with negative numbers that are enclosed in parentheses, insert an underscore at the end of the positive number format followed by a right parenthesis character.

To repeat a character in the number format so that the width of the number fills the column, precede the character with an asterisk (*) in the format code. For example, you can type 0*– to include enough dashes after a number to fill the cell, or you can type *0 before any format to include leading zeros.

You can use number format codes to control the display of digits before and after the decimal place. Use the number sign (#) if you want to display only the significant digits in a number. This sign does not allow the display non-significant zeros. Use the numerical character for zero (0) if you want to display non-significant zeros when a number might have fewer digits than have been specified in the format code. Use a question mark (?) if you want to add spaces for non-significant zeros on either side of the decimal point so that the decimal points align when they are formatted with a fixed-width font, such as Courier New. You can also use the question mark (?) to display fractions that have varying numbers of digits in the numerator and denominator.

If a number has more digits to the left of the decimal point than there are placeholders in the format code, the extra digits are displayed in the cell. However, if a number has more digits to the right of the decimal point than there are placeholders in the format code, the number is rounded off to the same number of decimal places as there are placeholders. If the format code contains only number signs (#) to the left of the decimal point, numbers with a value of less than 1 begin with the decimal point, not with a zero followed by a decimal point.

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#

Number:

44.398

102.65

2.8

Decimal points aligned:

44.398

102.65

2.8

???.???

Number:

5.25

5.3

Numerators of fractions aligned:

5 1/4

5 3/10

# ???/???

To display a comma as a thousands separator or to scale a number by a multiple of 1000, include a comma (,) in the code for the number format.

To display

As

Use this code

12000

12,000

#,###

12000

12

#,

12200000

12.2

0.0,,

To display leading and trailing zeros prior to or after a whole number, use the codes in the following table.

To display

As

Use this code

12

123

00012

00123

00000

12

123

00012

000123

"000"#

123

0123

"0"#

To specify the color for a section in the format code, type the name of one of the following eight colors in the code and enclose the name in square brackets as shown. The color code must be the first item in the code section.

(Video) Microsoft Excel Custom Numbers Formatting

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

To indicate that a number format will be applied only if the number meets a condition that you have specified, enclose the condition in square brackets. The condition consists of a comparison operator and a value. For example, the following number format will display 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 hide zeros or to hide all values in cells, create a custom format by using the codes below. The hidden values appear only in the formula bar. The values are not printed when you print your sheet. To display the hidden values again, change the format to the General number format or to an appropriate date or time format.

To hide

Use this code

Zero values

0;–0;;@

All values

;;; (three semicolons)

Use the following keyboard shortcuts to enter the following currency symbols in the Type box.

To enter

Press these keys

¢ (cents)

OPTION + 4

£ (pounds)

OPTION + 3

¥ (yen)

OPTION + Y

€ (euro)

OPTION + SHIFT + 2

The regional settings for currency determine the position of the currency symbol (that is, whether the symbol appears before or after the number and whether a space separates the symbol and the number). The regional settings also determine the decimal symbol and the thousands separator. You can control these settings by using the Mac OS X International system preferences.

To display numbers as a percentage of 100— for example, to display .08 as 8% or 2.8 as 280%— include the percent sign (%) in the number format.

(Video) How to Edit Phone Number Format in Microsoft Dynamics GP

To display numbers in scientific notation, use one of the exponent codes in the number format code— for example, E–, E+, e–, or e+. If a number format code section contains a zero (0) or number sign (#) to the right of an exponent code, Excel displays the number in scientific notation and inserts an "E" or "e". The number of zeros or number signs to the right of a code determines the number of digits in the exponent. The codes "E–" or "e–" place a minus sign (-) by negative exponents. The codes "E+" or "e+" place a minus sign (-) by negative exponents and a plus sign (+) by positive exponents.

To format dates and times, use the following codes.

Important:If you use the "m" or "mm" code immediately after the "h" or "hh" code (for hours) or immediately before the "ss" code (for seconds), Excel displays minutes instead of the month.

To display

As

Use this code

Years

00-99

yy

Years

1900-9999

yyyy

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

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 PM

h:mm:ss A/P

Time

4:36:03.75 PM

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

Note:If the format contains AM or PM, the hour is based on the 12-hour clock, where "AM" or "A" indicates times from midnight until noon and "PM" or "P" indicates times from noon until midnight. Otherwise, the hour is based on the 24-hour clock.

See also

Create and apply a custom number format

Display numbers as postal codes, Social Security numbers, or phone numbers

Display dates, times, currency, fractions, or percentages

(Video) Format numbers in cells in Microsoft Excel

Highlight patterns and trends with conditional formatting

Display or hide zero values

FAQs

How do I change the number format in Microsoft Office? ›

Define a new number format
  1. Select the text or numbered list you want to change.
  2. On the Home tab, in the Paragraph group, click the arrow next to Numbered List. ...
  3. To change the style, click the down arrow next to Number style and choose numbers, letters, or another chronological format.

What is number formatting with example? ›

You can use number formats to change the appearance of numbers, including dates and times, without changing the actual number. The number format does not affect the cell value that Excel uses to perform calculations. The actual value is displayed in the formula bar. Excel provides several built-in number formats.

What are the types of number formatting? ›

An N-formatted number has an implied decimal point (for example, 2.01 formatted as N2 is 201). An R-formatted number has an explicit decimal point and truncates trailing zeros (for example, 2.123 formatted as R2 is 2.12 and 3.10 formatted as R2 is 3.1).

How do I create a custom number format? ›

Apply a custom number format
  1. Select the cell or range of cells that you want to format.
  2. On the Home tab, under Number, on the Number Format pop-up menu. , click Custom.
  3. In the Format Cells dialog box, under Category, click Custom.
  4. At the bottom of the Type list, select the built-in format that you just created. ...
  5. Click OK.

What are the different number formats in Excel? ›

In Excel, you can format numbers in cells for things like currency, percentages, decimals, dates, phone numbers, or social security numbers.

How do you reset number format in Word? ›

Restart numbering at 1

Ctrl+click or right-click the item, and then click Restart numbering.

How do I format my number? ›

Since the U.S. and Canada both use the North American Numbering Plan, the commonly accepted formatting of phone numbers is (NPA) NXX-XXXX, NPA-NXX-XXXX or 1-NPA-NXX-XXXX, where 'NPA' is the area code, 'NXX' is the central office code and 'XXXX' is the remaining subscriber number personal to each phone number.

What are the 4 types of formatting? ›

To help understand Microsoft Word formatting, let's look at the four types of formatting:
  • Character or Font Formatting.
  • Paragraph Formatting.
  • Document or Page Formatting.
  • Section Formatting.
Apr 11, 2022

What is standard number format? ›

Standard numeric format strings are used to format common numeric types. A standard numeric format string takes the form [format specifier][precision specifier] , where: Format specifier is a single alphabetic character that specifies the type of number format, for example, currency or percent.

What is numeric formatting? ›

Numeric formatting occurs when converting a number to a string with the USING operator, for example in a LET , DISPLAY or PRINT instruction, and when displaying numeric values in form fields defined with the FORMAT attribute. Numeric values can be of type such as INTEGER , FLOAT , DECIMAL , MONEY , etc.

What are the 7 types of numbers with examples? ›

What does it look like?
Type of NumberExample
Prime NumberP=2,3,5,7,11,13,17,…
Composite Number4,6,8,9,10,12,...
Whole NumbersW=0,1,2,3,4,…
IntegersZ=…,−3,−2,−1,0,1,2,3,…
5 more rows
Dec 9, 2022

What are 3 formatting styles? ›

The Three Popular Formatting Styles | APA, MLA, CMOS

In academics, presenting the information in an appropriate manner by using correct formatting styles is as putting forth the key study idea.

How do I change the data format of a number? ›

Format numbers, dates, and currencies
  1. On your Android phone or tablet, open a spreadsheet in the Google Sheets app.
  2. Tap a cell or range of cells.
  3. Tap Format. .
  4. Tap Cell. Number format.
  5. Tap an option from the list. The format will be applied to your selected cells.

Where custom number formats can be use? ›

Custom number formats can control the display of numbers, dates, times, fractions, percentages, and other numeric values. Using custom formats, you can do things like format dates to show month names only, format large numbers in millions or thousands, and display negative numbers in red.

Can you customize your number? ›

A custom telephone number is available through many VoIP service providers such as RingCentral. The first step you need to take before purchasing a phone number is deciding what type of custom phone number you want.

How do I fix number format error in Excel? ›

Next to the selected cell or range of cells, click the error button that appears. On the menu, click Convert to Number. (If you want to simply get rid of the error indicator without converting the number, click Ignore Error.) This action converts the numbers that are stored as text back to numbers.

How many data formats are there? ›

The four types of data are text, number, logical and error. You may perform different functions with each type, so it's important to know which ones to use and when to use them. You may also consider that some data types may change when exporting data into a spreadsheet.

How important is number format in Microsoft Excel? ›

Whenever you're working with a spreadsheet, it's a good idea to use appropriate number formats for your data. Number formats tell your spreadsheet exactly what type of data you're using, like percentages (%), currency ($), times, dates, and so on. Watch the video below to learn more about number formats in Excel.

How do I fix a corrupted number in Word? ›

Fixing Microsoft Word Blacked Out Numbers
  1. For each Heading, Modify / Numbering / None / OK.
  2. Once all Headings are set to no numbering, select a heading set to heading one.
  3. And select on the Home ribbon, the Multi-level List with Numbers and headings.
  4. All Headings should now have the correct numbering:
Jun 2, 2015

How do I fix formatting errors in word? ›

Select the text that you want to return to its default formatting. On the Home tab, in the Font group, click Clear All Formatting. On the Home tab, in the Font group, click Clear All Formatting. On the Message tab, in the Basic Text group, click Clear All Formatting.

Is US code 001 or 1? ›

001, or +1 is the telephone calling code of North America; which includes Canada, the United States and the Caribbean.

What is a valid US number? ›

The standard American telephone number is ten digits, such as (555) 555-1234.

What are the three 3 levels of formatting? ›

Formatting Basics. Word deals with formatting on three levels encompassing small and specific on up to big and broad—through characters, paragraphs, and sections. You apply different types of formatting to each of these parts.

What are the formatting options? ›

Which text formatting options are available?
  • Bold.
  • Italic.
  • Underline.
  • Text and Text Background Color.
  • Code run - use to format the selected words as code snippets (e.g. HTML or Javascript).
  • Subscript.
  • Superscript.
  • Paragraph style - Normal, Code, or Prompt.
Apr 21, 2021

What is an example of format? ›

Example Sentences

Noun The journals are available in electronic format. The file is saved in MP3 format. Verb The book is formatted in several different styles. The data was improperly formatted.

What is integer format? ›

The integer format is similar to the binary format with two exceptions: The integer format allows the full range of binary values. The number of decimal positions for an integer field is always zero.

What is decimal format? ›

DecimalFormat is a concrete subclass of NumberFormat that formats decimal numbers. It has a variety of features designed to make it possible to parse and format numbers in any locale, including support for Western, Arabic, and Indic digits.

What is numbering in MS Word? ›

You can use the numbered list feature in Word to add numbers to cells in a table. Note: If a table cell contains multiple lines of text, Word numbers each line in the cell. Select the table cells that you want to number.

How do I change the number format in numbers? ›

Select a cell or a range of cells you want the format to apply to, then in the Cell pane of the Format inspector, choose Create Custom Format from the Data Format pop-up menu. Type a name for your format, then choose Number from the Type pop-up menu.

How do I fix messed up numbers in Word? ›

Right-click the number you want to change. Click Set Numbering Value. In the Set value to: box, use the arrows to change the value to the number you want.
...
Start with 1.
  1. Double-click the numbers in the list. The text will not appear selected.
  2. Right-click the number you want to start a new list.
  3. Click Restart at 1.

Why do we need to set the number format in number? ›

If you want your data to appear exactly as entered, you'll need to use the text number format. This format is especially good for numbers you don't want to perform calculations with, such as phone numbers, zip codes, or numbers that begin with 0, like 02415.

How do I fix an incorrect number format in Excel? ›

Turn off Error Checking

Under Help, click Options. In the Excel Options dialog box, click the Formulas category. Under Error checking rules, clear the Numbers formatted as text or preceded by an apostrophe check box.

What is a valid US phone number format? ›

The standard American telephone number is ten digits, such as (555) 555-1234. The first three digits are the "area code," which, in the past, indicated in what part of the country the phone was located.

What is the default number format in Excel? ›

The General format is the default number format that Excel applies when you type a number. For the most part, numbers that are formatted with the General format are displayed just the way that you type them.

Videos

1. Excel: Understanding Number Formats
(GCFLearnFree.org)
2. CUSTOM FORMATTING OF PHONE NUMBERS IN MICROSOFT EXCEL
(Brian Turgeon)
3. Number Format Action in Power Automate or Microsoft Flow | Power Automate – Advanced Expression
(Dhruvin Shah)
4. How to Format Numbers as Currency in Microsoft Excel
(MDTechVideos)
5. How to Format Numbers as Currency in Microsoft Excel Tutorial
(The Teacher)
6. AM 4113 Custom Number formats Microsoft Excel 2003
(Ron Taylor)
Top Articles
Latest Posts
Article information

Author: Chrissy Homenick

Last Updated: 03/09/2023

Views: 6607

Rating: 4.3 / 5 (54 voted)

Reviews: 93% of readers found this page helpful

Author information

Name: Chrissy Homenick

Birthday: 2001-10-22

Address: 611 Kuhn Oval, Feltonbury, NY 02783-3818

Phone: +96619177651654

Job: Mining Representative

Hobby: amateur radio, Sculling, Knife making, Gardening, Watching movies, Gunsmithing, Video gaming

Introduction: My name is Chrissy Homenick, I am a tender, funny, determined, tender, glorious, fancy, enthusiastic person who loves writing and wants to share my knowledge and understanding with you.