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 8. Total comments: 643

  1. 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!!!

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

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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.

  10. 00, what it's means?

  11. 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.

  12. 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

  13. 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!

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

  15. How to add comma in amount in sentence

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

  17. 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

  18. 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

  19. 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.

  20. 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.

  21. 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.........

  22. 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.

  23. 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.

  24. 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!

  25. 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.

  26. 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.

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

  28. 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.

  29. 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?

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

  31. Hi,
    Is there a way to format numbers, so that the digit of the decimals appear with a smaller size or a different color than the integer part?
    Thanks a lot!

  32. How can I use format cell custom in Likert Scale? My interest is that I should key in a number but the cell should read the scale. for example, 1=Totally Disagree, 2=Disagree, 3=Undecided, 4= Agree, and 5= Totally agree. My interest is to key in a number e.g. 4 but the cell should read AGREE.

  33. Cool!! Nice explanation, thanks

  34. Suppose i have a text "Apples" in cell A and a negative number (1,234,567.55)in cell B
    Now iam clubbing these two cells using concatenate formula while converting the amount in to Millions.
    the result iam getting is Apples $ -1.23 M.
    =CONCATENATE(A1," ","$"," ",ROUND(B1/10^6,2)," ","M")= Apples $ -1.23 M
    My question is how can i get the negative number into a parenthesis like
    Apples ($ 1.23) M

    1. Hello Ram!
      It is impossible to make a custom number format in the form you want. Therefore, you need to use text functions.

      =IF(B1>0,CONCATENATE(A2," ","$"," ",ROUND(B1/10^6,2)," ","M"), CONCATENATE(A2," ","($"," ",ROUND(-B1/10^6,2)," ",") M"))

  35. Hi guys. I just wanted to say thank you for an incredibly informative page about excel formatting.
    I am stunned to see that despite the quality and breadth of formatting info provided, so many desperate people still need to send you exotic requests for formatting help, and they are genuinely stuck. I think there is a general issue in need of a better solution than what excel offers. Food for thought!

  36. Got a special need whereby i need to format data that contains numbers in a special format

    If the there are 3 numerical digits i need to display them as 3 digits
    If more the structure would be 000/0000

    I will be using them for formulas containing product codes

    How would i do them baring in mind I need the content to be exactly as formatted above

    1. Hello Ahmad!
      Your problem can be solved by converting the number to text.

      =IF(LEN(A3)>3,REPLACE(A3,3,1,MID(A3,3,1)&"/"),A3)

      I hope this will help

  37. Hi! I would like the fraction part of mixed fractions to be smaller font (ideally like the symbol). Example: 3¼ instead of 3 1/4. Is there any way to do this in Excel?

    1. Hello Naomi!
      In Excel, the number format can be set only for the entire cell, but not for its part. If the cell contains text, then you can select part of the text and set a special format for it.

  38. Hello !
    I wanted to convert "a row consisting of numbers ending with either Dr or Cr at end " to row consisting numbers with -ve wherever Dr and +ve wherever Cr occurs.
    ex row 23 Dr -23
    52 Dr to be coverted as -52
    72 Cr 72
    thanks

    1. Hello!
      I’m not sure I understood you correctly, because your abbreviations are not clear to everyone.
      The following formula should work for you

      =IFERROR(--SUBSTITUTE(A15,"Cr","",1),-SUBSTITUTE(A15,"Dr","",1))

      I hope this will help

  39. I need a number format to show numbers like following
    ABC/20/I/461
    ABC/20/P/5420
    ABC/20/K/489
    Now the First 7 characters are fixed i.e., ABC/20/ , next character is an alphabet then / and then a number is to be written.
    What should be the number format if we can avoid typing ABC/20/ portion and simply type like
    I461
    P5420
    K489
    and those are shown as
    ABC/20/I/461
    ABC/20/P/5420
    ABC/20/K/489

    1. Hello!
      It is impossible to separate the text from the number directly in the current cell and at the same time add additional text using the format. Use VBA. Using formulas, this can be done. But you will enter data in one cell, and you will receive the result in another cell.

  40. Hi Team,
    I'm currently working on converting different formate, but I didn't figure out, How do I convert 1467:1 into number formate.
    Thanks

    1. Hello!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail.
      Please let me know in more detail what you were trying to find. Explain what you want to get - remove the colon, discard the characters after the colon, do the division, or something else?
      Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.

  41. Sorry this is what i want to ask if string is 20SCSE171398 i want 101398 , first two digit and last 4 digit, how to remove in between 6 characters or digit

    1. Flash Fill could do the work without using any formula. I really dont know how to explain it right but you can search it for yourself if you are interested.

    2. Hello Ravi!
      To delete and replace several characters in the text, the REPLACE function is used. If I understand your task correctly, the following formula should work for you:

      =REPLACE(Q1,3,6,"")
      =REPLACE("20SCSE171398",3,6,"")

      Hope this is what you need.

    3. 201398 i want

  42. if string is 20SCSE1398 i want 101398 , first two digit and last 4 digit, how to remove in between 4 characters or digit

  43. I'm putting together a sheet with different number-formats and examples for myself.
    A little question: there's a format #.##0,00 and a format #.##0,00;-#.##0,00 (XL2010)
    What is the difference between those two? If a negative number is formatted with #.##0,00 it just gets a minus sign as expected. Why then would one need #.##0,00;-#.##0,00 ?
    Thanks in advance!

    1. Hello Marcel!
      Each number format can have up to four sections, separated with semi-colons. This structure can make custom number formats look overwhelmingly complex. To read a custom number format, learn to spot the semi-colons and mentally parse the code into these sections:
      1.Positive values
      2.Negative values
      3.Zero values
      4.Text values

  44. I use a format such as:
    [<1000000]0.0000," Kilo";[ 100milli
    0.0001 -> 100micro
    0.0000001 -> 100nano
    etc?

  45. If custom format consists of 4 sections of code separated by semicolons then how do I create a custom format with more than conditions 4 conditions as conditions are also separated by semicolons?

    1. Hello Abhishek!
      Custom Format indeed consists of 4 sections separated by a semicolon. Each section has its own assignment: 1 – format for positive numbers, 2 – format for negative numbers, 3 – format for zeros, 4 – format for text values. A user-defined number format can also contain up to two conditions which are entered in square brackets, e.g. [> 100] or [<= 100].
      When you use conditions in a user-defined number format, you redefine the first and the second sections.
      For example, Custom Format [Red] [<100] 0; [Blue] [> = 100] 0 means that values less than 100 will be displayed in red, and more than 100 – in blue. However, there can be only two of such conditions.
      To apply more than 2 conditions to the cell format, you may try to use Conditional Formatting which is well described in this blog post.

  46. Hey I added the following formula _(* #'##0_);_(* (#'##0);_(* "-"_);_(@_) but now it set's numbers like 333 like this '333 too. Can I somehow modify this so it doesn't include these?

    1. Hello Codru!
      As a digit grouping symbol, Excel uses the symbol that is specified in Control Panel -> Clock and region -> Region -> Formats -> Additional Settings -> Digit Grouping Symbol.
      Unfortunately, it is not possible to change that symbol using a custom number format. Please change it to ' via Control Panel.

  47. Hello,
    The excel formatting information that you provided on this site is amazing and so helpful.
    Is there a formula to make 4 display as 0004000
    Thank you very much.
    Sharon

    1. Hello Sharon!
      If you want to add three 0 to the left and to the right of number 4 and the number itself won't change, then you can try to use Custom Format.
      Please go to Format Cells, choose Number -> Custom Format and set the followig format:

      000###0"000"

      I hope my advice will help you solve your task.

  48. Hi, I have whole that I need to change to currency format.
    For example, 10816, I need to convert to 108.16.
    What Custom formula would I need to use to accomplish this?
    Thanks,
    AL

  49. I want 123A as 1/23A in excel custom format. Pl help

  50. Hi there, what's the formula if i want the 206,508 to be shown as 2.06508? thanks!

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 :)