Rounding Numbers to Millions with Thousands as a Decimal

To round a number to millions with the thousands shown as a decimal:

1. Press Ctrl+1 to open the Format Cells dialog box.
2. Select the Number tab, and from Category, select Custom.
3. In the Type box, enter the following Custom Formatting syntax:
#,##0.0,, ;[Red](#,##0.0,,);- ;

• Original number: 5,645,625
• The displaying formatted number: 5.6

The number formatting syntax is:
Positive; Negative; 0; Text
Screenshot // Rounding Numbers to Millions with Thousands as a Decimal
Rounding Numbers to Millions with Thousands as a Decimal

5 thoughts on “Rounding Numbers to Millions with Thousands as a Decimal

  1. I have two sales reports. Both have the same format and layout. I am trying to generate a list that compares one report to the other and picks out the records that do not appear in the list i.e. this years sales vs last years sales : pull out all records that appeared last year but are not in this years.

  2. “Hello,

    Is there an easier way to count the amount of text cells in a column automatically via a formula.

    I understand I can put a 1 in a column next to each text cell in the previous column, then formulate to count the 1′s, but I think thats extra work.

    Isnt there a easier way to just add the amount of cells in a column that show text, as opposed to numbers, at the end of the column?

    I dont/cant figure out if the COUNT function is able to do this or not, it appears to only work with numbers.

    Any advice/assistance would be appreciated. Thank you very much in advance.”

  3. “I’m trying to use a “”countif”” function in a 3 dimensional environment on a workbook that has pages representing months of the year.

    I would like Excel to count all of the entries in Cell A1 on all of the pages, if there is anything there in any of them.

    On a totals page, in Cell A1, I tried the following formula:


    It gives me a #Value error.

    If I use:


    it works fine. So there must be something in the way I am defining the January to December worksheet pages that is messing up the formula, but I can’t figure it out. Can you help me? “

  4. Hi Ron. My only idea is the formula =LEN(text) which counts the characters for every cell. Maybe you have to add each cell. For example =(LEN(text)+LEN(TEXT)..this is just a one time set-up.

    Hope this can help you.

  5. 0.0,, Works just as well

    0.0 says that it must have 1 decimal
    ,, means that it must be divided by 1000 and another 1000 (each “,” means divide by a thousand)

    If you want to place text afterwards as Unit of measure use this:

    0.0,, “MM”

    This way is clearly noted that you are talking about Millions

Leave a Reply

Your email address will not be published. Required fields are marked *

8 − six =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>