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 3. Total comments: 444

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

    1. Hi!
      In Excel's custom number format, only the thousands separator is provided, not the hundreds separator.

      1. yes, you are right sir., but i need separator in hundred place. can Is it possible to have a hundred separator.

  2. Hi,

    I'm trying to understand if this is possible - K12343-4-AB1-F888 ? Can I force excel inputs to be this specific string of alphabets, numbers and hyphens?

    Thanks, in advance!

  3. Hi, I have a cost sheet template that displays the cost of items with 2 decimal places, however, I am using the formula 0;-0;;@, to display any zero or null values as blank. However, I want to apply that formula to all fields, but where there is a value, I need it to display 2 decimal points, and I cant find a formula that can be applied to all cells to tell them to display zero if no value, but if there is a value, display 2 decimal places. thank you in advance

  4. Hello,
    I have a problem with prices.
    In all tables, my prices are like this: 2290 and are supposed to be 22.90
    Is there any way to fix them all together, not going one by one?

  5. Hi,

    I have $5,775 and want to display this as US$ 5,775

    Thank you in advance.

  6. How to remove text from cells with custom formatting like dilip.choudhury@ gmail.com will show as dilip.choudhury. I know this can be done with excel functions, vba..etc. Is it possible to do with excel custom feature option.

  7. Thanks for a great article!

    Is it possible to display a percentage value without the percentage sign using custom number formatting? I want the chart labels for each data point to be displayed without %.

    What I have: 65%
    What I want to display: 65

    I cannot change (like x*100) because it is used in the graphs and in other calculations. I also do not want to add an extra column with a new value.

      1. Ah, too bad! Thanks

  8. Hi,
    Can someone help me make my excel cell that looks like 12345, look like 1ha23a45m?
    That is the system for the area In my country.
    Even something like 1,23,45 could help a lot.
    I really appreciate any help you can provide.

      1. Try this on a cell containing 12345

        #"ha"##"n"##

  9. H,
    I am using [>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0
    to show the number as 1,11,11,111.

    Subtracting two number using above format, I will get the result in same format as shown below.

    A1=3,45,67,890
    B1=2,34,56,789
    C1=1,11,11,101 (A1-B1)

    But, i need to have plus (+) and minus (-) based on operation.

    Here C1 should show like +1,11,11,101 and D1 (b1-a1) = -11,111,101

  10. Hi,

    Hope you are well.

    i have data with 0, 10%, 30 (example) so when I do custom format to hide zero I have used 0;-0;;@ so it hides 0 but 10% became 0. Please advise what is the issue and how I can hide 0 and keep the rest number as it is.

    Look forward to your reply

    Thanks

    Razib

      1. Hi Alexander,

        Thank you for your prompt reply.

        I have tried #;-#;;@ but its hiding 0 and 10% both. I want to hide only 0 and keep 10% sales and other numbers same.

        Is there any other thought ?

        Look forward to your reply.

        Thanks
        Razib

          1. Hi,

            But both are in different cell. 0% is one cell and 10% is another cell. So can’t do custom format to hide 0 and keep 10% as it is ?

            Please advise

  11. Hello! Fantastic article, I have used it a lot in the past year!
    I have the following problem. I have a set of numbers in a column, some are integers, some are decimals. If I use something like #.##, then the integers appear with a dot at the end (ie. "42."), instead of just the number. Is there any way to make the decimal point conditional?
    (I am sorry if this has been asked already, I scrolled for a while but there are a lot of comments...)
    Thanks! Kalman

    1. Hello!
      Unfortunately, if you have selected a number format with a decimal separator, it will always be displayed.

    2. Ah never mind, I found the comment in the end where it is confirmed that it cannot be done :( It is weird though, one would expect to see whole numbers without a point in the end. I constantly need to explain this behavior to users which is annoying.

  12. In practical, I am using this method often (eg. #,##0,↑;[red]#,##0,↓;-). Unfortunately when ever I copy-paste into PowerPoint table, the Font Color format can't be copied into the PowerPoint.
    I checked using vba script of the cell target using script "rng.Font.ColorIndex" the result seems is not match with the shown font color that perform by Number Format.

    Is there any vba script to verify the color shown by Number Format?

    1. Hello Alexander,
      Thank's a lot for your fast response.

      I've been trial GetCellFontColor function to get the color code of a cell that use custome number format below,
      number format : #,##0,↑;[red]#,##0,↓;-
      trial number1 : 1000 (shown font color black). Validate using GetCellFontColor, Result = 0
      trial number2 : -1000 (shown font color red), Validate using GetCellFontColor, Result = 0 (instead 255 for red color).
      It seems the GetCellFontColor is using xlRange.Font.Color that work on cells, It can't detect the font color that produce by custome number format.

      Do you have any alternate function that can validate font color that produce by custome number format?

      I'll be greatfull for your advice.

  13. Great article - thanks. So much information here that you can't find elsewhere.
    I'm having trouble with a custom format. I'm trying to show a number in 4 digit format with a letter at the end. For example "50A" would show at "0050A", or "180C" would show as "0180C".

    Obviously if i remove the trailing letter from the cell i can get the 0000 custom type to work for the number alone, but when putting the trailing letter back in I don't know what to change my custom format too to also show the letter as well but also stay in the 4 digit format. Can you help?

    1. you can use custom format. just right click the cell and after your number format put "Aa".

      I.e. I wanted to calculate hours worked but I wanted "hrs" to show up after the numerical part of the result, so in custom I put: 00.00"hrs". If I wanted a space between the number and hrs I would just include a space between the last 0 and the quation mark like: 00.00 "hrs"

    2. Hello!
      Your data is text, not a number. Therefore, the 0000 custom format cannot work here. You can get the value you need in another cell using text functions.

  14. Hi - Really informative article - appreciate all the details.

    I'm wondering if there is a way to hide blanks for currency, but have it apply to multiple currency types. My document displays either $ or € based on some other conditional formatting I've got built-in, but I can't seem to find a way to hide cells with a value of €0.00... it only seems to work for $0.00.

    Under cell formating, using custom formula, I'm using $#,##0.00;($#,##0.00); which I imagine only works for those cells with the $ currency type - is there a way to make this formula or something similar apply to both $ and €?

    Any help would be appreciated.

    1. Hello!
      €0.00 values will not be shown with this custom format:

      €#,##0.00;(€#,##0.00);

      Hope this is what you need.

      1. Hi - Thank you for the quick response!

        This seems to work only if the cell is in €. In this use case, the cell this formula is applied to varies between either $ or € depending on other conditional formatting variables. Is there a single formula/format that supports hiding multiple currencies or is that not possible?

        Thanks again.

  15. Hi! I have a series of fractions in 1 row that I would like Excel to convert to the percent format in the next row. How do I do this?
    Thanks!

  16. Hi,

    Looking for a custom number format that shows 450 as 0'5 and -450 as -0'5 (in red).

    0.0,;[Red]-0.0, shows 0.5 and -0.5, but need to show an apostrophe.

    Thanks,

    Simon

  17. Hello,

    I have an excel sheet that counts the number of data and reflects it on a pi-chart. however, i found it better to reflect zero cells as #N/A in order to not let it be visible in the chart until it gets a count.

    is there a way to format #N/A (basically error cells) as "TEXT" instead?

      1. As I am using the cell value in a pie chart I would like to exclude the zero values cells from being shown in the chart and found out the best way is to have the zero cells as #N/A. however, I would like to change the cell appearance to show "ZERO" instead of #N/A while keeping their values.
        I have tried using IFNA and ISNA functions in the Formatting and in the Conditional Formatting but it didn't work out for me, is there any other methods I can try to get this result?

          1. Actually what I am trying to do is to change only the appearance of the cell to show "ZERO" while maintaining the value of the cell as #N/A.

      2. is it possible to use these functions in the formatting? because i would like to keep the cell value as #N/A

  18. Hi,
    Thanks for the great page. Very through and structured.

    One query, though.
    Take the number 27618263177.12

    How can I custom format the cell so that it is shown as 2761.83. Basically, what I mean is I wish to use the Crores format.

    1. Hello!
      In custom Excel formats, you can show the number in thousands or millions. Unfortunately, your task does not match these possibilities. You can apply to round of numbers as described in this tutorial.

  19. Hi,
    Very good blog. Thank you.

    My problem is: I am dealing with a lab measurement. I need conditional decimal point length depending on another column. As follows

    if A1 = 0.001 C1 = 6.000
    A1= 0.1 C1= 6.0
    A1= 0.010 C1=6.000
    A1=0.02 C1=6.00

    Kindly Help me. Thank you

    1. Hello!
      Unfortunately, a custom format in Excel cannot use data from another cell. Your problem can be solved with a VBA macro.

  20. hey i want to get the number format where i can fix the number and change the values after decimal points .is this possible with custom format please reply me.for example i want to fix 12 then if i enter 75 then i want it as 12.75.

  21. Hi, How are You,
    I need to custom number format code in below format ( 12 Nuber )
    123456789025 Change of 1XXX XXXX 9025
    Pl. provide custom codeing
    thanks for help

  22. I deal with measurement units. Displaying the unit tells the reader what the number means. For instance, the format #,##0,, "MHz" will display the number 100000000 as 100 MHz. So I know the number means frequency. Similarly, I can use the format #,##0, "km" to display distances. I would like a way to display time so that the number 0.0015 is displayed as 1.5 msec or 1500 microsec. Is that possible?

  23. Thanks for your comprehensive article.

    Is there a way to extract a useful cell format from a referred to cell? If i do a =CELL("FORMAT",A1) and A1 is formatted Accounting with 2 decimal places, AKA (almost) #,##0.00 the value returned is ",2"

    ",2" is not useful in a =TEXT(A1,CELL("FORMAT",A1)) formula. I have to use a xref table to keep the formatting.

    So I want Excel to return #,##0.00 instead of ",2" so I can ditch the xref table...

    Thanks!

  24. Hi Alex,
    I need assistance in excel format. I have variance column which is column A minus column B. The result is rounded to zero decimal point using excel format.
    Now I dont want the variance to appear as Zero. If I have variance as 0.123 or 0.56 it should appear as dash (-) insted of Zero. Negative numbers should appear in red under bracket (15) or (-).
    Do we have any format option here.

    1. Hello!
      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. [> 1] or [<= 1].
      When you use conditions in a user-defined number format, you redefine the first, second and third sections. For example, Custom Format for you -

      [>1] 0;[Red] -## ; "-"

      1. Hi Alex, thank you for the format. It works however i just want to understand what can be changed in above format so that if my number is -0.234, that also should appear as dash under bracket (-) insted of (0). The above format is working fine for positive & negative number and even if I have positive 0.234 its giving me dash insted of zero. But I need to keep an eye everytime when the output is in negative 0.234 so that it can be formatted as (-).

  25. I have an Excel column with over 70,000 entries in this format: 123456789A12345. All of them are nine numbers followed by a letter, followed by five more numbers.

    I'm trying to get it into the a format like this: 1-23-456-78-9A-12345. I have typed in a custom format of #-##-###-##-#-##### but it only works if there were no letters and every cell has the letter in the tenth position. Is there a symbol, like # that will allow the letter to be used in my custom format? Thanks.

    1. Hello!
      You cannot apply number format to text. Try this formula:

      =LEFT(A1,1)&"-"&MID(A1,2,2)&"-"&MID(A1,4,3)&"-"&MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,5)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)