Excel automatically rounds to 10+ decimal places

Hi all,

Sometimes when I am doing a formula and press enter, the result is a number with so many decimal places. Is this because of a setting? Is there a setting that automatically rounds the number to 2 decimal places without having to manually do it each time.

Thanks for your help.

Originally Posted by jason4444

Sometimes when I am doing a formula and press enter, the result is a number with so many decimal places. Is this because of a setting? Is there a setting that automatically rounds the number to 2 decimal places without having to manually do it each time.

Not exactly. You might consider clicking File > Options > Advanced and setting the "Precision as displayed" (PAD) option.

I do not recommend that option.

And if you experiment with it, make a backup copy of the Excel file before setting the option.

PAD will permanently change constant cell values that you enter with more decimal places than the cell format displays.

But note that setting PAD causes cell values to be rounded to the number of decimal places specified by the cell format. That is not necessarily the number of decimal places displayed, if the cell is formatted as General.

Moreover, PAD affects only the final cell value. It does not affect the precision of values used or derived within formulas.

So setting PAD still might not solve all of your problems with the default precision of Excel calculations.

Originally Posted by AliGW

Select the column - choose number format. It should set it automatically at 2 decimal places, but you can use the buttons to adjust it.
Formulaically, you'd do it this way: =ROUND(your_formula,2)

Originally Posted by AliGW

Thanks for the rep!

Jason, of course you specifically asked for a solution "without having to manually do it each time". So I assume that you rejected the ROUND(...,2) solution.

And if you opted for the first solution -- setting the column format -- that might not be the right solution for you.

Note that setting the format only changes the appearance of values [1]. The actual value still has many more decimal places, and those extra decimal places affect dependent calculations.

[1] Unless you also set the option "Precision as displayed", which I deprecate and discuss earlier in this thread.