Comments on: Custom Excel number format

Custom number formatting in Excel is a very powerful tool, and once you learn how to use it property, your options are almost unlimited. This tutorial explains the basics of the Excel number format and provides the detailed guidance to create custom formatting. Continue reading →

Comments page 7. Total comments: 643

  1. HI Dear expert community,
    I'd like to get your advise, is it possible to add more that 1 conditional under Format Cell /Custom, I have this:
    [=1799.99]"Door";[=1200]"Box"

    but would like 2 add another to variables, I tried this, but didn´t work:

    [=1799.99]"Door";([=1200]"Box";([=1800]"Side";[=2400]"Top")
    [=1799.99]"Door";[=1200]"Box";[=1800]"Side";[=2400]"Top"

    Could anybody give a hint?
    I appreciate your kind support :D

  2. Very Detailed Article. No Words to appreciate....
    Very Helpful...

    Thanks for such a long article...

  3. Hi i am aware of the , and ,, rounding to thousands an millions in number formatting.

    However is there a way to round to the nearest tenth or hundreds in number formatting? I would like to preserve the original value in the cell (instead of round/up/down)

    e.g. value 2431 display as 2430 (to nearest 10）
    Display as 2400 ( to nearest 100)
    Thx.

      1. So which format code should I use to SHOW in tens?

  4. how can I split MP53HA2219 to MP 53 HA 2219 (needs to insert the space) {format will be same always}

  5. I solved the problem partially by holding the Alt key and entering 010, however, if I reduce the wide column, I have XXXXXXXXXXXXXXXX instead of the value...

  6. hello Alex

    I need to wrap the text in custom formatting as the column becomes too wide and spoils the excel sheet, is there a way to do that.

    this is what I have:
    "TOTAL PICK AISLE LOAD ("#,##0"kg/m2)"

    The column width is for the full text, I need to some how wrap it with in the custom formatting not from the tab as it does not work...

  7. Hi,
    I am using Excel with the region set to India. The numbers display as 1,00,000 by default. I want to change a few cells in this to display in the million 1,000,000 format.
    The default setting of #,##0 does not work and it displays as 1,00,000. Looks like putting in this format defaults to the default setting.
    Any suggestions? I do not want to change the region.
    Thanks.

  8. Thank you so mush for this complete and useful tutorial. What is the difference between #,##0 and #,# for thousand separator?

  9. Thanks, this info really helpful :)

  10. Hi, I'm trying to format my columns to where I type.25 and it turns to 0.25% but I keep getting 25.0% can anyone help please?

  11. Hi Alexander,

    I need to transfer this format 1.234-567.8 to a formula that I can use in Format cells Custom type. I then want to apply the custom formula to several cells with 8 digit numbers (e.g. 12345678) at once.

    Can you help me provide me with such a formula?

    / Robin

    1. Hi!
      Digit grouping symbol is set in Local Settings in the Control Panel. However, it is not possible to use several different separators. Also, it cannot be the same as the Decimal symbol. I'm really sorry, we cannot help you with this.

  12. Hello, great article. I want to have different currency formats, sometimes it needs to be comma for thousand separator and sometimes period, how do I do these number formats?
    Thanks!

    1. Hello!
      The thousands separator is set in the Windows Control Panel under Local Settings. There can only be one.

  13. how do we add colors with conditional formatting for more than 2 conditions. As i tried only 2 conditions are allowed in format axis eg.[Red][<=200]General;[Magenta][<500]General; how do we add more colors for different intervals?

  14. How do you convert custom format results to text? Can it be done in any simple way?

  15. Hi,

    Great tutorial.

    Is there a way to write a custom number format that removes decimal places while preserving the remaining syntax of a cell's accounting number format (including currency symbol and alignment, where the currency type is unknown?

    I am trying to create a conditional formatting formula that removes decimal places after a number exceeds a certain value, independent of whatever accounting currency style is in effect. I can't use VBA for this application.

    Thanks,
    Lloyd

    1. Hello!
      This format does not show the decimal part of the number if the number is> 99.

      [>99] $#,##0;$#,##0.00

      Pay attention to the following paragraph of the article above — Custom number formats based on conditions.
      I hope I answered your question.

      1. It looks like there is no way to re-format decimal place while preserving currency symbol without knowing the active currency symbol. I wish nested conditionals was allowed.

        I'll restrict to default currency symbol.

        Thanks for the help.

  16. Hi!
    I have a number consisting of nine to eleven digits, for example 12345678000.
    The first two digits have one “meaning”, digit number 3 and 4 have one "meaning" and digit number 5 to 8 have one "meaning". To improve the readability of the number I would like to group the numbers, with the groups separated by blanks/a space.
    I would like to show the number as follows: 12 34 5678 000
    I have tried number format ## ## #### ###, that works perfect as long as there are exactly three digits after digit number 8, for example 000 as above
    It there are two digits after the eights digit – the number is for example 1234567811 it is grouped like this: 1 234 567 811
    If there is one digit after the eights digit –the number is for example 123456781 it is grouped like this: 123 456 781
    What can I do in order for it to start counting the characters from the left and always grope the two first digits together, then the next two and then the next four, followed by the rest?

    1. Hello!
      You can apply this format if you convert a number to text:

      =LEFT(A1,2)&" "&MID(A1,3,2)&" "&MID(A1,5,4)&" "&MID(A1,9,20)

      I hope it’ll be helpful.

      1. Just wanted to let you know that while you haven't responded to my question (see above, March 9), this response to Anders provided a viable solution. So thank you!

        1. Hi,
          The thousands separator is set in the regional settings of Windows. But you cannot make the first separator "/" and the second " ". To do this, you need to convert your number to text and insert the necessary symbols into this text.

  17. Can a cell be referenced in custom formats? I want to combine the number in cell A4 with text when a date is entered in that cell.

      1. I didn't think so. Thank you!

  18. 45 x 48
    i want to do = 45 x 48 ft

  19. Is there a way to format using slashes and commas?
    Basically, I'm trying to format something with a 2 digit code, then a slash, then a 6 serial number separated by a comma.

    Here are some examples:
    16/123,456
    11/955,416
    22/546,000
    10/000,000

    I tried "00\/000,000" but that gave me a comma before the slash: "16,/123,456"

    Any thoughts?

    Thank you

  20. I need excel number format which shows fraction if there is any fraction value. If fraction value zero value should be appear without fraction.
    For eg: value is 25.50 it shows 25.50. if value 25.00 it shows 25

  21. Hi,
    Thank you for this. However I am stuck on something.

    I have numbers in the millions in a range of cells:
    Ex: 3,704,268.16 or -4,440,823.49

    I am using a format to round to the 000's

    #,###,; (#,###, )

    Which gives me a result of
    3,704 & (4,441)
    which is perfect

    but what's driving me crazy is when I have a formula within the cell and it nets a very low negative number I get this as an result: ( ).

    Is there any way of removing those brackets? when I run into a situation like that?

    Thank you in advance. I love the article its super useful!!!

  22. I want to write 22 and excel to show 0.22

  23. Thanks for sharing. This is super useful. Related to the "Indent" section, as it relates to text, this really isn't an "indent". At at least in doesn't behave (in 365/v16) the way manually adding indents does. It just adds space on the 1st line of wrapping text. Not to all lines.

  24. Hi
    I have a question about custom format cell , Which codes or characters in custom format cell should I use that when I copy a formula from another cell and past in formatted cell then the result of formatted cell (displayed number) does not change and main as before pasting?

    1. Hello!
      The information you provided is not enough to understand your case and give you any advice. Please describe your problem in more detail. Are you copying just a formula or an entire cell? To preserve the formatting, you need to use Paste Special - Formula or Value.

      1. hello, thanks for your help
        Suppose we have a table where the third column is the product of the first column multiplied by the second.
        a b a*b
        2 3 6
        6 3 18
        5 4 20
        3 5 15
        3 6 18
        2 7 14
        Then we change one of the cells of the third column using the custom format cell and the character “apple” as follows.
        a b a*b
        2 3 6
        6 3 18
        5 4 20
        3 5 apple
        3 6 18
        2 7 14
        Now if we copy the second row cell of the third column and paste it in the fifth row cell of the third column, the word apple changes to the number 15.
        My question is what character instead of “apple “ to use in the custom format cell that does not change the word apple after copying the second row of the third column and pasting in the fifth row of the third column?

        1. Hello!
          Your explanations are not very clear to me. But I can assume that you want to print the word before the number written in the cell.
          Use the format

          "apple " ##.##

          Pay attention to the following paragraph of the article above Add text to number formats
          Hope this is what you need.

          1. Thank you for your guidance.
            But that was not what I meant. In fact, I might ask the question, is it possible to define a format for a cell that contains a number that when we paste a formula into it , the number does not change in the cell ?

            1. Hi,
              A cell can contain either a formula or a number. The formula calculates the number to be displayed in the cell.

  25. Hi,

    In my country its not thousands(3 decimal places) , Mill (6 decimal places) and Bill(9 decimal places).
    Its thousands(3 decimal places), Lakhs (5 decimal places) and Crores (1 decimal places).

    So how to place the decimal place through custom formatting to convert it into Lakhs or Crores (Not at the same time obviously.

  26. I want to display like below

    10 to be displayed as 000000000000010
    -55 to be displayed as 000000000000-55
    12.22 to be displayed as 000000000012.22
    -23.33 to be displayed as 000000000-23.33

    1. Hello!
      You can use custom number format

      0000000000000##.##;0000000000000##.##

      Hope this is what you need.

  27. How do I display 4 left-most digits? I.e.
    123456789 should be displayed as 123400000
    0.123456789 should be displayed as 0.1234
    0.0001234567 should be displayed as 0.0001234

    Is this possible? Thanks.

    1. Hello!
      To extract the first 4 digits (other than 0) from a number, you can use the formula

      =LEFT(A1,MIN(IFERROR(FIND({1;2;3;4;5;6;7;8;9},A1,1),99999))+3)

      I hope my advice will help you solve your task.

  28. Good afternoon
    I have been studying custom number format but I would like to know what I am doing wrong in this exercise, please.
    A number greater than 1000, such as 52411, will be shown as $52 and a number less than 1000 will be shown as $0, also I need to have a custom format for negative numbers between parentheses.
    I tried to apply this format: [>=1000]$0, ;[<1000]$ "0" and it works for positive numbers, but when I try to format negative numbers Excels displays me an error message. What can I do to apply this format to negative numbers?
    Thanks

    1. Hello!
      To display numbers in thousands, use the custom format:

      [>=1000]$#,##0, ;[<1000]$"0"

      But in this formula, you cannot set a special format for negative numbers. Excel only allows one condition. You already have two.

      To show negative numbers in parentheses, you can apply the format

      #,##0;(#,##0;0

      I hope it’ll be helpful.

  29. I have whole numbers and some with a single decimal points. If I use say ##0.# if get "6." and "6.5", but would like "6" not "6." Is this possible to remove the "." with just formatting?

    1. Hello!
      If you want to show the fractional part of a number using formatting, then you need to use the Decimal point. Unfortunately, it will always be displayed.

  30. 00, what it's means?

  31. Is it possible to use some mathematical operations to format a number? For example, I want to display a year (e.g. 2012) as both the year and the number of years since a certain event (e.g. birthdate). So a date in 2012 would display as something like "2012 (27 years)", where the 27 is calculated as 2012–1985. I know how to do this using Excel formulas, but I'm trying to display this on a graph and want to maintain the numbers as dates, not text, so Excel knows how to display them properly.

  32. Hi,

    Great guide! Now that I have the format I desire, I want to apply it to excel charts. However I don’t get that to work, neither by pointing at already formatted values nor pointing at regular values and applying the format on the axis/label. Any idea why this doesn’t work?

    Thanks!

    //Patrik

  33. Hello!

    First of all, great guide, thank you!

    I encountered with the problem with my custom format #,##0.00 and numbers that have decimal places in format x.0x. Meaning that once I have number e.g. 1234.05 it fails and displays number e.g 962.587.00

    Do you have any idea what caused it and maybe how could I fix my custom format?

    Thanks!

  34. ="Total value A+B+C+D = Rs. "&L55&"/-"

  35. How to add comma in amount in sentence

  36. 04/01/5021
    How i can change this into 04-01-5021 format

  37. In a column is a large number of numbers
    How do I color each number with a bold font, a different font color, and a different border from the other, with repeating the format if the number is repeated
    Note that the numbers from 1 to 3000?
    Thank you

  38. Hello!
    how to create format cell by refer other cell
    example, product "apple" code "####.##" if the product is different "banana" code "#.####" in the same cell that need to key in

  39. The separators are not correctly separating the number. Not sure how to fix it. I tried formatting it but it kept on putting the separator in the hundreds place. For example: 1234567.89 when I format this number to include separators it's showing as 123,45,67.89. How can I fix it? I checked the setup also (File>options>Advanced) but wasn't able to fix it. Please advise.

  40. how can i write "ABCDE-1234Z" when I type 1st five letter is word then hyfan then four letter is digit and again last letter is word??

    1. Hi,
      If the cell contains at least one letter, then it means text. The value in the cell is left-aligned.

  41. I have data in IT2019111515691204569 and need to change into text format as

    IT-####-#####-##########

    Please help..

    1. Hello!
      It is impossible to solve your problem by formatting. You can use the formula

      =LEFT(H1,2)&"-"&MID(H1,3,4)&"-"&MID(H1,7,5)&"-"&MID(H1,12,10)

      1. Thanks A Lot.........

  42. I'm trying to add and "*" before and after a number "*"####"*" which works (ie *1234*) but my number has a - in it like 1234-6 when I add the - the * goes away. How do I format this? This is what I'm looking for *1234-5* with no spaces. I don't want to format without the - because the number after the - could be a 2 digit number and the format would only take the last number, like this "*"####-#"*". My number already has the - in it.

    1. Hello!
      You should understand that 1234 is a number and 1234-5 is text. They need to use different formats.
      For text —
      \* @ \*
      or
      \*@\*
      For number - replace the @ symbol with #.
      You can also convert your numbers to text.
      I hope my advice will help you solve your task.

  43. Hi,

    My actual text is "Scheme85" but wanted to appear as "Old Scheme85" without changing actual content of the cell, just for visual purpose...

    Is there any format which can convert my actual text into customized display without changing actual context of the cell?

    1. Hello!
      If I understand your task correctly, you can use custom text format

      "Old" @

      I hope my advice will help you solve your task.

  44. Hi to all,

    I need to use this kind of format for example: (12.5 = 12 + 500 ) it is possible?

    1. Hello!
      You have written a mathematical expression. Moreover, this expression is mathematically incorrect. What does the cell format have to do with it?

      1. We do something similar with surveying and station numbers. For example, a location 13,758 feet from the beginning would be labeled as "13 + 758"
        To make a format like that in Excel, use 00 + 000
        For the question you had above, just write the 12.5 as 12500 and it'll display as 12 + 500
        The biggest issue with what you wrote out is the decimal point. I don't know of any way to make that disappear. You could do 00. + 000 which would display your 12.5 as 12. + 500 That "." is annoying though.

        If you have a long line of numbers formatted as the 12.5, you could put a column that multiplies that column by 1000 and then copy that and paste the value into the original column to overwrite the 12.5 with 12500. (To paste the value hit control + alt + V and select "Values" (or press v) and it'll paste the value rather than the formula)

        Hope it helps!

  45. i have a doubt if i want a variable preceding a constant such as ###/20-2021 in this case ### is a variable like 001 or 002 or ....... 1999 , etc and " /20-2021 " is a contant . when in drag the cell only variable should change.Kindly Help me.

  46. I should have done more experimenting. You can take out the blanks between format conditions. This Code allows for the minus sign up down to Millions:

    [>999999] $#0.0,,,”B”[>99999]$#0.0,,”M” ;[<999999]$-#0.0,,,”B”[<99999]$-#0.0,,”M”

    And it is 81 characters in length. If your number range stays below Billions, then simply change out the 'B' to 'M' and 'M' to 'K' etc. In other words, you can handle two different 1,000 multiple ranges of values with this Format Code structure.

  47. CORRECTION. Excel limits the Format Code to 83 characters, not the number of 85 I posted earlier.

  48. So after some experimenting I discovered how to have multiple formats for Billions, Millions, and Thousands. Here is the solution:
    [>999999] $#0.0,,,”B” [>99999] $#0.0,,”M” [>9999] $#0.0,”K”
    Note that a space is between each format condition (For example, ..."B" space [>99999]... allows for the millions to follow the billions labeling.). By having only this, then all others (negative) will follow the same format. I tried to get the negative but Excel limits the format code to 85 characters. A key piece of information to know.

  49. Hi, I'd like to type in a MAC address (12 Charactors A-F 0-9) an have it display in the format 44-85-00-B2-5E-3B
    So I'd type in 448500B25E3B and the cell would display 44-85-00-B2-5E-3B
    How can this be done?

  50. A2 = x
    A3 = $
    B2=2
    B3=3
    can i custom format with formula like B2= 2x, B3=3$ B2 +B3 =5

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)