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 4. Total comments: 431

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

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

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

  4. Thank you SO MUCH for this incredibly useful information!! It is so well presented and thorough!! I am glad that I have purchased your product and supported your efforts, keep up the great work!!

  5. Hello. Here's a challenge:
    I'm looking for a solution to a problem that I have regarding regional settings.
    Is it possible to have my regional setting in place [.(period) as Thousands separator and ,(comma) as Decimal point] and have specific cells in my spreadsheet with: [.(period) as Decimal point and ,(comma) as Thousands separator]?
    I need to have charts and tables with two different settings (and language) in the same sheet...
    THANK YOU!!!!

    1. Ricardo Mateus, have you found a solution?

  6. Hello, I'm currently using a formula to combine values from several cells into one cell. While doing that, I am hoping that all of the numbers that get merged into the final cells show up to two decimal points (e.g., 1.20), however, even when I custom format the cells using the 0.00 code, my merged number does not show the last decimal 0 value. So it shows as 1.2 instead of 1.20. Is there any way I can fix this? Just in case it might be helpful, the formula that I am currently using the following formula:
    =IF(c9>.05," ", IF( c9<.001,"***" ,IF(c9<0.01,"**",IF(c9<0.05,"*"))))

    Thank you in advance for your help!

    1. Sorry, that was the wrong formula. Below is the correct one!
      =CONCATENATE(C6," (",D6,", ",E6,")","",G6)

  7. Hi Svetlana,
    Could you please help me on this number formatting?
    My figures are all in thousands. I need to input 1 and it displays as 1,000. (or type 10, displays 10,000 ),type 1.5 shows 1,500 type 10.5 shows 10,500 .(So as to save myself typing the zeros). Then when I sum those figures, the total should be 23,000 (total of
    1,000+10,000+1,500+10,500 (and not 23).
    I'd appreciate your help.
    Thank you in advance.

    1. In Alignment in excel go to number, in that select custom, in that delete General option and insert #, and press Ctrl+j and insert %%% and get the result please

  8. hi
    hope you can help.
    i have a number, eg. 0602.10.00.3.
    however this number has to have 10 digits as in 0602.10.00.03.
    there has to be a zero before the final digit if it is less that 10.
    i hope you can help
    Kathleen

    1. Hi Kathleen,
      You may try this formula =CONCATENATE(LEFT(A1,11),"0",RIGHT(A1,2)). Value in cell A1. Hope this can solve your problem.

  9. Hi,
    I use IF Function in the cell so that it will show me "yes" or "no"
    The thing is that I want if the formula show "yes" I want it in a red color... been trying to format the cells but nothing work... could you tell me what should i do? many thanks

    1. Sorry, no need to answer... I already got the solution, changing the if formula into a code of 1,0 then formatting the cell to translating the code into a text and coloring according to my condition

  10. Hi,
    0,, "Mbps" will display 10000000 as: 10Gbps
    How do i display:
    0.000001 as 1 usec
    ?
    Thanks

    1. Of course i meant:
      0,, "Mbps" will display 10000000 as: 10Mbps

  11. Hi I am trying to format numbers into billions so displaying 4.38 billion instead of 4,378,772,008, I have over 40 numbers I need to do this for. Any help would be appreciated.

    1. Did you figure this out? I'm doing the same Econ assignment

  12. Hi, Svetlana!

    Thank you for your article, its great and helpful.
    Please, tell me, is it possible to display -3.5 (for example) as
    (space)(space)(minus)3.5(space)(space)
    I managed only to place (minus) only before (left from) insignificant spaces, like this:
    (minus)(space)(space)3.5(space)(space).

    Thank you!

    1. Hi Dmitry,
      Thank you for your question.

      If we understand your task correctly, the following formula should work for you:
      =CONCAT(" ", -3.5, " ")

      You can learn more about the ways to concatenate values in Excel in this article on our blog.
      Hope this is what you need.

      1. Dmitry, I'm assuming that this is what you're after?
        _ _ #.#_ _ ;_ _ -#.#_ _ ;_ _ 0.0_ _ ;@

        1. Correction, sorry:
          _ _ 0.0_ _ ;_ _ -0.0_ _ ;_ _ 0.0_ _ ;@

  13. Hi guys, I need to make a custom currency format same as accounting one, but I need it in ARABIC figures with ARABIC currency symbol "ج.م.". the numbers & symbol should be aligned same as accounting excel format.

    Any way to do please?

    1. Hi, Mohammed,

      Simply select your data, press Ctrl+1, and go to the Accounting category in the dialogue window that appears. You will see a drop-down menu for Symbols. Pick Arabic (Egypt) from the list and hit OK to save changes.

  14. We get a list of numbers in that actually start with a letter (Example L18-021547) and I would like to figure out how to apply a format to those numbers that adds an asterisk (*) to the beginning and the end of that without having to add them to every single field manually (*L18-021547*). We get a bundle of 100+ of these numbers for each report and I am just trying to come up with a way for it to see the field as a number, which it is not doing at the moment because it starts with "L". Any suggests?

    1. try using this formating in cell format

      "*L"00-000000"*"

      each time you just need to type out the number and it will add the * and also the L for you.

  15. Hi Svetlana,

    Trying to use Custom Format to color numbers in a cell based on the following conditions: green for numbers less than or equal to 45, amber for numbers greater than 45 but less than 60, and red for numbers 60 and greater.

    Any suggestions?

  16. I am trying to format my phone numbers to look like this: 303.555.9876, but instead they look like this:3035559876..

    I will like to know how to fix this problem.

    1. This works for me.
      [<=9999999]###"."####;###"."###"."####

  17. I'm trying to round the number 1,230 to 1,200; or 43,540 to 43,500. Thanks.

  18. I NEED 4556 A,4557 B,4558 C,4559 D ,,,,,,,,,, serial numbers with 6 rows and 6 columns formula send me plz

    1. instead of using conditional formatting in excel you can use a simple formula . 1&char(64+Rows(a$1:a1)) will give you 1A try this

  19. I'm trying to convert a number, say 123.50 to 000012350 (always 10 characters, need pennies but no decimal point.

    Thanks

    1. Hi Kevin,

      Assuming the original number is in A1, the conversion can be performed with this formula:
      =TEXT(A1*100, "0000000000")

      However, the result of the formula will be a numeric string, not a number.

      If the result should be number, you can multiply the original numbers by 100 to get rid of the decimal point (=A1*100), replace formulas with values if needed (an intermediate result will be 12350), and then apply this custom format: 0000000000 to always display 10 characters with the required number of leading zeros.

  20. Hi,
    How do I format a decimal number to the unit using the costume format.
    I don´t want it to roud the number.
    Example: The number is 3,84 and I want it to display only the number 3 (not rouning to 4)
    Thank you

    1. Jose:
      You can use the ROUNDDOWN function to return only the first digit like this:
      =ROUNDDOWN(M40,0)
      Where 3,84 is in cell M40 you want the function to return only the first digit.

  21. Maybe the code is something like
    "B"0000/00/
    But the problem is that excel doesn't let me to change/enter the code in the costume format cell box

    1. I don't think its a matter of formatting the cell. As best that I can tell you can achieve the end result like this. Because there is no pattern to the text other than the number of digits I used three different formulas.
      For the seven digit texts I got this to work:
      ="B0"&LEFT(O1,3)&"/"&MID(O1,4,2)&"/"&RIGHT(O1,1)
      For the eight digit texts this works:
      ="B"&LEFT(O2,4)&"/"&MID(O2,3,2)&"/"&RIGHT(O2,2)
      For the nine digit texts this works:
      ="B0"&LEFT(O3,3)&"/"&MID(O3,4,2)&"/"&RIGHT(O3,3)
      I use Excel 2016, so my concatenation function might be different than yours.

    2. Hi,Please send sample file.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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