2

Is there any way to do this:

I have cell and most of the numbers have 2 significant digits like 3.45. But once in a while I have a number like 143 big number, the rest are under 10. Currently for 143 the cell is displaying 143.00 which takes up too much space. I want it to show 143 just three digits. Is there a way to format it so it only shows 3 digits? no matter where the decimal point is?

3

What you're looking for is conditional number formatting. You can do this in the Google doc by highlighting the column you wish to format and clicking:

Format → Number → More Formats → Custom number format...

Thereafter, specify this as your formula:

[<10]_(###0.00_);_(#0_)

What it means is, if the cell value is less than 10 then format the cell in #,##0.00 format otherwise format as #0.

You can read more info over here:

Display decimals only when the number is less than 1 [Excel number formatting tip]

As well as here:

Number Formats in Microsoft Excel

| improve this answer | |
  • 1
    I just got around to looking at the links you provided... very very useful. Especially the last one. Never seen anything so comprehensive. Didn't know you could do all that. Thanks. – jason Feb 28 '15 at 2:48
  • If a number is 0.0001123 your method will round to 0.00, but 3 significant digits would dictate rounding to 0.000112 – par Dec 16 '19 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.