LibreOffice Calc Tricks

LibreOffice Calc makes it easy to display numbers in engineering notation, which is a scientific notation where the exponent is always a multiple of three. There's a checkbox for it in the "Format Cells…" diaglog box:

The magic is the two hashes in the format code: ##0.00E+0. This means there will be up to three digits for the integer portion before the decimal point and zero padding if the number is fractional. The exponent adjusts automatically to fill them.

This format code also works in Excel, even though there is no "Engineering notation" checkbox for it.

SI prefixes are even more concise and easier to read than engineering notation. The following Calc macro defines a function that will display a value using SI notation:

' Display a number in engineering notation, using SI prefixes.
' Based on https://stackoverflow.com/a/55382156
Option VBASupport 1
Function ENG(value as Double, decimals)
    normalized = ROUND(value / (1000 ^ INT(LOG(ABS(value))/log(1000))),decimals)
    prefix = CHOOSE(INT(LOG(ABS(value))/log(1000)) + 11, _
                    "q","r","y","z","a","f","p","n","µ","m","", _
                    "k","M","G","T","P","E","Z","Y","R","Q")
    ENG = normalized & prefix
End Function

Once macros are enabled it can be used like any other function in a cell formula (eg =ENG(A1,2)). Unfortunately, the result is a string type for any cell that has an SI prefix in it, so they can't be used in further numeric formulas. So I suggest using it only to pretty print the final output of a calculation.

I haven't tried this macro in Excel yet, but the Option VBASupport 1 won't be needed there.