Share via

Formatting numbers to show them in Millions

Anonymous
2014-06-10T10:13:13+00:00

Hi

In my Excel almost all my numbers are in Millions, expressed entirely (meaning all 6 digits are written including 2 places of decimals).

Without having to write an identical sheet with formulae that divide original numbers by 1000000, is there are an easier way to display the sheet with the numbers expressed in Millions only?  Maybe a formatting value?

Many thanks for your precious inputs.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2014-06-10T10:38:25+00:00

    Assuming your absolute value is in cell A1, then select cell A1

    Right click/format Cells/In Category select custom/in Type box

    type "#,###.00,," without inverted commas,

    it will convert your number to Million with 2 decimals. In case you want 3 decimals or one decimals, increase or reduce zero accordingly.

    Apply same formatting to other cells, if required with format painter.

    5 people found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vijay A. Verma 104.8K Reputation points Volunteer Moderator
    2014-06-10T10:17:56+00:00

    If you want to show with two decimals, Custom Format code would be

    0,,.00

    Adjust 0's accordingly after decimal digits for showing more. Hence for 4 decimal digits,

    0,,.0000

    Edit - For showing all 8 digits

    0,,.00000000

    If you also want to show word million

    0,,.00 "Million"

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2014-06-10T14:56:35+00:00

    Vijay, Vijay

    Thanks.  Works like magic.  Exactlywhat I wanted.

    0 comments
  3. Vijay A. Verma 104.8K Reputation points Volunteer Moderator
    2014-06-10T10:43:06+00:00

    Use 0,,.00 or 0,,.00 "M" as your custom format. (Take right click on cell, Go to Custom and put 0,,.00 or 0,,.00 "M" in the Type: box)

    19245662.38 is 19.2 not 1.92 in Millions.

    0 comments
  4. Anonymous
    2014-06-10T10:30:31+00:00

    Hi Vijay

    I did not express my need correctly.

    A cell has the number 19245662.38

    I would like to show it as 1.92 or 1.92 M

    I would like to do that without having to divide all my numbers by 1000,000.

    0 comments