How To Disable Rounding In Google Sheets

The first thing you should understand is that although Google shows sheets as rounded up or down, it only does so visually. This will not change the actual value of the input number. That said, a cell formatted as a currency will always show two decimal places by default unless it is custom formatted.

Disable Rounding on Google Sheets using the TRUNC () function

TRUNC (), or truncate, is a function built into Google Sheets that allows you to display decimal places, not round or down. Any decimal place that does not appear retains their value, they are not shown. This is the easiest way to show the exact number without defining a custom number format.

It is also quite easy to use. Just type the script into a cell where you want to display the non-circular number. The syntax of the code is as follows:

The code is ‘= TRUNC (value, [space])’ where:

'=' Is the command line that tells Google Sheets that it is a formatting script.

'TRUNC' is the command that specifies that whatever is entered will be truncated.

'Value' is the amount you want to show that will not be rounded

'Space' is the decimal number you show.

For example: if you want to display 123.45678 without rounding up or down, the code will be = TRUNC (123.45678,5).

In a new Google sheet, type '= TRUNC (123.45678,3)' and press Enter. Google Sheets - Trunk function

Of course, you can enter variables in the value section so that you do not have to enter numbers manually. For example, if you want to show the value of the number up to five decimals in cell A1, the formula would be = TRUNC (A1,5). If you want to display the trimmed value of the sum of two cells, you can input the process as = TRUNC (A1 + A2,5). Google Sheets - Trunk Function 2

Value may be another script. For example, the sum of several cells will be written as A1 to A10 = SUM (A1: A10). If you want to minimize it to six decimal places, the formula will be = TRUNC (SUM (A1: A10), 6). Exclude the equal sign for the second process so you don't get any errors.

The value can also be a number located on another sheet. For example, you want to show the cut value of a number up to five decimal places in cell A1 of sheet 2. You can type the formula = TRUNC (Sheet2! A1,5). '!' Is an indicator that the data you are trying to get is on another sheet. If the other sheet is renamed, for example, product instead of sheet 2, you will write the formula = TRUNC (products! A1,5) instead. Google Sheets - Trunk Function 3

Be careful about the syntax you use when typing formulas. The code may not be case sensitive, but if you move a comma or parentheses incorrectly, the function will return an error. If you have received a #NAME error, this means that Google Sheets is having trouble identifying a value you entered. Check your code by clicking on it and looking at the standard window just above the sheets To its right is a long text box with fx. If a cell has a formula, it will always appear here.

Currency formatting in Google Sheets

As mentioned earlier, any cell formatted for displaying currency will only show up to two decimal places unless otherwise formatted. It's pretty easy to do, but it won't be obvious to those who don't use regular sheets.

To format a cell that does not show more than two decimal rounds 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 display more options.

Hover over more formats at the bottom of the menu

Click on the custom number format.

Enter the number format you want to use. The list of number formats will show different types and how the numbers will be displayed if you use each type. If you want to use a currency format, type '$' before the hashtag. For example, if you want to display a currency with thousands of divisors up to three decimals, type ‘$ #, ####. ###’.

Each hashtag represents a potential number. Increase or decrease the number of hashtags you like.

If you want to use different currencies when hovering over more formats, choose more currencies instead of custom number formatting.

Change the currency you are currently using, then proceed to change the custom number format indicated in the instructions above. Google Sheets - Custom Currency

Google Sheets and Prevent Rounding

Finding the exact number of numbers in Google Sheets is easy if you know-how. TRUNC (), and custom number formatting options may not be immediately obvious to a casual user, but they are a great tool to have.

About Debmalya Datta

Welcome to our blog “CYBER SUGGEST“. Debmalya Datta is a professional Blogger, Developer, Social Media Influencer, Digital Marketer and Entrepreneur. He loves doing work which makes him happy, that’s why he choose Blogging and Educate people. You will love exploring educational stuff on our blog. Live your dreams as much as you can. Be a SHAMELESS SELF-PROMOTER