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

  1. hi i need to convet this numebr
    4184
    2459
    4918
    3164
    5942
    4925
    1120
    6804
    7990
    3300

    in

    41,84
    24,59
    49,18
    31,64
    59,42
    49,25
    11,20
    68,04
    79,90
    33,00
    how to do?

  2. Huh? OK, so let's try splitting it up (note that this commenting system also deleted the ">" in the ">=". So maybe it doesn't like the ">" operator?

    The first part should read: "[=1000000]#,#.##,, \M_)"

    If this doesn't work, I give up.

  3. For whatever reason, it truncated the format formula. Here it is in full:

    [=1000000]#,#.##,, \M_)

  4. The conditional formatting is great. It took a while for me to realize that you can then no longer format negative numbers, zero, and text. Thanks for all of this! I did this: [=1000000]#,###.##,, \M_)

    As examples, it get's you this:

    1,666 = 1.7 K
    166,666 = 166.7 K
    1,666,666 = 1.67 M
    166,666,666 = 166.67 M
    1,666,666,666 = 1,666.67 M

  5. The conditional formatting is great. It took a while for me to realize that you can then no longer format negative numbers, zero, and text. Thanks for all of this! I did this: [=1000000]#,###.##,, \M_)

    Example, it get's you this:

    1,666 = 1.7 K
    166,666 = 166.7 K
    1,666,666 = 1.67 M
    166,666,666 = 166.67 M
    1,666,666,666 = 1,666.67 M

  6. Thank you so much honey.

  7. I'm trying to use text and currency in the same cell using distributed indent. with the currency on the right. how do i get the dollar sign to show up?

    ex. Supplies $20.00

    1. These both work, but #2 is the more elegant solution.

      1. "Supplies $"General

      2. "Supplies "$#,##0

  8. Awesome tutorial. Makes for a great reference guide. Thank you!

  9. Thankyou sir, its very helpful for us..

  10. I would like to have a number formatting that does the following:

    10% would be 10%, not 10.% or 10.0%
    ...but 10.5% would be 10.5%
    ...and 105% would be 10.25
    In other words, I don't want any extra 0's after the decimal point, and if there are no fractions, I don't want the decimal point at all.

    Thank you!

  11. Thank you! This is an excellent resource you've compiled.

  12. How would I custom format the cells to get:
    0=Apple
    1=Oranges
    2=Pears
    3=Berries

    It will always be an integer value between 0-3.

    I'm in too deep with formulas to change the actual cell value.

    Thanks!

  13. Please help to apply below login in formating

    Location Capacity Usage Query
    Agra 392 220 IF usage is >70% of capacity background colure of value Yellow
    Ahmedabad 390 324 IF usage is >80% of capacity background colure of value Red
    Ambala 120 100 IF usage is >90% of capacity background colure of value Green

    1. Hello,

      If I understand your task correctly, you need to create a rule for conditional formatting

      We have an article on our blog that describes how to use conditional formatting in Excel. Please have a look at it.

      Hope this information will be helpful for you.

  14. I noticed that excel 2016 (.xlsx) doesn't have the same complement of cell custom formats as Excel 2007 (.xls). I am using 'times' expressed in minutes, seconds & thousands of secs (i.e. mm:ss.00), Excel 2016 lets you choose (mm:ss.0). I need to save these values as a .csv file. I can modify them in the spreadsheet in the 'Type' line by adding an extra '0' digit to the format. Problem is, it's not permanent as the next time I open the .csv file in Excel, its back to the old 'mm:ss.0' format.

    Any way to make that permanent ?
    ...Bill

  15. It looks like you've got the Excel formatting down, but the web formatting isn't cooperating: the formatting involving ???.??? doesn't actually line things up in the "Display as" boxes, though in Excel it works perfectly as described.

  16. Amazing, perfectly complete, big LIKE

  17. The Format Cells Excell 2010 to 0.5%

  18. I have 5 cells in column:
    G17 = 5
    H17 = IF(G17=4,"4",0))
    I17 = H17*OT_pay*1.25
    J17 = IF(G17 >4,G17-4,0)
    K17 = J17*OT_pay*1.5

    How can to make this in One cell only.Please help.. Thank you

    1. G17=5
      H17=IF(G17=4,H17*OT_pay*1.25,IF(G17>4,(G17-4)*OT_pay*1.5,0))

      1. Sorry, should be
        H17=IF(G17=4,G17*OT_pay*1.25,IF(G17>4,(G17-4)*OT_pay*1.5,0))

  19. How can i carry over the formatting of a cell in a text box? For example, i formatted a cell with red font when it has a negative value. The color doesn't carry over to the text box if i reference it to that cell.

  20. How can i carry the formatting of a cell in a text box? For example, i formatted a cell with red font when negative value. The color doesn't carry over to the text box if i reference it to that cell.

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

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

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

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

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

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

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

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

  29. Very Helpful Thank You

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

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

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

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

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

  35. 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,"@"))))

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

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

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

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

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

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

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

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

  44. 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. ;;;$@

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

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

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

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

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

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

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