Last Updated on October 31, 2023 by Jake Sheridan
In this tutorial, you will learn how to stop Google Sheets from rounding.
How To Stop Google Sheets From Rounding
If you work with numbers that have many decimal places in Google Sheets, you’ll probably have run into a case where the number displayed in the cell is rounded automatically.
You can see that this is happening because the full value will be visible in the formula bar when the cell is selected, but the rounded value will display in the cell
While this can sometimes be useful if you don’t need to know the exact value of a number, it can also lead to confusion and make troubleshooting calculation errors more difficult.
Using Automatic Number Formatting
If your number has 10 or fewer total digits, you can use Automatic Number Formatting to display the unrounded number in the cell.
Select the cell or range you want to remove rounding from, then open the Format menu, hover over the Number submenu to open it, then choose the Automatic option at the top
Your numbers will automatically display up to 10 digits. However note that if any numbers contain more than 10 digits they will still be rounded to 10
Increasing Decimal Places
If you need to use a different number format from the Automatic number format (for example, a currency format) you can increase the decimal places as needed to prevent Google Sheets from rounding
Select the cell or range you want to remove rounding from
Click on the Increase Decimal Places button on the toolbar, and you’ll see an additional decimal place appear in the cell
Repeat Step 2 as needed until all required decimal places are visible.
Note that, unlike the Automatic number formatting option above, this option will display zeros if you extend the number of decimal places beyond the digits of the number
Google Sheets Limitations
Note that the maximum number of digits Google Sheets supports for numbers is 15.
If you try to add more than 15 digits to a number, Google Sheets will remove any digits after the 15th.
You can store longer numbers by using the Plain Text number format option, but Google Sheets will round these numbers if used in in formulas
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to stop Google Sheets from rounding. Want more? Check out all the Google Sheets Tutorials.
More ways to clean data in Google Sheets:
- Clear Cells
- Clear Contents
- Clear Formatting
- Clear Filter
- Remove Dollar Sign
- Stop Google Sheets From Deleting Zeros
- Separate First And Last Name
- Format Phone Numbers
- Change Currency
- Add Dollar Sign
- Split Text To Columns
- Split Text to Rows
- Split Cells
- Split Text
- Find And Replace
- Find And Replace Wildcard
- Swap Cells
- Check if a Cell is Not Empty