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

  1. what is the format crore value with 2 decimal

    input
    57761870

    Output required
    5.78 CR

    1. Hi! You can use number formatting to show a number in thousands or millions.
      For example,

      #,##0.00,," M"

      But you cannot show the number in tens of millions as you want.

  2. Hi, Alex! Awesome article, awesome knowledge of Excel. Thank you for posting and answering questions! Kurt

  3. Hi, I am using Excel for mac. I am trying to custom format a list of fractions, such as:
    1 1/4
    2 3/8
    11/16
    3 5/8

    etc.

    When I apply the custom code of # #/# it is not working, the info still comes in as either dates or decimals.

    How can I force excel to treat all of these the same and have the same formatting?

    Thanks-

  4. How can I see the following format DVO-000. For example it should show as DVO-001 or DVO-010 or DVO-100.

      1. Cell value is DVO123
        I want to custom format to DVO-123.
        It seemed custom format is not working text string followed by a number

  5. Hi. How about showing letter and number characters?

  6. How to change the number format from Dr and Cr to + and -

  7. General number is 15
    How I found 45 by custom format cell ???

  8. Mohon bantuannya saya ingin membuat format angka
    1-2
    1-22
    Saya mencoba #-##
    Tapi hasilnya
    -12
    1-22

  9. In Excel I have a formula that equals ($3,55,12,059). I want to just show ($35,512,059 )

  10. Excellent tutorial. I have a question on the example of text added to custom format. On the example Apples 1 day Shipped in 1 day

    I would like to know if it is possible a custom format that refer to the cell location (H2) where is "Apples" such as "Apples are shipped in 1 day" .
    Thank you for your help,
    Roberto

  11. In Excel I have a formula that equals ($35,512,059). I want to just show ($35512) so I changed the number formatting to custom $#,##0,_);($#,);--_) now I just want to add a comma so it shows ($35,512)

    What's the number formatting that I should use?

  12. I want to center aligned when cell value is 0 (zero), else right aligned. How can it be done?

  13. How to format a number with specific colored text (like red) e.g 2.695 format to 2.7a - the letter a is specific and color red. Thanks

  14. Hello I am trying to change the format of a date on a csv file.
    the format I want is yyyy-mm-dd hh:mm:ss.000
    I change it through custom type and save csv again, but when I reopen the csv the format of the date is the previous one.
    How can I permanently change the type of date to this format "yyyy-mm-dd hh:mm:ss.000" ?

    1. Save the file as an Excel file, do the formatting, then resave as a csv.

  15. Is it possible to create number formats in a start up file and then use them in any other excel file that i open?

    1. Hi!
      Simple solution: just copy your format with the Format by Pattern tool (brush) from book to book.
      Difficult solution: create a new book, add your format to it, save the book as a template (xltx extension) to the Custom Office Templates folder. When you create a new file, you will see this template in the Personal section. Create a new file from this template.

  16. how can i set a costum format to have a number like this IR-06-2541-2451-6582-4895-5842-01??

    1. Hi! What you wrote is text, as there are 2 letters. There is no possibility of separating each 4 digits with the number format in Excel. Use the REPLACE function to insert "-" at the exact position in the text string.

      1. How about this:
        "IR-"00-0000-0000-0000-0000-0000-00"??"

  17. Great Explanation of custom number formats. However, there is something I am confused by that may have been overlooked in this explanation. Does Custom conditions make negative formatting irrelevant? For example:

    [=1]?" mile";# ?/?" miles"

    My mind is still stuck on the fact that anything that comes after the first semi colon is reserved for negative numbers. Is there a hierarchy thing that cancels that rule out.

    Also, If you set a conditional format for a positive number does it automatically apply to negative numbers by treating the value as an absolute?
    Example: [>=1000000]_($* #,,"M";_($* #,,"M"

    This seems to apply to both positive 1 million as well as negative 1 million

    1. Hi! The first and second sections of the default formatting pattern are used to format positive and negative numbers. You can use other conditions in these sections. If you specify one condition, the second section is used for all other values. If you specify two conditions, the third section is used for all other values. For example,

      [=1]?" mile"; [<0]-# ?/?" mil";# ?/?" miles"

      1. Thank you so much for your explanation!

  18. Hi
    I want to display the letter "k" as the unit of kilograms, and its done with below format without backslash
    format" 0 K"
    It seems that the use of backslash is not mandatory isn't it?

    1. Hi! Backslash must be placed before characters that are used in the number format pattern. In other cases, you may not use it.

  19. how to format this 117610600000 its 117 billion to millions numbers only? Anyone can help and will appreciate it much. Thanks.

  20. I was wondering how to create a column where text is right aligned with positive and negative numbers. I have used this custom format to align the numbers, _*#,##0_);_(* (#,##0);_(* " "-"??_);_(@_) but the text will not align and if I use the indent feature for the text, it does not align properly.

  21. I could not found this in all the comments but maybe I messed some.

    I need do format from a CONCATENATE function with embedded line breaks, 2 numbers (or one of them if the other doesn't exists)

    The only part I'm having trouble is the TEXT function format part within IF clauses, as the numbers vary in size.

    the numbers should have the following formats for each variable number
    9
    99
    9 99
    99 99
    9 99 99
    99 99 99
    9 99 99 99

    How can I add multiple conditions to format these #\ ## or ##\ ## or #\ ##\ ##, etc.?

    I can set a condition to divide it in two, but I can't divide it into multiple conditions of the same nature [99] [<=999] #\ ##; etc.

    Is it possible? Or a work around?

    Thanks in advance for any help. I haven't found it any reference or tutorials yet.

    1. Hi!
      If I understand your task correctly, to split a number into hundreds (2 digits each) try this custom number format

      ## ## ## ## ## ##

      1. Thanks for your input. That format provides this (after not the desired format):
        9
        99
        9 99 not 99 9
        99 99
        9 99 99 not 99 99 9
        99 99 99
        9 99 99 99 not 99 99 99 9
        99 99 99 99
        9 99 99 99 99 not 99 99 99 99 9

        Is there a workaround this? I tried to split intervals with conditions to aggregate intervals, but It appears one cannot conjugate in the same format filter more than 1 condition of the same type [>x] with [<=xx] etc. tried the & operator also.

        1. found out a workaround (conceptually, but one could translate it to excel formulas quickly):

          evaluate char number of text string, IF uneven format text string "##\ ##\ ##\ ##\ #" ELSE [even] format text string "##\ ##\ ##\ ##\ ##"

          Sorry fort not posting the formulas but I don't have to time to search for the english functions names of those I don't know from memory (I'm using PT-PT version).

          1. It followed from your questions that you want to change the custom format of the cell in which the number is written. If you were to say that the number needs to be shown in another cell, this completely obvious solution would be offered right away.

    2. Sorry for the typos *missed, etc.

      1. gosh... [>99] [<=999] #\ ##, etc...

        1. Wrong ID of format numbers, sorry

          The one I wanti is this one:

          9
          99
          99 9
          99 99
          99 99 9
          99 99 99
          99 99 99 9
          99 99 99 99
          99 99 99 99 9

          The other version is easy to format in TEXT function with "#\ ##\ ##\ ##\ ##\ #0" Just have to be care to let the single digit option #\ and enough repeats of ##\ for the highest length number...

  22. Hi,
    I'd format a single excel file (not all mine ) a different international format (decimal separator). I do not want change operating system seetings nor excel settings in the advanced menu . thanks

  23. Hello,

    I have a large digit of numbers that always starts with 55, and I need help creating a Format Cell that would color in blue (or bold) any numbers after 55 and before the last 7 digits (the numbers in the seven digits sometimes remain the same). Here is my example 5512345673806327, and the numbers to color in blue are 1234567. Thank you!!

  24. I want to add a minus sign to the beginning of a number and have one decimal place. If I enter 12, I'd like it to show as -12.0. All I've been able to accomplish is:
    "-"@
    It gives me the minus sign, but turns the number into text and I haven't been able to figure out how to add the decimal point.

      1. Awesome thank you!! How was I supposed to infer this?

  25. Hi,
    I want to know how can I use auto numbering in my packing list. Such as my packing list should show.

    Serial No of Carton
    1-2 1
    3-4 1
    5-7 3

    Can I automate the process? it took me while to type the data into the 'serial number' column.

    Please help

  26. I want my table to out put the standard error with +/- the error, is this possible?

  27. Hi Alex,

    This article gives me new insights to do custom format in excel that l think impossible before. Thanks a lot for that.
    I am trying to make this format showed in the cells

    3 digits number, forward slash, fixed character lNV, forward slash, fixed character DJE, forward slash, character, forward slash, 2 digits number
    Example: 123/lNV/DJE/VII/23

    Can l write them like these Alex:

    ###/INV/DJE/General/##

  28. Is there a way for showing the data as 2^#?
    I want to show 1024 as 2^10 and 1 as 2^0.

    1024,00000000000 = 2^10
    512,00000000000 = 2^9
    256,00000000000 = 2^8
    128,00000000000 = 2^7
    64,00000000000 = 2^6
    32,00000000000 = 2^5
    16,00000000000 = 2^4
    8,00000000000 = 2^3
    4,00000000000 = 2^2
    2,00000000000 = 2^1
    1,00000000000 = 2^0
    0,50000000000 = 2^-1
    0,25000000000 = 2^-2
    0,12500000000 = 2^-3
    0,06250000000 = 2^-4
    0,03125000000 = 2^-5
    0,01562500000 = 2^-6
    0,00781250000 = 2^-7
    0,00390625000 = 2^-8
    0,00195312500 = 2^-9
    0,00097656250 = 2^-10

  29. Is there a way to key in, say, 327 and have it automatically convert to 0.327 without using formulas or conditional formatting? I am manually keying many numbers and don't want to have to type the decimal. Nor do I want to manage any other columns.

  30. Hi,
    I am looking for an opportunity to generate an AWB stock.
    An AWB number consists of 3 numbers, a minus, and 8 numbers. a total of 12 characters
    The first 3 numbers and minus (-) are always the same.
    The next 7 mummers count up as usual
    The last number counts up from 0 to 6, and starts at 0 again

    111-12345670
    111-12345681
    111-12345692
    111-12345703
    111-12345714
    111-12345725
    111-12345736
    111-12345740
    111-12345751
    111-12345762
    111-12345773

    1. Hi!
      If I understand your task correctly, try the following formula:

      ="111-"&SEQUENCE(100,1,1234567,1)&TRUNC(MOD((SEQUENCE(100,1,1,1)-1)/1,7))

      1. Thanks for your help, this works good

        1. Hi,
          I notes something wrong in the formula (maybe I was not clear in my fist question)
          This do not always start with a 0 in the end.
          It can be like all these 3 example

          111-12345670 111-23456781 111-3456792
          111-12345681 111-23456792 111-3456793
          111-12345692 111-23456803 111-3456804
          111-12345703 111-23456814 111-3456815
          111-12345714 111-23456825 111-3456826
          111-12345725 111-23456836 111-3456830

            1. Im sorry. I have one more question
              How do I do it if the numbers start with 0?
              like:
              111-00005252
              111-00005263
              111-00005274
              111-00005285
              111-00005296
              111-00005300

              If I use the formel will it show:
              111-5252
              111-5263
              111-5274
              111-5285
              111-5296
              111-5300

  31. Hi,
    Firstly, thank you so much for this useful information, and for the method of the display.

    Please, I need to use commas to separate thousands for numbers with five or more digits (not four digits) in the picture, e.g., “10000” should be “10,000”. So, could you please tell me how to do this? Thanks in advance.

  32. I want to format a tag entry cell so that it can auto correct the manual entry to the correct number of characters. All tags should be 3 numbers, a selection of letters, 4 numbers, Optional letters if there are multiple items doing the same job. For example if the manual entry was 13FT26 then the corrected would be 013FT0026. Or 7PSLCI8B would be 007PSLCI0008B / 007 is 3 numbers, PSLCI is a selection of letters, 0008 is 4 numbers, B is the optional letter.
    If a cell format cannot do this then I can use a macro but I'm not too sure on how.

      1. Thank you so much, I shall take a look.

  33. Is there any option to automatically get numbers in an order when u press enter to the next cell

  34. I have data like this
    CS-1
    CS-10
    CS-125
    P-2
    P-23
    P-134
    BX-12
    BX-1367
    BY-1366
    TKY-1
    TKY-12
    TKY-3

    I want to format this data as below for Sort by number, please suggest me the format I need to do.

    CS-0001
    CS-0010
    CS-0125
    P-0002
    P-0023
    P-0134
    BX-0012
    BX-1367
    BY-1366
    TKY-0001
    TKY-0012
    TKY-0003

      1. I have data like this
        3A1B
        1A1B
        2A1B

        I want to add space on custom format to:
        3A 1B
        1A 1B
        2A 1B

        Can anyone help me? Thanks

  35. I have this number 81628215511. and i want in this format 81.62821.5511. kindly provide me the formula which would help me get rid of my half problem.

    hopping you would help me

    1. no need we got the formula.you guys are the worst.

      1. sorry and thank you for the information. sorry for the previous comment .that guy is bulshit guy.

  36. What formula/ custom format can I use to print -2 pts as (2 pts)?

  37. using ##\,##\,## number format, if i type a number 123456789, it displays like 12345,67,89. But if i type three digit number like 123, it displays in the cell like ,,123 i.e. leading commas are displaying. Please let me know how to format a cell to curtail leading commas and show numbers in hundreds separator

  38. Is there any provision in Excel numbering system that - one can enter any number with any decimal places and insignificant zeros.
    e.g. in cell A1 one can enter the numbers 0.110, 1.0000, 00.567, 0.001, 0.0000100 etc, without any restriction or rounding up the numbers.
    I know this is possible if we format the cell A1 as TEXT, but then it can not be used in formula to use it for the calculation.

    1. Hi!
      Only one number can be entered in a cell. Insignificant zeros are ignored. As a text, you can enter any value.

  39. Such an impressive document. Thanks a lot!

  40. Thanks a lot Sir,
    It is working as 500/00 or any other amount, it is possible it is working as 500/- (like forward slash and a dash)
    Again thanks.

    Reply
    Alexander Trifuntov (Ablebits Team) says: September 21, 2022 at 7:51 am

    Hello!
    You can change the decimal delimiter.
    Go to Excel Options.
    Go to the "Settings" - "Advanced" section.
    Uncheck the option to use system separators.
    In the window that is now active, replace the dot with /.
    After pressing the OK button, all numbers with a fractional part will be displayed in a new way.

    sohailafzal says: September 20, 2022 at 7:19 pm

    You and your team always help the others may God bless you.
    I have question in our areas we often use 500/- in the place of .00 like as 500.00 but we use 500/- and also using this style in adding or substract or multiply so in excel any format of currency that used /- style like as decimal .00 and working as zero

  41. You and your team always help the others may God bless you.
    I have question in our areas we often use 500/- in the place of .00 like as 500.00 but we use 500/- and also using this style in adding or substract or multiply so in excel any format of currency that used /- style like as decimal .00 and working as zero

    1. Hello!
      You can change the decimal delimiter.
      Go to Excel Options.
      Go to the "Settings" - "Advanced" section.
      Uncheck the option to use system separators.
      In the window that is now active, replace the dot with /.
      After pressing the OK button, all numbers with a fractional part will be displayed in a new way.

  42. Using a custom format of #,###.## so that decimals will only appear if there is a need for them does however lead to this display showing say 5 as 5. which looks strange. Is there any way to omit the decimal point, if there are no decimals to be displayed?

  43. HI Alexander, thanks for this great article!
    I'd like to use colors in Custom Format to highlight errors, ie, get a #N/A or #DIV/0 in Magenta. However, Excel does not recognize the error code as text, nor as a number. Is there any way to have the error codes in a different color?
    Thanks so much!

  44. Is it possible to specify different cell format for odd and even number? Thanks.

  45. I have some numbers I am working with in a pivot table. I am wanting them in thousands. I have formatted them with "$#,##0,K" and they look great. For example 4234 shows up as $4K. However when I go to group these I get something like 0-5000. Is there any way to format this as 0K-5K? Thanks

  46. I have numbers formatted and customized with prefix numbers, but I want to copy it at a whole as shown including the preformatted numbers,

    Example: Inputed as 10 but with a prefix number of "295500901000000" so the result becomes "2955009010000010"

    but, I want to copy the whole result number to "2955009010000010" instead of "10"
    Can help me fix them? Thank you in advance.

    1. Hi!
      Excel accepts and processes numbers only up to 15 digits, and displays no more than 11 digits for a numeric value. Anything greater is shown in exponential (scientific) format, such as 1.111E+14
      You can automatically add a number as you type using a VBA macro

      1. Thank you Sir Alexander for replying, I really appreciated it. Here is my concern:

        How can I copy a cell with a value entered as 2340 (example only), but it was formatted with prefix the 2955009, so the result would become “29550092340” (joined the value entered).
        Value entered: 2340
        Result: 29550092340
        Question: If I copied the result, the value showed just 2340, but I want to copy the whole result of 29550092340.
        Please assist me how; thank you very much.

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

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

  49. Hi,
    Id like to appreviate currencies per the below:

    $1,234,567 show as $1,235
    $730,935 show as $731
    $50.357 show as $50

    can you please assist with correct format code?

    1. Hi!
      Pay attention to the following paragraph of the article above: Round numbers by thousand, million, etc.
      It covers your case completely.
      Try custom format:
      $#,##0,

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

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