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

  1. How do I format a percentage to show the “0” in front of the decimal.

    Example when I type in .25% it shows up as 25.00% but when I type in 0.25% then it shows up as 0.

    I’m trying to make it that if you type the “0” prior to the decimal that it will not matter either way

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

  3. Please suggest which formula is required for customer formatting in excel.

    I want custom formatting in text & number with general.

    I want to display in excel cell like 0 - 5 KM OR else 0 - All KM

    "0 - "#0" KM";"0 - "General;@" KM" this formula is correct or not please suggest.

  4. Thx.
    Can I create no format as follows:
    10,00,00,00,000 ie. last should be 000 all others 00,00,

  5. I have huge data in one column in excel in custom format (like 40000.00Cr or 5000.00DR) I want to convert the Cr amounts into negative.
    How can i convert?
    Please Guide

  6. Is there a way to use value of a cell in custom formatting.

    Let's say I want the number to appear with a prefix created by the user. For example:
    1. Cell A1 value = "A"
    2. The formatted cell has 3.01 value.
    3. I would like the number to be formatted as : "A3.01"
    4. If the value of A1 changes to "B", the formatted cell should display "B3.01"

    This does not work : [A1]0.##,[A1]-0.##

    1. Hello, Pawel,

      I'm afraid that it's impossible to use any formulas in custom formatting.

      However, as a workaround I can advise you to try and use conditional formatting instead.
      You'll be able to set the rules that will change the value in one cell depending on the other one.

  7. Hi! Is it possible to change the colour of one of the labels in X axe?? They are formated as text and I need so mark one of them (as it's a special case).

  8. I figured out how to change 131430026910035 into 13-143-0-0269-10-035 which is what I need for one spreadsheet. For another spreadsheet I need the same number but without the first 6 digits. 0269-10-035 Is there a way to do this?

  9. Very Helpful Thank You

  10. Help please, I need to format cells, so when i enter in 12344444 it will show 1234.44.44, can this be done? Its just a reference number

    1. Try This 0000"."00"."00

  11. Hi Svetlana,

    This is a great help, thank you very much.
    I followed the instructions and I have now the following format
    #,##0.##;(###0.##);"-"

    My problem is that when I have an integer it displays with a period

    Example:

    15 is formatted as 15.
    14.3453453 is formatted as 14.35
    -12.1 is formatted as (12.1)
    0 is formatted as -

    Would you be able to help with that?

    Many thanks

  12. This is all fine, except I need a single custom format that will display a certain number of digits for both positive and negative numbers:
    0 <= ABS(value) 9.99)
    10 <= ABS(value) -10.0, 10.0 -> 999.9)
    1000 <= ABS(value): 0 digits (e.g. -1000, 1000)

    I have read several articles and I have not found how to apply a single format to accomplish the above.
    Thanks,
    Jim

  13. Hi everyone!

    I have an issue with Fraction in Microsoft Excel 2016 when I want to calculate fraction of, an example, 0 and 7.

    Excel calculate it as 0 but I want to calculate it as 0/7 so I don't need to write it manually.

    Can anyone help me?

    Take you a lot!

    1. Give example of other things that work, your question doesn't make much sense alone.

  14. I have string of text/numbers in the form "A123412123456" that I would like displayed as "A1234 12 123455" with the spacing as indicated. Can anyone help?

    1. It is text so would be formula if original is in A1:
      =LEFT(A1,5)&" "&MID(A1,6,2)&" "&RIGHT(A1,6)

  15. hi i need help please!
    i have a coding structure and want output to be ###-###-### in the same cell
    so for example:
    1-234-45 >> 001-234-045
    1-2-3 >> 001-002-003

    can anyone help?

    1. It's not neat, but if your three numbers are in A1:A3:
      =IF(LEN(A1)>3,NA(),IF(LEN(A1)=1,"00"&A1,IF(LEN(A1)=2,"0"&A1,TEXT(A1,"@"))))&"-"&IF(LEN(B1)>3,NA(),IF(LEN(B1)=1,"00"&B1,IF(LEN(B1)=2,"0"&B1,TEXT(B1,"@"))))&"-"&IF(LEN(C1)>3,NA(),IF(LEN(C1)=1,"00"&C1,IF(LEN(C1)=2,"0"&C1,TEXT(C1,"@"))))

  16. Hi I am able to hide the cell value with format cell >> Custom >> Type after adding three times semicolon but I want if any one take cursor on that cell then value should be visible. Please help.

    Thanks & Regards,
    Ajit

  17. Hi how do i write a custom number format for numbers like this
    6.8.3.5.1
    6.8.3.5
    6.8.3
    6.21.1.3.2

    it does not want to sort it
    please help

    1. Are those the original numbers or what they are supposed to be? Explain what they should be.

  18. When rounding to zero decimals, I want 0.4 and (0.4) to display as a "-". I'm using the following Custom format:

    [>=0.5]_(* #,##0_);[<-0.5]_(* (#,##0);_(* "-"_);_(@_)

    The positive 0.4 now displays as a "-", but the (0.4) displays as a "- -"...

    How do I get it to work for the negative?

    1. This worked for me in Excel 2010.

      [>=0.5]_#0_ ;[>=0]"-";#,##0

  19. How can I show the amount like following .
    left aligned dollar symbol and right aligned the amount
    $ 1000.00
    in a single cell in excel ?

    1. Hi Sengolraj,

      Simply apply the Accounting format to the cell.

  20. How can I make date in
    Arabic Numbers in Excel work sheet
    Please help me Brothers

    1. Use this code:
      [$-2000401]0
      or
      [$-3000401]0

  21. Hi

    Thanks for the page.As you said,holding ALT key and pressing ANSI code, say 0176 for degree symbol returns a error sound.any other method for format numbers with special characters? i'm using office 2010 version.

    1. Make sure you are typing on the numpad. And make sure the numlock is on.

  22. I have a number 1.026596 but I'm wanting to format it to read 1026.596 so what Custom format would I use??? Thanks!

    1. Hi Phil,

      I am unable to find any format to change decimal place however you can use below formula.

      Imagine Your value is in A1
      so paste this formula in A2 Cell.

      = CONCATENATE(LEFT(A1,1),(MID(A1,3,3)&"."&(MID(A1,6,10))))

      1. There are a few issues I'd have with the suggested formula:

        * What happens if the length of the whole number portion is longer? This formula depends upon the decimal being the second character.

        * This formula also converts the value to text. If you want to use the value elsewhere, then you have to convert it back to a number.

        Just multiply the A1 value by 1000 and the decimal place will be moved.

  23. What about if I want to a format code to do the following:

    7, 0
    (As many countries use commas instead of decimal points and even have a space following the comma)

    Thanks!

    1. File > Options > Advanced > Editing options > Use system operators > select "," as the Decimal separator

  24. How do I get the result of a formula to display as currency?
    I have already formatted the cells as currency, they are all the same.

    This is my formula. I am starting with a base number, not in a cell.
    =IMSUB(390,H19)

    The number in cell H19 is a currency amount, and formatted as such.
    my result is 240, but it will not show up with the '$' no matter what I try.

    1. ;;;$@

  25. hi, i want to change the thousand separator for Style 1,234,633.53. when I have tried as per the suggestions above..I can only change as 12,34,633.53.

    Please help me out..

    thank you..

    1. HOW TO CHANGE THE NUMBER FORMAT AS 54,64,789.25

  26. Great job making these instructions! I easily found what I needed for a change! Kudos.

  27. hi,

    when i am trying to make a histogram out of my data , in x- axis it is showing two values with a comma in between , i want to replace dash(-) in place of comma(,)

    can anyone help?

    thanks in advance

  28. Hi

    if we enter in cell value as a 88 55 68 means Lenght width height should show in cell L88xW88xH68 any formula is there? Please help me

    1. If they are in cells A1 B1 and C1 then ="L"&A1&"XW"&B1&"XH"&C1

  29. How can format a cells to read same digit,I be an excel sheet with numbers in four digit,now am crossing to 5 digit and keeps rearranging it bring eg 10000 before 1001

  30. Hello all,

    I want to create an Excel sheet with user names and Passwords which can be opened by multiple users. However, I do not want them to see the passwords entered there.

    Example:

    User ID: User 1
    Password: AB12

    Instead of seeing AB12 in the cell, I would like to see XXXXXX or ****.

    Is that possible?

    1. Reconsider storing usernames and passwords in Excel.

      Otherwise, just type
      ;;;XXXXXX
      into the format field.

  31. How to write in excel cell like password in any website login.
    Want to write : 123456789
    Look like : *********
    Reply if any formatting available for it or any function.

    Thanks in advance.

    1. Reconsider storing usernames and passwords in Excel.

      Otherwise, just type
      ;;;*********
      into the format field.

  32. All of my excel spreadsheets seem to be corrupted. When I go to the format cell, the only option given me is negative numbers in Numbers and Currency. What happened and how do I get back to the default Format?

  33. how to place thousand separator as follows:
    the number is 19 lacs, 05 thousand, six hundred fortyfive and zero paisa

    that should be 19,05,645.00

    thankyou

    1. HOW TO CHANGE NUMBER FORMAT AS 12,54,780.34

  34. Hi

    I have a column of about 2000 numbers. How do I change the number format from, say, 11709 to 1170/900?

    Thanks

  35. Hi dear Svetlana,

    some times when I writ number in a cell, the number automatically changes to date.
    how can I solve this problem.please help me.
    thanks

    1. right click the cell and go to number format
      choose general

  36. sir, want to display A = 22° 05' 17.10 with increase A,B,C,D......
    i use the custom formula is #° 00' 00.00 for 22° 05' 17.10.

    please help me

  37. Hi,

    Hi How can I enter format like x.xxx-xxx.x (digits only)
    For example--------------------1.234-567.8
    in excel.

    plz guide me on the above.

    1. 0"."000"-"000"."0

  38. I have a mixture of digits and numbers and would like to format cell to have leading zeros
    eg. 12365L to show automatically as 00012365L

    1. Press CTRL+F or Ctrl+P, then go to
      number, search Custom, copy this code

      00000000"L"

    2. I am also seeking a solution for leading zeros with a mix of digits and numbers so I can sort by number, rather than having all text-only entries at the end. For example, I want the following data: 001, 001A, 001R, 002, 002R to sort in that order. If I format as text, my leading zeros disappear and if I format as a number using 000 as a custom format to maintain the leading zeros, the data sorts as follows: 001, 002, 001A, 001R, 002R. Any suggestions would be greatly appreciated!

  39. How do I put commas in the numerator and/or denominator of fractions? For example, 23 3,289/4,536. I tried various combinations of #, 0, and ? but to no avail.
    I tried:
    # #,###/#,###
    ? ?,???/?,???
    # #,##0/#,##0
    # ?,??0/?,??0
    and others.

  40. How do I make it so that:
    Zero = Black
    Less than Zero = Black, in brackets, thousands separator, no decimals
    More than Zero = Red, no brackets, thousands separator, no decimals

    I think I am close but not quite:
    [Red][>0]#,##0;[Black][<0](#,##0)

    1. Use this code, nice to see
      [blue][>0]"J";[red][<0]"L";General
      change the Font type to Wingdings

    2. Add this
      ;General
      So it becomes:
      [Red][>0]#,##0;[Black][<0](#,##0);General

  41. I have to create a custom format for all of these but I just dont understand the book? May someone please help....

    1.3217874955 i need to create a custom format to look like this 321.787.4955
    2. PN-25831 i need to create a custom format to look like this PN-025769
    3.110 dollars and .9 cents i need to create a custom format to look like this 340 dollars and .90 cents
    4. -150 150 loss its red though

  42. This has been helpful but hasn't quite hit the nail on the head for me. I have a cell that is displaying numbers through vlookup, some of the numbers are whole numbers and others have a decimal place. I would like the whole numbers to show as whole numbers without a decimal point. I also would like to show a unit with it. It seems I can do either one or the other but haven't figured out how to get both into one format. Is it possible to do that?

    1. Immediately figured it out, just had to have General "unit" after the semicolon.

  43. I work for an engineering company and the engineers requests can be quit specific. I have searched and searched to find the code to make diagonal stacked fractions that show the numerator as superscript and the denominator as subscript. Can you help with this.

  44. How can I trim only the decimal part from my figure.
    EG: If the # is 1425.50 or 1425.00 I need only the decimal like 50 or 00

    1. =MOD(1435.50,1) will give you the "remainder" of the number as divided by 1 (the argument in the MOD), so you will have .50.

      If you need it to be 50, then it's =MOD(1435.50,1)*100.

    2. Please give me a solution, Thanks in advance.

      1. Svetlana please help..

        1. Hi Sanoj, it's a very simple solution, just need to "input" it into your formula as text formatted with two digits behind the decimal point:

          Option 1 using your original function:
          =IF(IFERROR(FIND(".",TEXT(B129,"0.00")),0)>0,MID(TEXT(B129,"0.00"),FIND(".",TEXT(B129,"0.00"))+1,99),"")

          Option 2 which is much shorter:
          =MID(TEXT(B129,"0.00"),FIND(".",TEXT(B129,"0.00"))+1,2)

          These options would only treat up to two digits behind the decimal point and would trim any additional digits.

    3. I did like this but I am not getting the zero value
      =IF(IFERROR(FIND(".",B129:B129),0)>0,MID(B129:B129,FIND(".",B129:B129)+1,99),"")

  45. Is there a way to get Custom Formatting to be ALL CAPS for datetimes?

    For instance, I am using the Custom Format:
    ddd dd mmm yyyy hh:mm:ss AM/PM
    which, for today and now would be:

    Wed 19 Oct 2016 03:00:00 PM

    What I want are my day of the week and month in text and in all caps just like the US DoD has on their documents (although they use 24-hour time):

    WED 19 OCT 2016 03:00:00 PM

    I can't apply the UPPER function which applies to text because Excel stores the date as a datetime code which is a pure number.

    1. I may have found it:

      =UPPER(TEXT(MyCell,"ddd dd mmm yyyy hh:mm:ss AM/PM"))

  46. Hi

    Adavnce Vlookup examples are very fine, but when we are running the same formula at my system, the result get blanks. Why this is happened. I checked all the syntax however it shows blanl

    1. Hey Svetlana,
      i am wondering how can i fixe some of data in a cell and add only the variable data instead of doing as follow,

      1300-FF-02-0001
      1300-FF-02-0002
      1300-FF-02-0003
      1300-FF-02-0004

      1. 1. Highlight cells you want to format
        2. Bring up cell formatting
        3. In "Custom" tab enter:

        "1300-FF-02-"0000

        4. Click enter and voila!

        All you should have to do is input...
        This: To get this:
        1 1300-FF-02-0001
        2 1300-FF-02-0002
        3 1300-FF-02-0003
        4 1300-FF-02-0004
        etc... etc...

        If you want to add the variable data elsewhere in the text string just alter the "constants" in the formatting and enter your zeros or place holders where you want your variable data to be entered.

        For example:

        Formatting: Entry: Result:
        0000"-FF-02-0001" 1 0001-FF-02-0001
        2102 2102-FF-02-0001
        267594 267594-FF-02-0001 etc...

        "1300-"@"-02-0001" Q 1300-Q-02-0001
        FF 1300-FF-02-0001
        HEY 1300-HEY-02-0001 etc...

        "1300-FF-"00"-0001" 7 1300-FF-07-0001
        99 1300-FF-99-0001
        1277 1300-FF-1277-0001 etc...

        Just make sure that when you format the cell for numbers (using 0s) don't enter alphabetical data. And vice versa for text formatting (using @). If your cell is looking for letters and gets numbers or the other way around, the formatting won't work. you'll just see the numbers or letters you input into the cell.

        Hope this helps

    2. Hi Sandip,

      Sorry, it's difficult to say anything without seeing your worksheets. There can be a number of reasons that cannot be detected remotely, e.g. numbers formatted as text. Usually, we publish a sample workbook containing all of the formulas discussed in a post. Have you downloaded that workbook? Do those formulas work on your side?

  47. Ok how can i customized my excel cell as in ms access. for eg. i want to customized my cell A1 in this format cell A1 list of customers (den names of customers pop up as in ms access where u can create macro for such purpose....Help

    1. Convert the data to table by Selecting the data and then
      Insert -> Table

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