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)

Jernigan

You might have better luck with this question on superuser.com. – Pedestal

I added the unit symbol before, that isn't the problem, but the prefixes definitely are. (I meant prefixes of the unit :) but they are probably also the suffix of the number) – Jernigan

Answer 1

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}

Occupier

Thanks, at least now I won't waste any more time on it – Jernigan

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.

Conflation

Answer 3

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

Brownout

Answer 4

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.

Cording

Just realized I included B (for Billion) instead of G, and then had a G after that in my conversion table. This screwed up the order. Just remove B so G is 10^9 and T is 10^12 etc... Sorry for the foolish mistake. – Cording

Answer 5

There's no way I know of to do this as a number format (where you can then use the formatted number as you would any other numeric value for subsequent calculation), but for simply presenting the number using SI prefixes, here's the formula I use. It takes the formula in the specified cell (usually next to it, in this case E28), scales the number, rounds to the specified number of significant figures (in this case 3), appends the appropriate SI prefix, and then appends the specified unit (in this case 'F' for Farads). The advantage here is that the formula is self-contained and doesn't require any external reference tables. This formula works for femto (10^-15) through Tera (10^12), but can easily be expanded for additional prefixes

=CONCAT(
    ROUND(
        IF(E28>1E12, E28/1E12,
            IF(E28>1E9, E28/1E9,
                IF(E28>1E6, E28/1E6,
                    IF(E28>1E3, E28/1E3,
                        IF(E28>1, E28,
                            IF(E28>1E-3, E28*1E3,
                                IF(E28>1E-6, E28*1E6,
                                    IF(E28>1E-9, E28*1E9,
                                        IF(E28>1E-12, E28*1E12,
                                            E28*1E15
        )   )   )   )   )   )   )   )   ),
        3 +N("This is the number of significant digits to round to")
        -(1+INT(LOG10(ABS(
            IF(E28>1E12, E28/1E12,
                IF(E28>1E9, E28/1E9,
                    IF(E28>1E6, E28/1E6,
                        IF(E28>1E3, E28/1E3,
                            IF(E28>1, E28,
                                IF(E28>1E-3, E28*1E3,
                                    IF(E28>1E-6, E28*1E6,
                                        IF(E28>1E-9, E28*1E9,
                                            IF(E28>1E-12, E28*1E12,
                                                E28*1E15
        )   )   )   )   )   )   )   )   )   ))))    
    ),
    IF(E28>1E12, "T",
        IF(E28>1E9, "G",
            IF(E28>1E6, "M",
                IF(E28>1E3, "k",
                    IF(E28>1, "",
                        IF(E28>1E-3, "m",
                            IF(E28>1E-6, "µ",
                                IF(E28>1E-9, "n",
                                    IF(E28>1E-12, "p",
                                        "f"
    )   )   )   )   )   )   )   )   ),
    "F" +N("This is the unit symbol that will be appended to the end")
)

If you want to round to a fixed number of decimal figures as opposed to significant figures, the formula is a little simpler:

=CONCAT(
    ROUND(
        IF(E28>1E12, E28/1E12,
            IF(E28>1E9, E28/1E9,
                IF(E28>1E6, E28/1E6,
                    IF(E28>1E3, E28/1E3,
                        IF(E28>1, E28,
                            IF(E28>1E-3, E28*1E3,
                                IF(E28>1E-6, E28*1E6,
                                    IF(E28>1E-9, E28*1E9,
                                        IF(E28>1E-12, E28*1E12,
                                            E28*1E15
        )   )   )   )   )   )   )   )   ),
        3 +N("This is the number of decimal digits to round to")
    ),
    IF(E28>1E12, "T",
        IF(E28>1E9, "G",
            IF(E28>1E6, "M",
                IF(E28>1E3, "k",
                    IF(E28>1, "",
                        IF(E28>1E-3, "m",
                            IF(E28>1E-6, "µ",
                                IF(E28>1E-9, "n",
                                    IF(E28>1E-12, "p",
                                        "f"
    )   )   )   )   )   )   )   )   ),
    "F" +N("This is the unit symbol that will be appended to the end")
)

Note that I've written all of the scaling constants in exponential notation, Excel will change these to plain numbers when you enter the formula. By using a relative cell reference, it's pretty easy to copy and paste the formula around where you need it.

The formula could be condensed into a single block of IF/CONCAT/ROUND statements, but arranging it as I've done here separates out the rounding constant into a single point in the formula, making it easier to change.

Mohandis

Answer 6

Just select the cell or range of cells you want to contain the given symbol. Right click on the cell and select FORMAT CELLS. Select the NUMBER format on the left, enter decimal places, etc on the right. Now go all the way down the list of your format options on the left and select CUSTOM. (IMPORTANT: Do NOT select ANY custom format options on the right.) Left click in the box just below TYPE: and above the list of custom format options. (This box displays your current selected format. {0.00 if you selected the default number format} You want to keep this formatting AND add additional formatting.) Click to the right of 0.00 and type the following: " μ" Click OKAY and you may enter your data as normal. Formatting cells has no impact on the values you enter. You can perform all functions as normal. I am attaching a pic where I used the same instructions to apply litters and the greek MU notation to values and performed some basic calculations without impeding Excel's ability to function.Special Notation in Excel

