By default, Google Sheets displays numbers as rounded. However, double-clicking on the cell will still show the full, unrounded number. It only displays it as rounded for aesthetic purposes.
Additionally, Google Sheets only permits 15 digits in a cell (excluding the decimal mark). Any digits in excess will automatically be converted to 0.
Rounding can cause inaccuracies with calculations where you need more exact numbers. This guide will show how to stop Google Sheets from displaying numbers as rounded.
How does Google Sheets round numbers?
For example, we will display a 15-digit long number, 25.1234567890543, in different formats. Some of the standard number formats are:
- Plain text – By default, displays 10 digits in a cell.
- Number – By default, it shows only 2 digits after the decimal.
- Accounting – By default, displays only 2 digits after the decimal.
- Financial – By default, it shows only 2 digits after the decimal.
- Currency – By default, it shows only 2 digits after the decimal.
Note that the rounded numbers are not removed. The original numbers remain intact in the formula bar. Double-click on the cell to check the original unrounded number.
If you increase the length of the number to more than 15 digits, the numbers after the 15th digit will automatically be removed from the formula bar as well. For example, if you type 25.123456789054321, a 17-digit long number, the 16th, and 17th digits, will be removed from the formula bar.
Option #1: Disable rounding by changing the Number format
If you have a number with a format that permits only a few numbers after the decimal, you can change the format to Automatic. This will allow you to display up to 10 digits in a cell. Follow the steps below:
- Select the cell or cells that you want to format.
Select a cell or a cell range. - From the Format menu, hover over the Number option. Then, from the sub-menu, choose Automatic.
Click on Format > Number > Automatic. - You will notice the cells displaying numbers up to 10 digits.
Selected number stopped from rounding.
Option #2: Disable rounding by increasing decimal places
This is the only method that allows you to display up to 15 digits in a single cell.
- Select the cell or cells you want to round.
Select the cell. - From the toolbar, press the Increase decimal places icon. Continue pressing this icon until you reach the decimal places you want displayed.
Press the Increase decimal places icon.
Option #3: Disable rounding using the TRUNC() Function
TRUNC() is a built-in function that returns the integer part of a number without the decimal places. It gives you control over the number of digits you want displayed after the decimal.
Note: the TRUNC() function only allows 10 digits to be displayed in a cell.
Syntax
=TRUNC(number, [num_digits])Arguments
- number = the number you want to truncate.
- num_digits = [Optional] specifies the number of decimal places you want to keep.
Follow the steps below to use the TRUNC() function to stop rounding 25.1234567890543:
- Double-click on the cell where you want to display the un-rounded number. It will trigger the edit mode. In our example, I’m clicking on cell B13.
Double-click on the cell. - Type the following formula:
=TRUNC()
Enter the TRUNC() formula. - Within the parentheses, enter the number or refer to the cell you want to truncate. Then, enter a comma. In my case, I am selecting cell A13.
Input the Number argument. - Specify the number of digits you want to keep after the decimal.
Input the Num Digits argument. - Hit Enter, and you will notice the number of digits after the decimal.
Selected number truncated.
