Google Sheets rounds off numbers in cells by default to fit them within the column width or to show only a certain number of decimal places based on the cell format. However, the actual underlying number remains intact and is used in calculations.
There are two main methods to stop Google Sheets from visually rounding numbers in cells:
Table of Contents
Using the Increase Decimal Places Button
The quickest way is to select the cell(s) and click the Increase Decimal Places button in the toolbar.
Increase Decimal Places Button
This will change the cell format from General or Automatic to Number and incrementally display more decimal places with each click, preventing rounded values.
The cell width expands automatically to fit the full number. You can keep clicking until all decimal places are shown.
Limitations:
- Only allows up to 16 digits total including decimal points
- Changes cell formatting to Number instead of keeping General/Automatic
Using the TRUNC Function
The TRUNC function truncates a number to a specified number of decimal places without rounding.
=TRUNC(number, num_digits)
Where:
number
is the cell reference containing the full numbernum_digits
is the number of decimals to display
For example:
=TRUNC(A1, 5)
Shows the number from cell A1 truncated to 5 decimal places.
Benefits:
- Allows showing a large number of decimals without rounding
- Doesn’t modify cell formatting
- Can be used alongside other functions
Other Solutions
Increase Column Width
Manually increasing the width of the column containing rounded numbers also prevents rounding in many cases.
Custom Number Format
Applying a custom number format allows complete control over decimal places.
For example, format cells to show 15 decimals:
0.000000000000000
When Google Sheets Rounds Numbers
There are several reasons Google Sheets may round numbers:
Cell Format Has Limited Decimals
Currency and Percentage formats show only 2 and 0 decimals by default.
Number Has More Than 11 Digits
Cells only show a maximum of 11 digits including decimals. Additional digits get rounded.
Column Width Too Narrow
Numbers get rounded if the cell width cannot fit the entire number.
Scientific Notation
Large or small numbers get converted to scientific notation e.g. 1.2E+11 which rounds values.
Tips to Avoid Rounding
- Use the General or Automatic format instead of Currency %, etc.
- Widen columns to fit numbers
- Increase decimals before applying functions
- Show 5-6 extra decimals to prevent cumulative rounding errors
Examples
=TRUNC(123.456789, 5)
Result: 123.45679
The number is truncated to 5 decimals instead of rounding at 2.
=TRUNC(SUM(A1:A10), 7)
Result: 553.2346279
The SUM is truncated to 7 decimals instead of the default 2.
Conclusion
The key things to remember are:
- Google Sheets only rounds numbers visually, underlying values remain precise
- Use the Increase Decimal Places button or TRUNC function to quickly avoid rounding numbers
- Make columns wider or apply custom formats if needed for extra control
Stopping rounding ensures you always see the full precision of numbers in Google Sheets for accurate data analysis.
References
[1] https://www.alphr.com/google-sheets-disable-round/
[2] https://support.google.com/docs/thread/12612671[3] https://www.benlcollins.com/spreadsheets/google-sheets-custom-number-format/