My Excel spreadsheet is calculating some decimal values. Depending on the input value, the result may be e.g.


I would like these values to appear as


that is limit the maximum number of decimal digits but not set the minimum one, allowing it to be 0 or more as needed.

With "general" format, I get the minimum required but unlimited number of digits, i.e. 0, 0.5, 0.0416667.
With "number" format, I get 0.0000 and 0.5000 if I want to get 0.0417.

Any way around? FWIW I am on Excel 2010.

  • 1
    Why would trailing zeros be a problem? A value of 0.0000 would be equal to 0. The same for 0.5 and 0.500. Right now there doesn't seem to be an easy way to do this. Of course you could always use the ROUND function and not use a specific format. – Seth May 26 '17 at 10:15
  • @Seth because of readability. In my case 0 means there's no data. Redundant digits increase "noise". – texnic May 27 '17 at 7:00

In case, if you need to keep the values not rounded, try custom format 0.####:

enter image description here

This is just one extra point off (in 0.) from the described format.

  • This modifies the value. There are easier ways to do that, and it's not what he wanted – Aganju May 26 '17 at 17:32
  • @g.kov: I know you already edited the answer, however, I'd just recommend removing the first part you mention using TEXT function ... just keep it using the format as you put in your edit . that's the correct answer ... – Ditto May 26 '17 at 18:48

If changing the value is not an issue for you (you wrote 'appear as'), you can use =ROUND(...) around whatever you calculate, or in a new column. The second parameter defines to how many digits you want it rounded, for example
=ROUND(2/3,4) will give you 0.6667.

  • In principle, rounding would be fine. So it was good to receive this option to consider. However I prefer changing the format rather than the formula in each column. – texnic May 28 '17 at 19:56

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.