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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  19. Such an impressive document. Thanks a lot!

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

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

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

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

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

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

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

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

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

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

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

  31. Hello,
    I have a problem with prices.
    In all tables, my prices are like this: 2290 and are supposed to be 22.90
    Is there any way to fix them all together, not going one by one?

  32. Hi,

    I have $5,775 and want to display this as US$ 5,775

    Thank you in advance.

  33. What bothers me about Excel customizations is that Excel will not allow me to save them to use in other worksheets. I can make a template but that requires a lot of clunky cutting and pasting. Why not allow users a .ini file to save customizations of different types that could be used universally as if they were native?

  34. How to remove text from cells with custom formatting like dilip.choudhury@ gmail.com will show as dilip.choudhury. I know this can be done with excel functions, vba..etc. Is it possible to do with excel custom feature option.

  35. Thanks for a great article!

    Is it possible to display a percentage value without the percentage sign using custom number formatting? I want the chart labels for each data point to be displayed without %.

    What I have: 65%
    What I want to display: 65

    I cannot change (like x*100) because it is used in the graphs and in other calculations. I also do not want to add an extra column with a new value.

      1. Ah, too bad! Thanks

  36. Hi,
    Can someone help me make my excel cell that looks like 12345, look like 1ha23a45m?
    That is the system for the area In my country.
    Even something like 1,23,45 could help a lot.
    I really appreciate any help you can provide.

      1. Try this on a cell containing 12345

        #"ha"##"n"##

  37. H,
    I am using [>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0
    to show the number as 1,11,11,111.

    Subtracting two number using above format, I will get the result in same format as shown below.

    A1=3,45,67,890
    B1=2,34,56,789
    C1=1,11,11,101 (A1-B1)

    But, i need to have plus (+) and minus (-) based on operation.

    Here C1 should show like +1,11,11,101 and D1 (b1-a1) = -11,111,101

  38. Hi,

    Hope you are well.

    i have data with 0, 10%, 30 (example) so when I do custom format to hide zero I have used 0;-0;;@ so it hides 0 but 10% became 0. Please advise what is the issue and how I can hide 0 and keep the rest number as it is.

    Look forward to your reply

    Thanks

    Razib

  39. Hi,

    Hope you are well.

    i have data with 0, 10%, 30 (example) so when I do custom format to hide zero I have used 0;-0;;@ so it hides 0 but 10% became 0. Please advise what is the issue and how I can hide 0 and keep the rest number as it is.

    Look forward to your reply

    Thanks

    Razib

      1. Hi Alexander,

        Thank you for your prompt reply.

        I have tried #;-#;;@ but its hiding 0 and 10% both. I want to hide only 0 and keep 10% sales and other numbers same.

        Is there any other thought ?

        Look forward to your reply.

        Thanks
        Razib

          1. Hi,

            But both are in different cell. 0% is one cell and 10% is another cell. So can’t do custom format to hide 0 and keep 10% as it is ?

            Please advise

            1. Hi!
              Custom formats follow a structure with up to four sections separated by semicolons. Use the third section to hide null values: 0.0%;0.0%;""
              Please re-read the paragraph above: Understanding Excel number format.

  40. I have a currency format "#,##0.00" when the computer is configured the language of the ENG system separates by thousands and in ESP it does so by millions. how can the format be standardized

  41. Hello! Fantastic article, I have used it a lot in the past year!
    I have the following problem. I have a set of numbers in a column, some are integers, some are decimals. If I use something like #.##, then the integers appear with a dot at the end (ie. "42."), instead of just the number. Is there any way to make the decimal point conditional?
    (I am sorry if this has been asked already, I scrolled for a while but there are a lot of comments...)
    Thanks! Kalman

    1. Hello!
      Unfortunately, if you have selected a number format with a decimal separator, it will always be displayed.

    2. Ah never mind, I found the comment in the end where it is confirmed that it cannot be done :( It is weird though, one would expect to see whole numbers without a point in the end. I constantly need to explain this behavior to users which is annoying.

  42. In practical, I am using this method often (eg. #,##0,↑;[red]#,##0,↓;-). Unfortunately when ever I copy-paste into PowerPoint table, the Font Color format can't be copied into the PowerPoint.
    I checked using vba script of the cell target using script "rng.Font.ColorIndex" the result seems is not match with the shown font color that perform by Number Format.

    Is there any vba script to verify the color shown by Number Format?

    1. Hello Alexander,
      Thank's a lot for your fast response.

      I've been trial GetCellFontColor function to get the color code of a cell that use custome number format below,
      number format : #,##0,↑;[red]#,##0,↓;-
      trial number1 : 1000 (shown font color black). Validate using GetCellFontColor, Result = 0
      trial number2 : -1000 (shown font color red), Validate using GetCellFontColor, Result = 0 (instead 255 for red color).
      It seems the GetCellFontColor is using xlRange.Font.Color that work on cells, It can't detect the font color that produce by custome number format.

      Do you have any alternate function that can validate font color that produce by custome number format?

      I'll be greatfull for your advice.

  43. Ok firstly thankyou for this detailed article on custom number formating. I have struggling with it specially because i am from india and use indian number system which consists of lakhs/crore and not million billion. Was searching everywhere right from youtube but no success.. finally read this article and now i can show values in crores or in lakh.

  44. Great article - thanks. So much information here that you can't find elsewhere.
    I'm having trouble with a custom format. I'm trying to show a number in 4 digit format with a letter at the end. For example "50A" would show at "0050A", or "180C" would show as "0180C".

    Obviously if i remove the trailing letter from the cell i can get the 0000 custom type to work for the number alone, but when putting the trailing letter back in I don't know what to change my custom format too to also show the letter as well but also stay in the 4 digit format. Can you help?

    1. you can use custom format. just right click the cell and after your number format put "Aa".

      I.e. I wanted to calculate hours worked but I wanted "hrs" to show up after the numerical part of the result, so in custom I put: 00.00"hrs". If I wanted a space between the number and hrs I would just include a space between the last 0 and the quation mark like: 00.00 "hrs"

    2. Hello!
      Your data is text, not a number. Therefore, the 0000 custom format cannot work here. You can get the value you need in another cell using text functions.

  45. Thank You.

  46. Hi - Really informative article - appreciate all the details.

    I'm wondering if there is a way to hide blanks for currency, but have it apply to multiple currency types. My document displays either $ or € based on some other conditional formatting I've got built-in, but I can't seem to find a way to hide cells with a value of €0.00... it only seems to work for $0.00.

    Under cell formating, using custom formula, I'm using $#,##0.00;($#,##0.00); which I imagine only works for those cells with the $ currency type - is there a way to make this formula or something similar apply to both $ and €?

    Any help would be appreciated.

    1. Hello!
      €0.00 values will not be shown with this custom format:

      €#,##0.00;(€#,##0.00);

      Hope this is what you need.

      1. Hi - Thank you for the quick response!

        This seems to work only if the cell is in €. In this use case, the cell this formula is applied to varies between either $ or € depending on other conditional formatting variables. Is there a single formula/format that supports hiding multiple currencies or is that not possible?

        Thanks again.

  47. Hi! I have a series of fractions in 1 row that I would like Excel to convert to the percent format in the next row. How do I do this?
    Thanks!

  48. Hi,

    Looking for a custom number format that shows 450 as 0'5 and -450 as -0'5 (in red).

    0.0,;[Red]-0.0, shows 0.5 and -0.5, but need to show an apostrophe.

    Thanks,

    Simon

  49. i already made it as a conditional formatting for other non error cells and it keeps the data of the cell but it change it appearance to preview a "TEXT". however, i couldn't do the same to na() cells.

  50. Hello,

    I have an excel sheet that counts the number of data and reflects it on a pi-chart. however, i found it better to reflect zero cells as #N/A in order to not let it be visible in the chart until it gets a count.

    is there a way to format #N/A (basically error cells) as "TEXT" instead?

      1. As I am using the cell value in a pie chart I would like to exclude the zero values cells from being shown in the chart and found out the best way is to have the zero cells as #N/A. however, I would like to change the cell appearance to show "ZERO" instead of #N/A while keeping their values.
        I have tried using IFNA and ISNA functions in the Formatting and in the Conditional Formatting but it didn't work out for me, is there any other methods I can try to get this result?

          1. Actually what I am trying to do is to change only the appearance of the cell to show "ZERO" while maintaining the value of the cell as #N/A.

      2. is it possible to use these functions in the formatting? because i would like to keep the cell value as #N/A

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