The Definitive Guide to Custom Number Formats in Excel

Excel has a lot of built-in number formats, but sometimes you need something specific. Whether you’re representing a little-used currency, tracking in-stock units, or want to color code profits and losses, you are in need of a an Excel custom number format. Number formatting in Excel is pretty powerful but that means it is also somewhat complex. This is the definitive guide to Excel’s custom number formats…

 

Using Custom Number Formats in Excel

By default, each cell is formatted as “General”, which means it does not have any special formatting rules. When you enter data in a cell, Excel tries to guess what format it should have. When it doesn’t guess correctly, you need to change the format. Excel has a few pre-set formatting options attached to buttons in the Home menu, but if those don’t meet your needs, you need to use the full options available in the Format Cells menu.

To access this menu, look for the Number section of the Home menu tab. Click the arrow in the lower right corner of the Number section.

It will bring up the Format Cells menu in the Numbers tab:

Format Cells Dialog

Underneath the pre-defined number formats for common items like currency and percentage, there is a category called Custom. The format types in this section are different from the pre-set options. They are filled with symbols and codes:

Custom Formats Dialog

A number format code is entered into the Type field in the Custom category. These codes are the key to creating any custom number format in Excel. First, however, we need to understand how they work…

Understanding the Number Format Codes

Number format codes are the string of symbols that define how Excel displays the data you store in cells. We will get into the ways to describe the formats in a minute, but first we need to go over how Excel interprets those symbols. Each number format code is made up of as many as 4 sections separated by a semi-colon (;).

These sections control formatting for one or more parts of the number line, including positive numbers, negative numbers, and zeros. They can also control formatting for sub-sets of these parts, like all numbers greater than 100 and text-based data. What each section controls depends on how many sections there are in the number format code. A full number format code will be entered as follows:

Section1;Section2;Section3;Section4

The behavior of different parts of the number line will be as follows:

Section Format Behavior

As indicated above, when there is just one section provided, it describes the format for all numbers. With two, the first section describes the format of positive, zero, and text values, while the second section describes the format of negative values, etc.

You can choose to skip formatting for any of the middle sections by entering General instead of other format data. For example, if you only want to affect positive numbers and text, you can enter a number format code with this arrangement:

Section1;General;General;Section4

General strips all formatting from the data entered, so be careful how you use it. Negative numbers with the General format code will not display the minus sign in front of their number.

Important note: Using a single section number format code does not always have the same result as expanding the same rules to all sections. For example:

Section1

Is not the same as:

Section1;Section1;Section1;Section1

Look at the examples below to see examples of the difference…

Now that we understand what a number format code is, what can we do with it?

Changing Font Color with Number Format Codes

One of the simplest things you can do with number format codes is change the color of the font in the affected cells. The syntax for doing so is simple:

[Color Name]

Just choose the section that corresponds to the part of the number line you want to change color, and provide the color in brackets. The color options are as follows (the background is gray for contrast in the table, but backgrounds are not affected by the number format code):

Color Format Table

As an example, we can provide a separate color code for each part of a number format code:

[Red]General;[Blue]General;[Magenta]General;[Cyan]General

The General message just tells Excel to represent the numbers as entered by the user. The output of this number format code looks like this:

Color Format Output

Note that the negative number in row 3 does not automatically get a negative sign (-) in front of it. We are overriding the default format of negative numbers in the cell. Also notice that the color format is not affecting anything about the presentation; the number of decimal places stays the same, as does the alignment of the data to the left or right of the cells.

Adding Text with Number Format Codes

You can add text around numbers with number format codes by inserting the text in a section one of two ways:

Single Characters

For single characters (like an @ symbol before a number), type a backslash (\) followed by the symbol. The number format code:

\@General

Results in the following output:

Leading Backslash Output

Note that the minus sign still precedes the negative number. Also note that the Text value is not affected by the @ symbol addition.

Importantly, this is different from the result if we expanded our format guideline to each section of the number format code:

\@General;\@General;\@General;\@General

Results in the following output:

Leading Backslash Output Expanded

Note that the minus sign is gone from the negative number and the Text value now receives the @ symbol.

