Google Sheets – Prevent Unwanted Rounding of Numbers

google sheets

This:

3.1415926535

produces:

3.141592654

How can I have it output the whole number as is?

I was using this with TO_TEXT(), getting the same result, but I guess the problem lies prior to that. The default setting seems to be to round numbers to the number of decimals in 3.141592654 (9).

I will stress that I cannot use TEXT() to format numbers explicitly, because the source data (coming in from 3rd party) appears over many lines (10000+) and I just need to reference the exact input values as is.

The "right way" to do this would be to set PLAIN TEXT format on the cell. However with existing value like 3.1415926535, that would end up with loss of data because the output will be 3.141592654. This is not acceptable for 10000+ rows.

Best Answer

The solution is quite simple. Press this button to increase the number of decimals: enter image description here

Now it will show the correct amount of decimals:
enter image description here

Please be aware that this works only up to 15 digits. Beyond that, Google Spreadsheets will ignore the rest.

Related Topic