How to Stop Google Sheets from Rounding

How to Stop Google Sheets from Rounding

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.
Number Formats
Number formats with pre-formatted decimals.

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.

Original Number
Unrounded number in the formula bar.

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.

Number Limit
Maximum limit of 15 digits in each cell.

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:

  1. Select the cell or cells that you want to format.

    Select cell
    Select a cell or a cell range.

  2. From the Format menu, hover over the Number option. Then, from the sub-menu, choose Automatic.

    Automatic Format
    Click on Format > Number > Automatic.

  3. You will notice the cells displaying numbers up to 10 digits.

    Un-rounded Numbers
    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.

  1. Select the cell or cells you want to round.

    Select cell
    Select the cell.

  2. From the toolbar, press the Increase decimal places icon. Continue pressing this icon until you reach the decimal places you want displayed.

    Increase Decimal Places
    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:

  1. 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.

    Edit Mode
    Double-click on the cell.

  2. Type the following formula:
    =TRUNC()

    Enter Formula
    Enter the TRUNC() formula.

  3. 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.

    Number Argument
    Input the Number argument.

  4. Specify the number of digits you want to keep after the decimal.

    Num Digits Argument
    Input the Num Digits argument.

  5. Hit Enter, and you will notice the number of digits after the decimal.

    Truncated Number
    Selected number truncated.

Phoebe is an HR assistant in her day job, but is also a web blogger that loves writing guides and sharing her experiences. When she is not out with her friends, she enjoys traveling or binge-watching Netflix. She graduated with a BA in Communication and Media Studies from the University of Utah, and is a chief editor at TechObservatory.

Leave a Comment