Text Strings

To add an entire text string to a number (like adding “units” to the end of a number), we surround the text string in quotation marks (” “). The number format code:

General" units"

Results in the following output:

Quote String Output

Once again, this is different from:

General" units";General" units";General" units";General" units"

Which results in:

Quote String Output Expanded

Special Characters

In addition to these two methods, there is a set of special symbol characters that do not need a leading backslash or quotes to be included in the number format code. The list is as follows:

Free Symbols Table

Excel will also accept most other non-mathematical symbols, such a non-dollar currency symbols, copyright/trademark symbols, and Greek letters. These symbols are not available on most standard keyboards, but they can be entered by holding down the ALT key while typing in a four-digit number. Some of the most useful ones are below:

ANSI Free Symbols

A full list of ANSI character codes can be found on Wikipedia here.

Changing Decimal Places, Significant Digits, and Commas

Adding symbols and colors is useful, but most of the work you’ll likely need to do with custom number formats is change the way Excel displays the numbers it stores. Number format codes use a set of symbols to represent how the data should appear in the cell. Here is a summary of the symbols:

Format Modifiers Table

Let’s review them each in turn…

Zero (0)

Zeros in the number format code represent a forced digit. That means that whether or not the digit is relevant to the value, it will be shown. A great example of this is the standard dollars and cents notation that is used to represent prices in the United States: $0.00. Even if there are no extra cents in the amount, the two zeroes are still shown in the notation.

Here is an example of the zero code in action. The following examples are using this number format code:

0.00

Zero Behavior

Question Mark (?)

Question marks in the number format code represent an alignment digit. This means that when the number being shown doesn’t need the digit in question, a blank space of the same size is used. This is used to align decimal and comma places for more easy ranking of values, etc.

Here is an example of the question mark code in action. The following examples are using this number format code:

0.??

Question Mark Behavior

