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