Caustic

Answer 7

This is a limited answer for Google Sheets, using actual number formats instead of expressions that output text.

Spinning off from endolith's answer, I settled on this:

[>=1E6] #,##0.0,,"M";[>1E3] #,##0.0,"K";0.#####################

It works on numbers from 1 to <1E16, though can't be expanded to units above M. Doesn't work for negative numbers or fractional numbers. It is limited by the number of conditional sections Google Sheets is able to parse.

Docs: https://developers.google.com/sheets/api/guides/formats#number_format_patterns

Katonah

Answer 8

' Hans Wolfgang Schulze 20190921, cause I always need this and need to write it again cause I forgot where I saved it.
' Paste this into Excel's Macro Editor (F11) and use from any cell.
' Copyleft 2019.  Please include original author's name in all derivative works.
'
' Note that the conversions in this code is assuming normal Base 10, and not Binary 1024.  Lots of code has to change.
' Currently recognizes numbers like "-123123.123G" or "123E15" with or without actual units.
' Special case of Excel's "-" nothing equals 0.
' Assumes, if exists, that the rightmost character is the SI exponent designation - See expS below.
' Usage: =DSci("45e9k") gives "4.5E12" as an answer.
Const expS = "-qryzafpnum KMGTPEZYRQ" ' https://en.wikipedia.org/wiki/Metric_prefix
Function DSci(inputS As String) As Double
    Dim which As Integer
    which = InStr(expS, Right(inputS, 1))
    whichUnitary = InStr(expS, " ") ' offset into expS for " " unity value
    If which = 0 Then
        which = InStr("----F-Nµ- k-gt-e", Right(inputS, 1)) ' try alt case and form that aren't obscure ie k=K or m!=M
    End If
    If which > 0 Then     ' has a terminating exponential character. 1 is not found.
        If which = 1 Then ' "-"
            DSci = 0      ' excel nothing value (0)
        Else              ' convert only the left side of input
            DSci = CDbl(Left(inputS, Len(inputS) - 1)) * 10# ^ ((which - whichUnitary) * 3#) ' fix for Binary K's
        End If
    Else
        DSci = CDbl(inputS) ' convert whole string instead ' special case devide by 1.024 for each 1000 for Binary K's
    End If
End Function
' Formats to SI convention 10 ^ (-30 ... +30) and recent suggested expansions
' Usage =Sci(5.531e9, "B") gives string of "5.531GB"
' Significant digits are suggested as 4, can be any positive number.
Function Sci(value As Double, optionalUnit As String, Optional significant As Integer = 4) As String
    Dim mant As Double, exp As Double, rank As Integer
    rankUnitary = InStr(expS, " ") ' offset into expS for " " unity value
    If value = 0 Then exp = 0 Else exp = Log(value) / Log(10#)    ' nDigits
    mant = value / (10# ^ exp) '
    While mant >= 999.9999999999  ' don't want 2000K, rather 2M.  Change to 1023.9999999999# for Binary K's
        exp = exp + 3#
        mant = mant / 1000# ' change to 1024# for binary K's etc.
    Wend
    rank = Int((exp + 0.0000000000001) / 3#) ' should be >1E-300 or so? Why not? 3 != 3#?  More changes for Binary K's ?
        mant = mant * 10# ^ (-rank * 3# + exp) ' adjust mantussa after de-ranking.  Change?? for Binary K's
    If Abs(rank) >= rankUnitary Then ' outside of +/- yY bounds
        expChar = "?"                ' what do you call it then?  Not defined.
    Else
        expChar = Mid(expS, rank + rankUnitary, 1) ' add SI
    End If
    Sci = Left(mant, Abs(significant)) ' don't allow negative numbers, pretend they are positive lengths
    If Right(Sci, 1) = "." Then Sci = Left(Sci, Len(Sci) - 1) ' lop off right DP
    Sci = Sci & " " & expChar & optionalUnit
End Function

Banas

Could you please add some comment as to what this is and how to use it in accordance with the SO guidelines? – Eye

Sorry, i am not sure what your question is asking. Also, what is SO, do you mean SI? The code is for Excel, from the comments. – Banas

SO is Stack Overflow :) Code-only answers are generally not favoured, if you look at the guidelines. It would be appreciated if you made an edit to your answer explaining one can use it. It's a macro - where to put it, how to link it to the sheets in questions. – Eye

Done. Took too a half hour of hacking around. – Banas

Answer 9

Brute force it:

=ROUND(n/(10^(FLOOR(LOG(n),3)-MOD(FLOOR(LOG(n),3),3))),3)&" "&SWITCH(FLOOR(LOG(n),3)-MOD(FLOOR(LOG(n),3),3),3,"K",6,"M",9,"G",12,"T",15,"P",18,"E",21,"Z",24,"Y",-3,"m",-6,"u",-9,"n",-12,"p",-15,"f",-18,"a",-21,"z",-24,"y"," ")&"m"

Where n is your cell reference, which has to be replaced in five places (the initial param in the ROUND function and all four of the LOG functions).

The final "m" is your base unit and can be changed to "g" or "Hz" or whatever (just do not use a term which already has a prefix, like "Kg").

Castle