When dealing with numbers, it is important to get the exact value. By default, Google Sheets will round the width of any value entered either up or down, unless you format the sheet correctly.
In this article, we will show you how to stop rounding numbers in Google Sheets, to get the exact value entered.
The width has been rounded up, but the value remains
What you should understand first is that although Google Sheets appears to round numbers up or down, it only does so visually. It will not change the actual value of the entered number. However, a cell formatted as currency will always display two decimal places by default, unless it is custom formatted.
Using the TRUNC() function
TRUNC(), or Truncate, is a built-in Google Sheets function that allows decimal places to be displayed without rounding up or down. Any non-displayed decimal places retain their value, not just shown. This is the simplest way to show exact numbers without having to specify a custom number format.
It is also very simple to use. Simply write the script in a cell where you want to display the unrounded number. The code format is as follows:
The symbol is ‘= TRUNC (value,[Places])’ where:
‘=’ is the command line that tells Google Sheets that this is a formatting script.
“TRUNC” is the command that specifies that whatever is entered will be truncated.
‘Value’ is the amount you wish to show and will not be rounded off
Places are the number of decimals you wish to display.
For example: If you want to display 123.45678 without rounding up or down, the code will be =TRUNC(123.45678,5). If you want to show only 123.456, the code will be =TRUNC(123.45678,3) and so on.
Of course, you can enter variables in the value section so you don’t have to enter numbers manually. For example, if you want to truncate the value of the number in cell A1 that shows up to five decimal places, the formula would be =TRUNC(A1,5). If you want to display the truncated value for the sum of two cells, you can enter the operation as =TRUNC(A1 + A2,5).
The value can also be another script. For example, the sum of several cells, A1 to A10 will be written as =SUM(A1:A10). If you wanted to show it cut to six decimal places, the formula would be =TRUNC(SUM(A1:A10),6). Just delete the equal sign for the second process so you don’t get an error.
The value can also be a number on another sheet. For example, you want to show the cut-off value of a number in cell A1 of Sheet2 up to five decimal places. You can write the formula as =TRUNC(Sheet2!A1,5). The ‘!’ It is an indication that the data you are trying to get is in another sheet. If the other sheet is renamed, for example, Products instead of Sheet2, then you’ll enter the formula as =TRUNC(Products!A1,5) instead.
Be careful of the syntax you use when writing into the formula. The code may not be case sensitive, but misplacing the comma or parentheses will cause the function to return an error. If you’re getting the #NAME error, then Google Sheets is having trouble selecting a value you entered. Check your code by clicking on it and looking at the value window right above the sheets. This is a long text box with fx to her right. If the cell contains a formula, it will always be shown here.
currency format
As mentioned earlier, a cell that is formatted to display currencies will only show up to two decimal places unless it is otherwise formatted. Doing this is fairly simple, but it won’t be obvious to those who don’t use spreadsheets regularly.
To format a cell to display more than two decimal numbers that are not rounded up or down, do the following:
- Click the cell you want to format.
- Click the Format menu in the top menu bar.
- Hover over the number to view more options.
- Hover over More Formats at the bottom of the list.
- Click Custom Number Format.
- Enter the number format you wish to use.
The list of number formats will display several types, and how the numbers will be displayed if each type is used. If you want to use the currency format, just type “$” before the hash marks. For example, if you want to display a currency with a thousand separator up to three decimal places, type “$#,####.###”. Each tag represents a possible number. Increase or decrease the number of ticks as you see fit.
If you want to use different currencies when hovering over more formats, choose More Currency instead of the custom number format. Change the currency you are currently using, then proceed to change the custom number format as described in the instructions above.
Simple but not immediately obvious tools
Getting the exact values of numbers in Google Sheets is very simple if you know how. The TRUNC() and custom number format options may not be immediately obvious to the average user, but they are great tools to have.
Do you have other tips on how to stop Google Sheets from rounding certain numbers? Share your thoughts in the comments section below.
