Have you ever noticed that Google Sheets rounds off long decimal numbers to a few digits after the decimal point?
While this can help keep your spreadsheet looking neat and organized, it can also cause issues if you’re working with scientific data or other scenarios where every digit matters.
If you’re wondering, “how do I stop Google Sheets from rounding numbers?,” this guide is for you.
In today’s guide, we’ll explain why your decimal values are getting rounded and show you simple techniques to stop Google Sheets from rounding numbers so you can maintain full precision.
Two Quick Ways to Stop Google Sheets from Rounding Numbers
Before we delve into more details on how to stop Google Sheets from rounding numbers, let’s quickly review the two simple ways you can stop Google Sheets from rounding numbers.
- The first method is pretty simple and straight to the point. What you want to do is select the range of cells where you don’t want rounding to occur. Then, click the “Increase decimal places” button in the toolbar until you see the desired number of decimal places displayed.
- For the second method, you want to use the TRUNC function with this syntax: TRUNC(value, [places]).
The “value” in the syntax represents the cell containing the number you want, and “places” specifies how many decimal places to show. For example, =TRUNC(A1, 8) would display the value.
Why Does Google Sheets Round Numbers?
Before we look at ways to stop Google Sheets from rounding numbers, it’s helpful to understand some of the reasons this rounding occurs in the first place.
Here, take a look at some of them.
- Cell Formatting: By default, cells in Google Sheets are formatted to display a maximum of 11 digits total, including decimal places. So, if a number has more than 11 digits, Google Sheets will round it to fit.
- Scientific Notation – In some cases, Google Sheets may display very large or very small numbers in scientific notation (e.g. 1.23E+12), which can cause rounding.
Let’s dive into the first issue around the 11-digit limit to better illustrate why you may need to stop Google Sheets from rounding numbers in certain situations.
The 11 Digit Display Limitation
Google Sheets truncates numbers with more than 11 total digits to prevent them from overflowing into adjacent cells. For example:
- A number with 11 digits like 1.234567891 will display in full
- However, a number with 12 digits like 1.2345678921 will be rounded to 1.234567892
The screenshot above illustrates how Google Sheets handles numbers exceeding 11 digits. Notice that the value in cell A2 displays only 11 digits, rounding off the full 12-digit number. However, if you look at the formula bar when that cell is selected, you can see the complete 12-digit value.
This behavior demonstrates Google Sheets’ default rounding mechanism.
Your Cells Might Be Formated to Display Limited Number of Digits
Google Sheets is usually set up to show up to 11 digits in numbers. However, if you observe that your numbers are being rounded, this may be due to the cell formatting settings, which limit the display to fewer digits.
This rounding issue commonly occurs if your cells are formatted in any of the following ways:
- Number format
- Accounting format
- Financial format
- Currency format
With these formats applied, numbers entered into the cells will typically be rounded to two decimal places, regardless of the original number of decimals. Even when settings allow for more than two decimal places, you might still see the last decimals rounded off.
Specifically, if your cells are formatted in the currency format, any numbers you enter will be rounded to the nearest whole number.
Conversely, if your numbers appear in an exponential form, it’s likely because the cells are set to the Scientific format.
This formatting choice affects how numbers are displayed and can be adjusted based on your preference for data presentation.
The screenshot above shows the number 1.2345678921 been displayed in various formats.
If you find that your decimal numbers are being rounded off, there are two simple solutions to address this issue, which we will explain in the next section
Copy Sample Sheet
To better understand and practice the techniques we will show you in this guide, feel free to copy our sample sheet using the link below:
Two Effective Strategies to Stop Google Sheets From Rounding Numbers
By now, we are sure you now understand why Google Sheets rounds numbers. With this foundation, we’re ready to delve into the two distinct methods to prevent Google Sheets from rounding your numbers:
- Adjusting Cell Formatting to Display Additional Decimal Places
- Utilizing the TRUNC Function
Let’s look at each of these methods more practically.
Method 1: Increase the Displayed Decimal Places
This approach involves adjusting the cell formatting to show more decimal places than the default setting. By increasing the number of visible decimal digits, you can override the automatic rounding.
But don’t just take our words for it. Follow along as we show you how you can stop Google Sheets from rounding off numbers by simply increasing the displayed decimal places.
We are going to use the following sample data to show you how to stop Google Sheets from rounding numbers.
Now that we have our sample data put together, lets show you the step by step process of stopping Google Sheets from rounding off numbers.
Step 1: Select Cells or Cell
The first thing you want to do is select the cells or cell with data you’d like to stop Google Sheets from rounding off their numbers. For this example, let’s select cells A2.
Step 2: Locate the “Increase Decimal Places” Button
After selecting the cells or cell containing data you wish to prevent from being rounded in Google Sheets, your next step involves locating the “Increase Decimal Places” button. This button is usually found on the toolbar.
Here’s what to look for:
Step 3: Click to Increase Decimals
Now that you have located the increase decimal places on the toolbar, all you need to do to stop Google Sheets from rounding numbers is to click the increase decimal places numbers.
For every time you click this button, Google Sheets increase the decimal places.
For our sample data, if we click the increase decimal places twice, the number instantly transform to display the complete digit.
Here is what it looks like:
Looking at the screenshot above, you’ll notice that we’ve successfully adjusted the number in cell A2 to show its full value. This was accomplished with ease using the “Increase Decimal Places” button found on the toolbar of the spreadsheet.
So if you need to stop Google Sheets from rounding numbers, the method we just described above will come in handy.
Important Limitation: It’s worth noting that Google Sheets has a hard limit on the number of digits it can display for decimal values. Regardless of the decimal place formatting, Google Sheets will not show more than 16 total digits (including the decimal point) for any decimal number.
If a decimal number exceeds 16 digits, Google Sheets will automatically truncate the value by converting all digits after the 16th position to zeros. This truncation affects not just the visible display, but also the underlying data stored in that cell.
For example, if you try to enter 0.1234567891234567, Google Sheets will store and display it as 0.1234567891234560, dropping the last digit.
It’s important to note that this 16-digit limitation applies strictly to decimal numbers. Whole numbers without a decimal component can have more than 16 digits in Google Sheets.
While this restriction may seem frustrating, it is a fundamental design constraint of the software. Unfortunately, there is no workaround to bypass this decimal number length limit within Google Sheets itself.
Method 2: Using the TRUNC Function to Stop Google Sheets From Rounding Numbers
In the previous section, we demonstrated the method of using the “Increase Decimal Places” button in the toolbar to prevent Google Sheets from rounding numbers.
However, as previously mentioned, this is not the sole method for achieving this goal.
In this section, we will introduce the use of the TRUNC function as an alternative strategy to stop Google Sheets from rounding numbers.
But, before we dive into the detailed instructions, let’s first understand the TRUNC function’s syntax more clearly.
The syntax for the TRUNC function is as follows:
TRUNC(value, [places])
Here:
- value refers to the number you want to modify. This can be either a numeric value directly entered, or a cell reference containing the number.
- places is an optional parameter that specifies how many decimal places you want to keep in the result. If left blank, it defaults to 0, meaning the function will return only the integer portion of value.
Now that you fully understood the syntax for the TRUNC function, let’s quickly go over the step by step process of stopping Google Sheets from rounding numbers.
And we are going to use the following sample data to display the entire process:
Step 1: Choose a Blank Cell
The first thing you need to do is choose an area in your spreadsheet, where you want the result to be.
For our example, we will go with cell B1
Step 2: Type in the TRUCN Formula
Now that you have chosen where you want the result to be, navigate to the formula bar and type in the following formula:
=Trunc(A1,5)
Step 3: Confirm with Enter
Once you’ve input the formula as outlined in Step 2, simply press the Enter button on your keyboard. Google Sheets will then display the full number without rounding it in the chosen cell.
Here’s our result:
Note: To display a specific number of digits without rounding using the TRUNC function, simply adjust the second parameter (places) to your desired value:
- TRUNC(A1, 3) would show the value in cell A1 with only 3 decimal places
- TRUNC(A1, 6) would display 6 decimal places
If your number appears in scientific notation (e.g. 1.23E+12), you can revert it to a regular decimal format by selecting the cell and going to Format > Number from the main menu.
Keep in mind that while the TRUNC function eliminates rounding, Google Sheets still enforces a display limitation of 11 digits by default. So if you need to show more than 11 total digits:
- Use the TRUNC function to set the desired decimal places
- Additionally, click the ‘Increase decimal places’ button in the toolbar to reveal more digit positions
Combining these two techniques allows you to precisely control the number format.
This approach ensures you can view all required digits of your values with their full original precision in Google Sheets.
Final Thoughts
Throughout this guide, we’ve explored the nuances of Google Sheets’ number rounding behavior and introduced practical solutions to maintain precision in your data.
Understanding why Google Sheets rounds numbers—whether due to cell formatting constraints or the inherent 11-digit limit—provides the foundation for implementing effective strategies to display numbers accurately.
In today’s guide, we’ve detailed two straightforward methods to ensure your numbers are presented without unwanted rounding, including adjusting cell formatting to show more decimal places and employing the TRUNC function.
Each method serves a particular need, from quickly increasing the visible decimal places for immediate visual accuracy to utilizing the TRUNC function for precise control over decimal representation without rounding.
That said, it’s crucial to remember that while these techniques offer valuable ways to display your numbers as intended, Google Sheets imposes a hard limit on the number of digits for decimal values.
This limit underscores the importance of understanding the tools and functions at your disposal to work within these constraints effectively.
By following the steps outlined for each method, you can tailor your Google Sheets experience to suit your exact requirements, ensuring that your numbers reflect the desired level of detail.
Whether you’re working with scientific data, financial records, or any scenario where precision is key, these strategies empower you to maintain the integrity of your data in Google Sheets.