In Google Sheets, I work with data spanning a few orders of magnitude, like mm (millimeter), µm (micrometer), and nm (nanometer). I wrote this formula for a custom number format using conditional rules:

[>=1000000]0.00,, "mM";[>=1000]0.00, "µM";0.00 "nM"

In this formula, nanometers are the default unit, so the data needs to be input in terms of nanometers. This means that:

1 is displayed as "1.00 nm"
1000 is displayed as "1.00 µm"
1000000 is displayed as "1.00 mm"

However, most of the data I work with is in the micrometer range, so I want to find a way to make micrometers the default unit for convenience. This would mean that:

0.001 should be displayed as "1.00 nm"
1 should be displayed as "1.00 µm"
1000 should be displayed as "1.00 mm"

Is there a way to do this?



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.