How to use unit abbreviation in Excel?

Question

Excel Scientific Number Formatting is Like This:

1,000,000  >> 1.00E+06

330,000    >> 3.30E+05

How Can I Convert To This Format :

1,000,000  >> 1M

 330,000    >> 330K

(Use kilo, mega, milli and etc)

Answer 1

You can use custom number formats for each cell by right clicking the cell and choosing 'format cell'.

For example if you want to show millions use the following custom format

#,,"M"

---

For thousands use this format

#,"K"

---

If you want to dynamically change between two number formats you can set conditions. Note that you can only set two conditions at a time so you wont have enough to handle all possible units.

Custom format would look like this to test if it should use K or M:

[>999999]#,,"M";#,"K"

---

EDIT

As @Doug Glancy pointed out you can use conditional formatting to set number formats based on values which gives you more conditions. This is also nice in the sense that you can apply the condition to a range of cell's easily.

Answer 2

For what it's worth, you can handle up to 999.9B using a single custom number format (ie. without setting up conditional formatting) -- here's what I use (tested in Google Sheets, but it should work in Excel too):

[<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B"

...which displays as: -0.1K | 123.4K | 1.2M | 12.3M | 123.4B | etc.

(Using 999950 as the threshold instead of 999999 means that you never see the confusing "1000.0K" for values that are getting rounded up to 1.0M)

It doesn't look quite so nice once you get into the trillions (ie. shows 1234.5B instead of 1.2T), but 0 to 999,994,999,999 is a pretty decent range to cover. ;)

(I prefer custom number formats over conditional formatting when possible, because custom formats are easier to transfer from one sheet to another...)

For cells that will always contain negative numbers, you can use this variation:

[>-999950]0.0,"K";[>-999950000]0.0,,"M";0.0,,,"B"

...and for cells that might be negative or positive, this will work correctly from -999.9M to 999.9M:

[>=999950]0.0,,"M";[<=-999950]0.0,,"M";0.0,"K"