How to Stop Google Sheets From Rounding – 2 Easy Ways

You might have noticed that Google Sheets usually rounds off decimal numbers that have too many digits after the decimal point. This is desirable in most cases, as it makes sure that the number does not overstep the column boundary into the adjacent cell, so your worksheet does not look untidy.

However, this could become an issue if you’re dealing with scientific data where accuracy to the least significant digit may be critical. In some cases, you might find your number displayed in exponent format (for example 1.23E+03).

If you’re wondering how to get Google Sheets to stop rounding, then keep reading! In this tutorial, we will go over some of the reasons your decimal numbers are getting rounded off, and show you some easy ways to stop Google Sheets from rounding.

How to Stop Google Sheets From Rounding – The Quick Answer

There are two ways to stop Google Sheets from rounding:

1. Select the range of cells you want to stop rounding and click the Increase decimal places button in the tool meu until you get to the desired amount of decimal places.
2. Use the TRUNC function with the following syntax TRUNC(value, [places]). Value is the cell you want to collect data from and places is where you can express how many decimal places you want to show.

Why Is Google Sheets Rounding Up?

Let’s look at why the problem is occurring. There might be several reasons for your numbers getting rounded in Google Sheets.

Here are a few probable ones:

• Your cells might be formatted to display a lesser number of digits.
• Your cells might be in the Scientific format.

Before we look at how to make Google Sheets stop rounding numbers. Let us see how to tackle each of the above issues.

Your Number Might Have More Than 11 Digits (Including the Decimal Point)

Google Sheets, by default, permits only 11 digits (in total) to be displayed in a cell. Any digits after that are rounded off.

For example, in the example below, Google Sheets displays the entire cell when the number has 11 digits in total. However, when your number has 12 or 13 digits (as shown in cells A3 and A4), Google Sheets rounds the numbers off to the 11th digit.

 

This is because the cells in Google Sheets are pre-formatted to display not more than 11 digits. It’s important to understand here that even though the cells display just 11 digits, they do not actually remove the 12th and 13th digits.

In other words, the underlying number remains unchanged and whole. So when you use the cell reference in computation, the entire original number gets used (with the digits after the 11th one), and not just the digits that you see in the cell.

Note: To look at the original number, simply click on the cell and you can see the actual contents of the cell in the formula bar (as shown in the above screenshot).

Your Cells Might be Formatted to Display Lesser Number of Digits

Besides this, the default formatting of certain cells might round off a decimal number to just one to two decimal places.

If your cell has been formatted in one of the following formats, you will most likely see your numbers rounding off to 2 (or sometimes more) digits:

• Number format
• Accounting format
• Financial format
• Currency format

Cells that are set to one of the above formats automatically round off values to two decimal places. Even if they have been formatted to display more than 2 (but less than 11) decimal places, you will find the decimals, in the end, getting rounded off.

If your cell has been formatted in the Currency (rounded) format, you will find your decimals getting completely rounded off to a whole number!

And if you find your numbers getting displayed in exponential form, then your cells are most likely in the Scientific format.

The above image shows the same number, 12.0123456789 displayed in different formats.

No matter the reason for your decimal numbers showing up as rounded numbers, the problem can be resolved in two easy ways.

Two Easy Ways to Stop Google Sheets from Rounding Decimal Numbers

There are two methods when looking at how to make Google Sheets not round the last few digits and display the entire number:

• Formatting the cell to show more decimal places
• Using the TRUNC Function

How to Stop Rounding in Google Sheets by Changing the Formatting

The easiest way to stop Google Sheets from rounding is by changing the formatting. I walk through this and other steps in my video on the subject. Below, I also include screenshots of my spreadsheet that also shows how to stop Google Sheets from rounding.

The “change formatting” method directly affects the cell and formats it to display more decimal places. It involves simply selecting the cell(s) that you want to format and clicking on the ‘Increase decimal places’ button. You will find this button in the toolbar, and it looks like this:

Each time you press this button, the selected cell will display one more decimal place of the underlying number. So, as you can see in the below example, pressing the button once will display the entire number stored in cell A3, while pressing it twice will display the entire number stored in cell  A4, as shown below. In this way, you can display your entire number without rounding.

Note: Google Sheets does not allow decimal numbers with more than 16 digits (including the decimal point). Any digit after that is automatically converted to 0, even if you press the ‘Increase decimal places’ button. This goes for not just the number displayed, but the underlying original number too. This is a basic design limitation of Google Sheets, and there’s not much that you can do about it. This is not the case, however, for whole numbers.

How to Turn Off Rounding in Google Sheets With the Trunc Function (Google Sheets Truncate)

This method is useful if you want to display more decimal places in a separate cell without affecting the original cell. The TRUNC function is used to reduce or increase the precision of a given value. The word TRUNC is short for ‘Truncate’.

The syntax for this function is as follows:

TRUNC(value, [places])

Here,

• value is the number that you want to work with. It can be a numeric value or a reference to a cell containing a number.
• The places parameter is optional. It specifies the number of decimal places that we want to keep. The value of this parameter is 0 by default. So if this parameter is not specified, the function returns only the integer part of the value.

The TRUNC function basically truncates the given number to a certain number of significant digits by omitting less significant digits, but without rounding. So, if we want to display the numbers of cells A2 to A4 (in the following screenshot) without rounding, we can use the TRUNC function as follows:

 

The cells A2:A4 (in the screenshot above) all contain the same underlying value: 12.056789. Since they had been formatted as Number, the cells A2:A4 show only two decimal places and round off the second digit to 6.

Note: If you choose to display a lesser number of digits without rounding, all you need to do is change the second parameter of the TRUNC function accordingly. So if you want to display just 3 significant digits of cell A1, you can use TRUNC(A1, 3).

If you see your number getting displayed in exponent form, you can change it to the Number format by selecting the cell and navigating to Format->Number from the main menu.

Note: If you want to display more than 11 digits, then you will need to press the ‘Increase decimal places’ button, even after using the TRUNC function.

Conclusion

In this tutorial, we discussed why Google Sheets rounds off decimal numbers with a large number of decimal places as well as how to stop Google Sheets from rounding them.

We encourage you to experiment with these methods and with different decimal places to better understand how Google Sheets works with decimal numbers. Now you know exactly how to get Google Sheets to stop rounding your numbers!