SI-prefixes for number format in MS Excel

Question

Does anybody know if it is possible to show numbers in MS Excel with SI-prefixes?

I'd like to have

... 1 n, 1 µ, 1 m, 1, 1 k, 1M, 1 G, ...

instead of scientific format

... 1E-09, 1E-06, 1E-03, 1, 1E+03, 1E+06. 1E+09, ...

Perhaps adding an unit like V (volts), F (farad) etc.

I would be perfect, if the cell would still contain the number and not a string, so it can easily be changed to another format (back to scientific or whatever)

Answer 1

You can also use LOG and CHOOSE to keep it in a single formula and reasonably compact.

=ROUND(
  E10 / (1000 ^ INT(LOG(ABS(E10),1000)) )
  ,0
) & CHOOSE(
  INT(LOG(ABS(E10),1000)) + 6
  ,"f","p","n","µ","m","","k","M","G","T","P"
)

In this formula:

• E10 (referred to 3 times) is the cell containing the raw value.
• ROUND formats number for display, here rounding to no decimals (0).
• INT(LOG(ABS(E10),1000)) is the prefix index -5 through +5.
• CHOOSE is the prefix to use (needs positive index, hence + 6).

Answer 2

You can do something like this, which I got from Millions & Thousands Custom Number Formatting :

[>=1000000] #,##0.0,," MΩ";[<1000000] #,##0.0," kΩ";General

• 400 renders as 0.4 kΩ (probably not what you want)
• 4000 renders as 4.0 kΩ
• 40e3 renders as 40.0 kΩ
• 40e6 renders as 40.0 MΩ

but you can probably add more clauses to cover other ranges. Nevermind, you can't.