How to Disable and Turn Off Number Rounding in Google Sheets Cells

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:

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 number
  • num_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/