Pound Sign (#)

Sometimes called a hash mark, the pound sign in the number format code represents an optional digit. This means that when the number being shown doesn’t need the digit in question, it will be omitted from the displayed number. This is most often used to represent numbers in their most easily readable form.

Here is an example of the pound sign code in action. The following examples are using this number format code:

#.##

Pound Sign Behavior

Period (.)

The period in the number format code represents the location of the decimal point in the number being displayed. When paired with the comma code, it can show numbers in thousands or millions, changing 1,200 to 1.2, for example. It is similar to the text format codes above in that it is always displayed when it is part of the number code, even when number being displayed does not straddle the decimal point. See the comma, pound sign and question mark examples above for useful illustrations of the period in use.

Comma (,)

The comma in the number format code represents the thousands separators in the number being displayed. It allows you to describe the behavior of digits in relation to the thousands or millions digits.

Here is an example of the comma code in action. The following examples are using this number format code:

$??,???.00

Comma Behavior

Asterisk (*)

The asterisk in the number format code represents the repeating character modifier. It is used along with a character to display a repeating digit that fills the empty space in a cell.

Here is an example of the asterisk code in action. The following examples are using this number format code:

*=0.##

Asterisk Behavior

Underscore (_)

The underscore in the number code represents the space character modifier. It is used along with a character to display a blank space equal in size to the specified character. It can be used, for example, to properly align positive and negative numbers when parentheses are used in only the negative case.

Here is an example of the underscore code in action. The following examples are using this number format code:

_(#.##_);(#.##)

Underscore Behavior

Using Fractions, Percentages, and Scientific Notation

Certain types of notation require that symbols be used to indicate the format change, including fractions, percentages, and scientific notation. Here is a summary of the symbols for each:

Notation Symbol Table

We’ll examine each in detail…

Fractions (/)

Fractions are special, since they require a change in units. The number 0.23 is represented as 23/100, but 0.25 can be simplified t0 1/4 or shown as 25/100. Similarly, 1.25 can be shown as 1 1/4 or the improper fraction 5/4. Which way Excel displays the number depends on how you construct the number format code.

Fractions effectively round values to the nearest possible fraction. They also take the guidelines of the pound sign and question mark symbols they are paired with.

Integer with Reduced Fractions

A fairly typical representation for fractions is to keep the whole numbers independent from the fraction remainder. The representation for this is relatively straightforward and can be done with pound signs and question marks to slightly different effect…

Using question mark (?) notation, the following number format code:

# ???/???

Produces a fraction remainder with up to three digits:

Fraction Remainder Question Mark Behavior

The alignment of the fraction bar is preserved regardless of the number of digits in use. If we limit the number of digits on each side of the fraction to 2, Excel will round the number to the nearest fraction value. The following number code format:

# ??/??

Changes the representation of 0.23 from 23/100 to 3/13.

If you don’t wish to preserve the alignment around the fraction bar, you can use a similar fraction number format code that uses pound signs.

Using pound sign notation, the following number format code:

# ###/###

Produces a more readable fraction remainder that can be justified or centered in the cell:

Fraction Remainder Pound Sign Behavior

Improper Fractions

If you’d rather bundle the whole number portion of a value into the fraction itself, you can specify as much in the number format code.

Using pound sign notation, the following number format code:

###/###

Produces an improper fraction with up to three digits:

Improper Fraction Pound SignBehavior

Fixed Base Fractions

It is also possible to force Excel to round fractions to a specific denominator by specifying it in the number format code.

Here is an example of a fixed base code in action. The following examples are using this number format code:

# ##/15

Fraction Fixed Base Behavior

The result is a rounded fraction remainder that goes to the nearest number of 15ths.

Percentages (%)

Much like fractions, percentages are controlled by the number format codes that accompany them. A basic percentage can be achieved with a pound sign symbol in the number format code:

#%

Results in the following output:

Percent Behavior

You can also specify fractional percentages, as shown with this number format code:

# #/#%

Results in single-digit fractions in the percentages where needed:

Percent Fraction Behavior

Finally, as always, you can specify the number of significant digits with decimal places:

#.0%

Results in a 10th place aligned decimal:

Percent Decimal Behavior

Scientific Notation (E)

It’s difficult to read extremely small and extremely large numbers conventionally because of all the leading and trailing zeroes. Scientific notation fixes that by moving the decimal to the relevant digits, so 0.0000001 can become 1 x 10-7. Excel uses the E notation for this, so that same number would be 1E-07. So, as you’d expect, the capital letter E signals scientific notation in number format code.

Otherwise, scientific notation in Excel is controlled by the same number codes as percentages and fractions. It needs a number format code in front of the E to describe the relevant digits and a plus (+) and another number format code behind to describe the handling of the exponential digit.

Here is an example of a scientific notation code in action. The following examples are using this number format code:

#E+#

Scientific Notation Behavior

You can also achieve more consistent notation with zeros. The following examples are using this number format code:

0.00E+00

Scientific Notation Zero Behavior

Note that in this case, the decimal and exponent are both constrained to 2 significant digits, regardless of whether they are necessary. The trade-off is, it keeps the output far more consistent, with a predictable string length.

Dates and Times

Dates and times in Excel are a special case. For a detailed discussion of how Excel uses them, please review the Definitive Guide to Using Dates and Times in Excel.  The number format codes work identically to the format_text input for the TEXT command, and they can be reviewed here.

Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

Other posts in this series...

82 thoughts on “The Definitive Guide to Custom Number Formats in Excel

  1. I have a column of numbers that have varying numbers of decimals characters. I need to only show the digits. Is there an easy way to only show the numbers without the decimal?
    Thanks!

    1. Hi,

      You can simple do this by selecting the cells, choosing format, number and then use the “Decimal Places” box to select 0. This will make excel automatically format each cell by rounding up. So bear that in mind 🙂

      Also – good article, thanks for info.

    1. I am using Excel 2013 and the format 00000″-“0000”-“000 works. Any extra digits are added to the 5 digit segment; less than 12 digits results in lead zeros.

  2. There is also Engineering Notation, a sub-set of Scientific Notation. I displays numbers in an exponential-3 format:

    Engineering Notation
    Engineering Notation uses a format similar to Scientific Notation, but its exponents are always expressed in powers of three. Consequently, the mantissa is expressed as n = 1.000… to 999.999… . The usefulness of Engineering Notation is that it readily makes evident the number of thousands (103 or kilo), millions (106 or Mega), billions (109 or Giga), thousandths (10-3 or milli), millionths (10-6 or micro), billionths (10-9 or nano), etc., of a numerical value.
    The following format causes numbers to be expressed in standard Engineering Notation, with three places after the decimal and two digits (with a leading zero) in the exponent:
    Format Example
    ###.000E+00 299.792E+06
    The following format causes numbers to be expressed in standard Engineering Notation, with up to nine places after the decimal:
    Format Example
    ###.#########E+00 8.854187817E-12
    Note: To cause whole numbers to have at least one digit after the decimal point, place a zero (0) immediately after the decimal point in the format code:
    Format Example
    ###.0########E+00 400.0E+03
    The following format causes numbers to be expressed in standard Engineering Notation, with at least three places and up to three additional places after the decimal:
    Format Example
    ###.000###E+00 400.000E+03
    The following format causes numbers to be expressed in standard Engineering Notation, as above, but with a space between the “E” and the last digit of the mantissa:
    Format Example
    ###.000### E+00 299.792458 E+06
    The following statement causes values in kilowatts less than 1000 to be displayed in a format with trailing zeros or tenths of kilowatts shown, and values in kilowatts greater than or equal to 1000 to be displayed in a format with trailing zeros omitted or tenths of kilowatts rounded up to the next kilowatt:
    [>=1000]#,##0.#” kW”;[<1000]0.0" kW"
    Engineering Notation is mostly used by electrical and electronics engineers, and in those engineering colleges and universities where courses in those disciplines are taught.

  3. I constantly work with long, 16-digit claim numbers that are numeric. We need them to show all 16 digits, but copying a column of them into Excel puts it in Sci Notation, and even converting from that to “Number” will round off the last few digits, which is not acceptable. Formatting the column as “Number” before pasting causes the same result. How can we get Excel to recognize the full number without loss of information?

  4. I’m trying to sort data using a column that contains codes with three letters and four numbers such as AAA1212. When I sort, the custom mmm-yy format built into the ss converts some of the codes – the ones with APR or AUG for example, into date format such as APR-12. Changing the format to text or general produces a result that is a string of numbers, eg 1612301. What format can we use in the column keep the original data of AAA1212 or AUG1212 when so we can sort the sheet by the column with this data?

  5. I work with Bond quotes that are in 32nds. This is understood throughout the industry so it’s common to drop the 32 from the fraction. 102 10/32 would be displayed as 102 – 10 (also much faster to key in on a 10-key). Any idea how I can accomplish this with cell format?

    1. Does the cell that the data is keyed into have to recognized as 102.3125?

      Can the display cell be a different cell from the data entry cell?

      Can the whole dollar value and the fractional value be in different cells?

      Finally, does or can your spreadsheet have some VBA embedded?

  6. I am trying to format cells as “GENERAL” with comma separators – 12,313; 2.387; and 1,287.5694 would display exactly as I typed them here. The closest I have come is a format that will display the decimal point whether or not there is anything after it – 3 is displayed as “3.” Any suggestions?

    1. I have the same issue and utilize the MOD function. Here’s a sample:

      TEXT(E87,IF(MOD(E87,1)0,”#,##0.0#######”,”#,###”))

      I’m converting source data so I stick with the excel function. I’m sure you could use VBA if the data entry cell needs reformatted.

      1. You could do a conditional format. So the normal custom format is: #,##0.#### or whatever. And then add a conditional format (Rule type: Use a formula), formula is =mod(A1,1)=0 , and format if this is true is: #,##0 or similar, where A1 is the upper left cell in your range.

  7. I can uses little help
    I have cell that Number is entered as 2,4 and I like to uses Custom cell format to end up with CKT 2,4
    is this possible? the comma is the entry is giving me problems “CKT” “”# if no comma ends up with CKT 24

  8. 2nd question

    can the tab sheet be linked to a cell on other sheet

    so if Sheet2!B44 was” Dinner” then sheet tab on disired sheet would show up as Dinner

  9. Nice find, your site.
    I’ve been working on inserting non-printing control characters in custom formatting: “^p”, for instance, to produce a line feed. Custom formatting doesn’t seem to accept the above or a keyboard entry: Alt+return.

    So far I’ve been reduced to constructing concatenated TEXT() functions.
    Am I missing something?
    JMarks.

  10. Hi friends,

    I was trying to format a cell with similar to following manner, where the engineering notation has to star with 0.. Any suggestions?
    1.751200E+12 => 0.175120E+13

  11. You forgot to mention additional uses of brackets in Excel custom number formats. You can use brackets for conditional formatting using a custom number format, rather than using Excel’s Conditional Format feature (which while allowing more options, is also more cumbersome to code in some scenarios).

    Example custom format: [=1] 0 “cup”;# ?/? “cups”
    If the cell value is equal to 1, the contents will display as: 1 cup
    If the cell value is equal to 2.5, the contents will display as: 2 1/2 cups
    As you can see, depending on the value of the cell we want to use different text and number formatting.

    I find this feature extremely useful, though rarely used. Unfortunately very few (if any) websites discussing Excel custom formatting cover this feature (if at all). To be honest I couldn’t find any websites discussing this feature, and was only able to use it / discover it through trial and error, though I stumbled across it years ago in an Excel developer forum.

    How far you can go with this particular feature I really don’t know, but the possibilities are very intriguing. I do know you can also use much more complicated variations like example format: [=3] 0 “more”; 0
    and when the cell value is equal to 1, contents display as:
    1 less
    when the cell value is equal to 3, cell contents display as:
    3 more
    and when the cell value is equal to 2, cell contents display as:
    2
    Proving that conditions in brackets can be combined.

    As I said, this little know feature is very interesting, but I really wish someone could expand on this and define the rules governing this feature of custom number formats, and it’s limitations.

  12. I am trying to display 15 numbers to the left of the decimal and 4 to the right. I am using ##############0.0###; however, it appears the cell will not display more than a total of 15 digits. When I have 4 digits after the decimal, to make sure they all get displayed, the most digits I can have to the left of the decimal is 11. For each digit I add to the left past 11, I lose a digit to the right of the decimal. Is there a way to display all 19 digits?

    1. Use two cells on the same row; in the left cell, use [FORMAT CELL], [NUMBER], (Decimal Places) – 0 (zero), and in the right cell, use [FORMAT CELL], [CUSTOM], (Format) #.0000.

  13. Hi
    When I enter say 23 I want the default display to show 23.00 but at present Excel shows 0.23.
    I cannot find a way to set up all of my spread sheets with this default format and then all I have to do is selectively change the minority of other formats, dates, etc as and when I need to

    1. I think your Excel Setting “Fixed Decimal Points” might be on and set to 2. Try looking for it in the Options. If taht is the case, in order for Excel to display 23.00, you have to enter 2300.

      1. Wrong; this setting can be changed:
        Click the OFFICE BUTTON at the top left corner of an Excel page; select EXCEL OPTIONS (at the bottom of the page); select ADVANCED; then under EDITING OPTIONS, check or uncheck AUTOMATICALLY ENTER A DECIMAL POINT; then click OK at the bottom right of the panel – if checked, entries apear in hundredths, if unchecked, entries apear as whole numbers; finit.

    2. Click the OFFICE BUTTON at the top left corner of an Excel page; select EXCEL OPTIONS (at the bottom of the page); select ADVANCED; then under EDITING OPTIONS, check or uncheck AUTOMATICALLY ENTER A DECIMAL POINT; then click OK at the bottom right of the panel – if checked, entries apear in hundredths, if unchecked, entries apear as whole numbers; finit.

  14. Great article, but I have a head scratching problem. I need to show 2.50 as 000250, any idea on how to do that without using a text format?

  15. Hi.
    Is there a way to save custom format cell. Facing problem when doing same thing in another file, it disappear automatically in next file

    For your reference Image is attached.

    Regards
    Sat

    1. Custom formats are saved with the files they are created in. The only way to carry over formats would be to adjust your PERSONAL.XLB file, but these might not transfer to other computers and that is beyond the scope of this article. Good luck!

  16. I want to format cell that displays date and OPTIONALLY time. Meaning, when the time exists, display it but when it doesn’t, HIDE it (don’t display 00:00).

    1. You will need to use two separate cells to do it this way:
      For two adjacent cells:
      (1) Place the date-time value in the first cell.
      (2) Place =[CELLNAME1] in the next cell over, or down; whichever way you want it displayed
      (3) Format the first cell in whatever Date format you want.
      (4) Use Conditional Formatting to format the second cell with this statement:
      =AND(HOUR(CELLNAME2)=0,MINUTE(CELLNAME2)=0,SECOND(CELLNAME2)=0)
      (5) Specify WHITE as the font color if the above condition is met.

      The first cell will display the date, and the second cell will display the time if the time is non-zero.

  17. This is the best Custom Format article I have found
    Thank you very much

    And also thank you Charles for the Engineering and Electrical notation examples. Very helpful.

    Cheers,
    Ben

  18. Dear Sir,

    I want to use this custom numbers format in excel permanently.
    [>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0

    when i adding in customs formats , after closing of workbook it will not working.
    kindly advise permanent solution.

    1. Custom formats are stored by Excel; so they will still exist after closing, then opening a workbook; but the format must be invoked for each cell or set of cells in which you want to display the format, and that workbook will need to be saved, as well. To transfer a format to other cells, you can use the “Format painter” feature (the icon is a paintbrush), or merely copy or cut the cell into another cell. Additionally, this format is also one of the standard formats available in Excel. You can select “Format Cells”, “Number”, click the DOWN or UP arrow in the “Decimal Places” box until the number in the box is 0 (Zero), & check the “Use 1000 Separator (,)” box. This format will also still exist after closing, then opening a workbook; but you will need to invoke it for each cell to which you want it applied.

  19. I have columns of over 50,000 numbers ranging from 2500 to 0.000021. I am given the numbers in an excel spreadsheet with 9 decimal places. I need to change the decimal place format to the lowest number, as in 1.5, 0.02 and 0.505 instead of 1.500000000, 0.020000000, and 0.505000000. I used to be able to do this using advanced find/replace and wild cards, but I have forgotten. Any ideas?

    1. Yes; use “Conditional Formatting” – “New Rule” – “Use a formula to determine which cells to format” under the “Home” tab, & set up different formats for numbers based on their values, then apply the format to cells as needed.

  20. When I enter a formula in Excel it appears automatically as an absolute reference. How can I get it to default to a relative reference?

    1. Place your number string in a cell; in this example, cell B19. Then in another cell, place this command: =MID(B19,FIND(“(“,B19,1)+1,3)&MID(B19,FIND(” “,B19,1)+1,3)&MID(B19,FIND(“-“,B19,1)+1,4). The cell will then display your number as you want it.

  21. I have text-type content of a cell, for instance: “123ABC456”, is there any way to use Custom Format to get in result “123 ABC 456” (formatting should add spaces before and after actual text – separate it from digits)?

      1. No: however, you can get Excel to display your characters the way you want by placing the character string in a cell, in this example, cell A4; then in the next cell to the right, enter “=LEFT(A4,3)” with a cell width of 2.83; leave a blank cell with a width of 1; in the next cell to the right, enter “=MID(A4,4,3)” with a width of 2.83; leave another blank cell with a width of 1; and finally, enter “=RIGHT(A4,3)” in the last cell.

        1. Oh, I forgot to mention that the last cell needs to have a width of 2.83, and that the left-most display cell needs to have a left-align applied to it, the center-most display cell needs to have a center-align, & the right-most display cell needs to have a right-align.

          1. The problem is that I would like to avoid using another cell/column to get the result, and maintain original value (spaces are only for user convenience). What’s more, it’s not necessary to use 5 additional cells/columns – it could be done using one formula in one cell (just join your three formulas using &” “&). Anyway, thank you for your effort!

  22. Great article! The only thing I don’t see is information about the @ character. As best I can tell, that code tells Excel to treat the value as text. So @*. creates trailing periods, which is awesome! Unfortunately, using the @ code appears to remove any other formatting, so the superscript portion of a cell (used to denote a footnote) gets stripped of the superscript and shown as standard text. Bummer!

  23. I am not sure how to format WITHOUT adding spaces in the a cell from “1234567890R1” to “12345 67890 R1”. Would someone know how to do the formatting code? Thank you.

    1. Hi Catherine! The example you provided has numbers and letters, so Excel won’t treat it as a number field. You can add spaces by using LEFT(), MID(), and RIGHT() to break up the field.

  24. Hi

    I have lots of numbers in millions – is there any way to make the comma (thousands separator line up so the relative digits align

  25. I have a negative number in a cell, like -1.232 , and I want to use custom format cell to make it look like B=-1.232, but when I type “B=” #.### the minus sign goes in front of B like -B=1.232. Can somebody help? Thanks 🙂

  26. Excel uses four format code sections; format for positive numbers, format for negative numbers, format for zeros, format for text.
    To get the displayed result you want, use the following format string:
    “B = “0.000;”B = “- 0.000;”B = “0.000
    or
    “B = “0.000;”B = “- 0.000.
    If you don’t want the spaces between the characters, eliminate them.

    1. In a cell, place your multi-digit numbers; in the next cell over, use the RIGHT text function:
      =RIGHT([cell],1), with [cell] being the cell reference.
      The result will look like this:
      345 5
      692 2
      157 7
      468 8

  27. For international readers: if your system is using “,” (comma) as decimal separator, you have to mentally exchange , and . when reading this article. For example, in Latvia we use to format currency values like this:
    # ##0,00##
    which results in

    1 234,56
    1 234,00
    1 234,5678

  28. Great information, the engineering notation especially useful for me. Any suggestions how to format a complex number? They always display with full decimal places, e.g. 1.65287436333954+1.34696283284023i

  29. There are two ways to build complex numbers in Excel:
    (1) place the real and imaginary values in separate cells and use the COMPLEX function.
    (2) enter the number directly, as in 1.65287436333954 + 1.34696283284023i.
    Excel treats both equally with respect to the complex number functions.

    If you want numbers rounded off, use the ROUND function embedded within other functions, as in =COMPLEX(ROUND([CELL],3),ROUND([CELL],3)), or
    =ROUND(A205,3)&”+”&ROUND(B205,3)&”i”
    Excel also treats both equally with respect to the complex number functions.

  30. Hi guys. I’m working on a spreadsheet where I’m using 1 row per ‘item’ and so I’m trying to get some cleaver formatting into a particular cell. Under the heading of File Location, I’d like to put the ‘name’ of the location and the ‘path’ but aligned in different directions, ie:

    ———–File Location—————
    Workspace
    C:\MyWorkSpace\
    ___________________________
    Workgroup
    C:\

    So I’ve been trying to use the repeat character formatter “* ” to align the 2nd line of text to the right. However this only really works for a single line of text per cell. If the cell contains the text “Workspace” and the number formatter contains the text “@* “C:\MyWorkSpace” and the cell is wide enough, then everything looks good. But put a newline character in (Alt+Enter) after the word Workspace, then you’ll see that the 2nd line doesn’t align to the right properly. Can anyone think of a fix for this?

  31. I want to use the “Single Accounting Underline” with a custom format with percentages but I cannot get this to work. It only underlines the number.

  32. Surprised not to see any mention of the other use of the comma. Adding a comma at the end of the formatting code rounds the value to the nearest thousand. Adding two commas rounds to the nearest million. Thus using a format code of “#,##0.0,” the number 12589 is shown as 12.6 and 23 is shown as 0.0. Similarly, using a format code “#,##0.0,,” 123456789 is shown as 123.5

  33. Every now and then, I find a post the EXACTLY answers my question in a clear, concise, accurate and complete manner.

    Thanks for that.

  34. Hi – I somehow stumbled on this website that “FINALLY” explains a lot that I’ve been desperately looking for. However, I am still struggling to find a simple solution for displaying both positive and negative-red-values for the Indian Rupee.

    For positive numbers, this works:
    [>=10000000][$INR] * ##\,##\,##\,##0.00;[>=100000][$INR] * ##\,##\,##0.00;[$INR] * ##,##0.00

    For negative numbers, this works:
    [Red][<=-10000000][$INR] * -##\,##\,##\,##0.00;[Red][<=-100000][$INR] * -##\,##\,##0.00;[Red][$INR] * -##,##0.00

    But how do I combine the two ? Combining them with a semicolon does not work – and I now understand about the 4 section bit.

    Is it possible to group the positive and negative formatting in parenthesis ? Is there another option available ? I will be very grateful for a solution to this.

    Thanks

    1. Also looking for a way to combine two conditions/formats for positive/negative numbers. Personally just want to display numbers >= 1000 and <=-1000 with a comma and no decimals, and anything else with two decimals. Help would be appreciated!

  35. Excellent comprehensive article! Thank you.

    Quick question, is there a way to format data tables within Excel?

    Thanks again for posting this.

  36. Thank you for the article!! Very helpful! Ive got an issue I can’t seem to resolve. is this possible?

    I’ve got a format that changes from this:
    22-22-222 to this: 22-2a-222 to this: 22-aa-2222 randomly throughout column E of my worksheet. It changes back and forth between the above mentioned formats.
    Is there a custom format I can implement so that it will stay the same all the way down the entire column?
    Thank You In advance!

  37. Hi. I want to display a number, say, 20 in format “at 20°C”. I make Custom Formatting as “at “#”°C”, and it shows what I want if the temperature is positive. When it is negative I see “-at 20°C”. Please help.
    Thank you.

  38. Regarding colors: you are not limited to those listed.
    I wanted to make grey text, tried using the color names with numbers such as Color48 (they range from 1 to 57, you can find many references by googling “excel number format color numbers”).

  39. Let say I have two numbers, 10% and 10.5%. Is there a way to format the number so that the since the 10% has no fraction, there will be no decimal point, but since 10.5% has a fractional percentage, that will be shown. Thank you!

  40. This information is fantastic! Thank you!! I’m looking for suggestions on how to format a cell with a particular format. Here’s an example:
    How it’s keyed into worksheet: 20170215021KC
    Need to autoformat and display as: 20170215_021_KC

    Any suggestions will be greatly appreciated.

  41. Lower to upper case in the same cell (custom format string) i.e. cell A1 = n (change to upper case) cell A1 = N (how to custom format string)

    1. Hi Ryo! Custom number formats are saved in the spreadsheet you worked on when adding them. If you want them to load every time, you need to save them in the startup template Excel uses when it loads. Search for “Excel Startup Template” or go here for more details.

  42. It’s pretty hard to me. 😀 Anyone could tell me what kind of code I need, if I want to display 0 as 00/00? For 1 it’s 00/01, for 10 it’s 00/10, for 100 it’s 01/00, for 1000 it’s 10/00, for 10000 it’s 100/00 etc.

  43. 00\/00
    That will display the numbers as you wanted to . But note that for example 2344.3455 or – 67 will be displayed as 23/43 or -00/67 respectively.

  44. I am trying to format phone numbers that may or may not have an extension. If the number has an extension, the extension may be 1-5 digits long. If there is an extension I want to display an “x” after the 10 digit number followed by the extension. So, if the cell value is 123555121299887, I want it displayed as: (123)555-1212 x99887.
    And a cell value of 234555121298 should display as (234)555-1212 x98
    I tried this custom format: [<=9999999999](###)###-####;(###)###-#### x#####
    but it only works for numbers w/o an extension and those with a 5 digit extension. For extensions with only 1-4 digits, the cell value gets displayed from the right to fill up the 5 extension digits first resulting in the area code portion & phone number getting messed up.
    So a cell value of 234555121298 is being displayed incorrectly as ()234-5551 x21298
    I don't want to use a multi-column solution, just 1 column for the phone numbers.

Leave a Reply

Your email address will not be published. Required fields are marked *