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

  1. How do I make it so that:
    Zero = Black
    Less than Zero = Black, in brackets, thousands separator, no decimals
    More than Zero = Red, no brackets, thousands separator, no decimals

    I think I am close but not quite:
    [Red][>0]#,##0;[Black][<0](#,##0)

    1. Use this code, nice to see
      [blue][>0]"J";[red][<0]"L";General
      change the Font type to Wingdings

    2. Add this
      ;General
      So it becomes:
      [Red][>0]#,##0;[Black][<0](#,##0);General

  2. This has been helpful but hasn't quite hit the nail on the head for me. I have a cell that is displaying numbers through vlookup, some of the numbers are whole numbers and others have a decimal place. I would like the whole numbers to show as whole numbers without a decimal point. I also would like to show a unit with it. It seems I can do either one or the other but haven't figured out how to get both into one format. Is it possible to do that?

    1. Immediately figured it out, just had to have General "unit" after the semicolon.

  3. How can I trim only the decimal part from my figure.
    EG: If the # is 1425.50 or 1425.00 I need only the decimal like 50 or 00

    1. =MOD(1435.50,1) will give you the "remainder" of the number as divided by 1 (the argument in the MOD), so you will have .50.

      If you need it to be 50, then it's =MOD(1435.50,1)*100.

    2. Please give me a solution, Thanks in advance.

      1. Svetlana please help..

        1. Hi Sanoj, it's a very simple solution, just need to "input" it into your formula as text formatted with two digits behind the decimal point:

          Option 1 using your original function:
          =IF(IFERROR(FIND(".",TEXT(B129,"0.00")),0)>0,MID(TEXT(B129,"0.00"),FIND(".",TEXT(B129,"0.00"))+1,99),"")

          Option 2 which is much shorter:
          =MID(TEXT(B129,"0.00"),FIND(".",TEXT(B129,"0.00"))+1,2)

          These options would only treat up to two digits behind the decimal point and would trim any additional digits.

    3. I did like this but I am not getting the zero value
      =IF(IFERROR(FIND(".",B129:B129),0)>0,MID(B129:B129,FIND(".",B129:B129)+1,99),"")

  4. Is there a way to get Custom Formatting to be ALL CAPS for datetimes?

    For instance, I am using the Custom Format:
    ddd dd mmm yyyy hh:mm:ss AM/PM
    which, for today and now would be:

    Wed 19 Oct 2016 03:00:00 PM

    What I want are my day of the week and month in text and in all caps just like the US DoD has on their documents (although they use 24-hour time):

    WED 19 OCT 2016 03:00:00 PM

    I can't apply the UPPER function which applies to text because Excel stores the date as a datetime code which is a pure number.

    1. I may have found it:

      =UPPER(TEXT(MyCell,"ddd dd mmm yyyy hh:mm:ss AM/PM"))

  5. Hi

    Adavnce Vlookup examples are very fine, but when we are running the same formula at my system, the result get blanks. Why this is happened. I checked all the syntax however it shows blanl

    1. Hey Svetlana,
      i am wondering how can i fixe some of data in a cell and add only the variable data instead of doing as follow,

      1300-FF-02-0001
      1300-FF-02-0002
      1300-FF-02-0003
      1300-FF-02-0004

      1. 1. Highlight cells you want to format
        2. Bring up cell formatting
        3. In "Custom" tab enter:

        "1300-FF-02-"0000

        4. Click enter and voila!

        All you should have to do is input...
        This: To get this:
        1 1300-FF-02-0001
        2 1300-FF-02-0002
        3 1300-FF-02-0003
        4 1300-FF-02-0004
        etc... etc...

        If you want to add the variable data elsewhere in the text string just alter the "constants" in the formatting and enter your zeros or place holders where you want your variable data to be entered.

        For example:

        Formatting: Entry: Result:
        0000"-FF-02-0001" 1 0001-FF-02-0001
        2102 2102-FF-02-0001
        267594 267594-FF-02-0001 etc...

        "1300-"@"-02-0001" Q 1300-Q-02-0001
        FF 1300-FF-02-0001
        HEY 1300-HEY-02-0001 etc...

        "1300-FF-"00"-0001" 7 1300-FF-07-0001
        99 1300-FF-99-0001
        1277 1300-FF-1277-0001 etc...

        Just make sure that when you format the cell for numbers (using 0s) don't enter alphabetical data. And vice versa for text formatting (using @). If your cell is looking for letters and gets numbers or the other way around, the formatting won't work. you'll just see the numbers or letters you input into the cell.

        Hope this helps

    2. Hi Sandip,

      Sorry, it's difficult to say anything without seeing your worksheets. There can be a number of reasons that cannot be detected remotely, e.g. numbers formatted as text. Usually, we publish a sample workbook containing all of the formulas discussed in a post. Have you downloaded that workbook? Do those formulas work on your side?

  6. Ok how can i customized my excel cell as in ms access. for eg. i want to customized my cell A1 in this format cell A1 list of customers (den names of customers pop up as in ms access where u can create macro for such purpose....Help

    1. Convert the data to table by Selecting the data and then
      Insert -> Table

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