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

  1. Hi,
    I enter seven,eight,or nine digits as text in a cell and I want it to be formatted as follows,Pls. help me what is the custom format code
    For example:
    0348971>>>B0348/97/1
    23209715>>>B2320/97/15
    044499565>>>B0444/98/565
    What ever format I choose to change the code the excel returns back an error msgbox that ((not included,excel can't create...)).
    Pls help me with the case which format code I should choose or write in the costume format...

  2. hi
    I have a problem with Access. Can anyone help?

  3. Hi,
    that format is custom "0000" and "AMN-1" Format Is General(
    Of course he did not care),The two cell formats are the same. when click on "0001" cell shown "1" I do not know what to do. Can i send you a file?
    thanks

  4. hi
    i have AMN and 0001 IN tow column in excel ,when CONCATENATE(A1,"-",B1) It shown AMN-1 ,and do not show 000.
    why?
    what happen this problem? and what's the solution? pleas

    1. Habib:
      Is the "0001" in B1 formatted as text or a custom format "0000"?
      How is the cell that displays "AMN-1" formatted?
      It appears as if the formatting is different for the two cells.
      Excel won't display leading zeros as in "0001" unless the value is text or the cell has the Custom Format "0000". The General formatting will work for this, too.
      In your case I would assume that General formatting will work because there is the "AMN-" included with the "0001".
      Right click on the cell that holds the "0001" and choose Format Cells, then choose General from the list. Do the same for the cell that holds the concatenated result. Both cells should be formatted as General.

      1. Hi,
        that format is custom "0000" and "AMN-1" Format Is General(
        Of course he did not care),The two cell formats are the same. when click on "0001" cell shown "1" I do not know what to do. Can i send you a file?
        thanks

  5. Awesome document: clear, to the point, and the instructions work! Glad I found this website.

  6. Hi.. i want to convert following numbers

    19,50,000 to 19.50
    01,00,000 to 1.00

    1. Vasu:
      I think what you want to do is display the numbers with two decimal places.
      You do this by formatting the cells that contain the numbers.
      Start by selecting the cells that contain these numbers then right click, choose Format Cells, then click the Numbers tab and you will see a list of formatting options. You can test each format for your particular need.
      If I'm understanding your question, you want the Number format as its default display uses two decimal places.

  7. Hello,

    How can i write in excel "5-15" whenever i write it shows May 15,

    Please help.

    1. Hi Sandeep,

      Set the cell's format to Text, and then type 5-15.

  8. I need to format numbers in excel as below:

    100,00,00,000/-

    How to do?
    Please..

  9. Hi,
    How can I make a '0' on the left side of another digit visible in the cell? Ex. 03, 01, etc.
    Thanks, Carolina

    1. Carolina:
      Change the number format to custom to include the number of zeros and digits you need e.g., "0000". This will display 0123.
      Select the cells you want to format right click the cell
      selection
      Select Format Cells
      Select the Number tab
      Select the Custom option
      Highlight the first 0 option
      In the Type field enter the total number of digits you want to display. For example, entering five zeros will allow the cells to display five digits including any leading zeros.
      Click OK and you're ready.

  10. i wonder in the customized number format which is 0;;;@
    how could i show the cell with the negative values with pharenthesis?
    just wondering why? for my excel formatting project,
    thank you so much

  11. Wonderful. Great load of information.

    I would like to know if there is a method that when I type Up in my cell, it shows 1, and when I type Down in the cell, it shows 0.

  12. Any way to get rid of the decimal for whole numbers when aligning decimals (for mixed fractional/whole numbers) with something like the 0.?? format? In other words is there a conditional for determining whole numbers?

  13. This should work:
    Enter this into the Format Cell window under the Number/Custom tab 0,,\M You'll still need to enter the number as 1000000 but it will be displayed as 1M and you can use it arithmetic operations as 1000000.

  14. I'm trying to convert from 1000000 to 1 M in office 2016 and none of the methods above are working. anyone else know a solution?

  15. Is there a custom format to always round up or down or does it have to be part of a formula?
    For example...
    Sometimes when rounding up I want a result or copied value of 1.1 to show as 2.
    Other times when rounding down I want a result or copied value of 1.9 to show as 1.

    Thanks in advance!

  16. Hello,

    Wondering if it is possible to format a cell to automatically divide whatever # is entered into the cell by 3.28?

    Thanks!

  17. Hi!

    I would like to know if there is a way to add an inch (") after the values on my dimensions?

    For example:

    2.75x5x.25 is my dimension, and I need it to look like 2.75"x5"x.25" on Excel.
    Is there a way to do that? Any help would be greatly appreciated.

    Thank you!

    1. The way to do this is to enter #?'' in the custom format list and then apply that formatting to the cell. Those are two single apostrophes after the question mark. The cell will display 2.75'', but you can use arithmetical operations on the entries.

  18. I am trying to use Custom format for a chart. If the data is a number that it is pulling from, then I want a number to be shown. If the data changes to a currency, I want the chart to automatically change to currency. Is there a way to do that?

  19. I am trying to use Custom format for a chart. If the data is a number that it is pulling from, then I want a number to be shown. If the data changes to a currency, I want the chart to automatically change to currency. Is there a way to do that?

  20. "For example, 1.25 can be shown as 1 ¼ or 5/5."

    That should be 5/4.

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

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

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

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

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

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

  26. Thank you so much honey.

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

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

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

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

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

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

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

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

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

  36. Amazing, perfectly complete, big LIKE

  37. The Format Cells Excell 2010 to 0.5%

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

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

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

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

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

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

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

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

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

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

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

  49. Very Helpful Thank You

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

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