SI-prefixes for number format in MS Excel

No solution will work better than scientific notation.

If you use custom number formats, then you would have to enter them manually (or with VBA) such that they will mask the actual content of the cell.

For instance, if you want to display the following format pairs:

1 n  1E-09
1 µ  1E-06
1 m  1E-03
1    1
1 k  1E+03
1 M  1E+06
1 G  1E+09

If you have 0.001, you would have to set the format as "1 m" -- this will mask the number, so if you have 0.002 you would have to set it as "2 m" -- if you changed it to 0.004 it would still display 2 m as a result. This obviously isn't ideal.

You could set it up as a two-column sheet, where you have the values in the left, and use a formula to display with units on the right, but then you end up not being able to do math with the formatted values.

So basically, the answer is "no", it isn't possible.

^{You could theoretically write a VBA script that will automatically change the visible contents according to the cell contents whenever a number is changed, but the script would be bulky and would cause serious trouble to whoever you sent to if they had macros off. It would also require all sorts of corner cases depending on if you wanted numbers formatted 'normally' in certain cells. So while it may be theoretically possible, it is practically impossible}

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

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.

It is possible, though bulky using a conversion table and the match and index functions.

From a conversion table like this (2 columns):

1.E+15  P
1.E+12  T
1.E+09  G
1.E+06  M
1.E+03  k
1.E+00   
1.E-03  m
1.E-06  µ
1.E-09  n
1.E-12  p
1.E-15  f

You could then perform the following translation

3.68437E+11 --> 368.44  G

If you have the conversion table in columns A and B and the unformatted number in cell G1

H1
 =G1/INDEX(A:A,MATCH(G1,$A:$A,-1)+1)
I1
 =INDEX($B:$B,MATCH(G1,$A:$A,-1)+1)

Then the proper numerals will display in column H with the suffix/prefix in column I.

It is still ponderous, and should only be used for final output since calculations from the modified numbers will have to include a reverse translation.