How To Disable Rounding In Google Sheets

When working with numbers, it’s important to get the exact value. By default, Google Sheets will round the display of any inputted value either up or down, unless you format the sheet properly.

How To Disable Rounding In Google Sheets

In this article, we’ll show you how to stop Google Sheets rounding numbers, to get the exact value entered.

Google Sheets Default Rounding Settings

What you should first understand is that although Google Sheets appears to round numbers up or down, it does so only visually. It won’t change the actual value of the inputted number. That said, a cell that’s formatted as currency will always show two decimal places by default, unless it’s custom formatted.

How To Disable Rounding In Google Sheets

Disable Rounding in Google Sheets using the TRUNC() Function

TRUNC(), or Truncate, is a function built into Google Sheets that allows the display of decimal places without rounding up or down. Any decimal places not displayed retain their value, they’re just not shown. This is the simplest method of showing exact numbers without having to define a custom number format.

Its use is also quite simple. Just type the script into a cell where you want the un-rounded number to be displayed. The Syntax of the code is as follows:

  • The code is ‘=TRUNC(Value,[Places])’ where:
  • ‘=’ is the command line telling Google Sheets that this is a format script.
  • ‘TRUNC’ is the command that determines that whatever is entered is to be truncated.
  • ‘Value’ is the amount that you wish to show that will not be rounded
  • ‘Places’ is the number of decimals you wish shown.

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

  1. In a new Google Sheet, enter ‘=TRUNC(123.45678,3)‘ and hit Enter. How To Disable Rounding In Google Sheets
  2. Of course, you can enter variables in the value section so you won’t have to enter numbers manually. For example, if you wish to truncate the value of the number in cell A1 showing up to five decimals, the formula would be =TRUNC(A1,5). If you wish to display the truncated value of the sum of two cells, you can input the process as =TRUNC(A1+A2,5). How To Disable Rounding In Google Sheets
  3. 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 want to show it truncated to six decimal places, the formula would be =TRUNC(SUM(A1:A10),6). Just omit the equals sign for the second process so you don’t get an error.
  4. The value can also be a number located in another sheet. For example, you want to show the truncated value of a number on cell A1 of Sheet 2 up to five decimal places. You can type the formula as =TRUNC(Sheet2!A1,5). The ‘!’ is an indicator that the data you’re trying to get is in another sheet. If the other sheet has been renamed, for example, products instead of sheet2, then you would enter the formula as =TRUNC(products!A1,5) instead. How To Disable Rounding In Google Sheets

Be careful of the syntax that you use when typing in the formula. The code may not be case sensitive, but misplacing a comma or a parenthesis will cause the function to return an error. If you’re getting a #NAME error, it means that Google Sheets is having trouble locating a value that you entered. Check your code by clicking on it and looking at the value window just above the sheets. This is the long text box with fx to the right of it. If a cell has a formula, it will always be displayed here.

Formatting Currencies in Google Sheets

As said beforehand, any cell formatted to display currencies will only show up to two decimal places unless formatted otherwise. Doing this is rather simple, but won’t be obvious to those who don’t use Sheets regularly.

To format a cell to display more than two decimals not rounded up or down, do the following:

  1. Click on the cell you want to format.
    How To Disable Rounding In Google Sheets
  2. Click on Format menu on the top menu bar.
    How To Disable Rounding In Google Sheets
  3. Hover over Number to display further options.
    How To Disable Rounding In Google Sheets
  4. Hover over More Formats at the bottom of the menu.
    How To Disable Rounding In Google Sheets
  5. Click on Custom number format.
    How To Disable Rounding In Google Sheets
  6. 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 you use each type. If you want to use a currency format just type ‘$’ before the hashtags. For example, if you want to display a currency with a thousand separator with up to three decimals, type in ‘$#,####.###’.
    How To Disable Rounding In Google Sheets

Each hashtag represents a potential number. Increase or decrease the number of hashtags as you see fit.

  1. If you want to use different currencies when hovering over More Formats, choose More Currencies instead of Custom number format.
  2. Change the currency you’re currently using, then proceed to change the Custom number format as indicated in the instructions above. How To Disable Rounding In Google Sheets

Google Sheets and Preventing Rounding

Getting the exact values of numbers in Google Sheets is quite simple if you know how. TRUNC(), and the Custom number format options, may not be immediately obvious to a casual user, but they’re great tools to have.

Do you have other tips on how to stop Google Sheets rounding certain numbers? Share your thoughts in the comments section below.

Sign up and earn $1000 a day ⋙

Driver Booster 12 Free: Keep 9.5M+ Drivers Up-to-Date in 1-Click

Driver Booster 12 Free: Keep 9.5M+ Drivers Up-to-Date in 1-Click

Driver Booster 12 Free is an effective tool that will keep your computers drivers up to date, which will make the system run faster and more reliably. This driver updater from IObit keeps your PC running at its best by checking for lost, out-of-date, or broken drivers immediately.

Advanced SystemCare 17 Free Review 2024 - Your Best PC Optimizer

Advanced SystemCare 17 Free Review 2024 - Your Best PC Optimizer

In an era where digital efficiency is paramount, Advanced SystemCare 17 Free emerges as a beacon for those seeking to enhance their PC's performance.

Summary of Movies & TV application shortcuts on Windows 10

Summary of Movies & TV application shortcuts on Windows 10

Summary of Movies & TV application shortcuts on Windows 10, Summary of Movies & TV application shortcuts on Windows 10 to bring you a great experience. Maybe

How to fix Messages Failed to Load error on Discord for Windows

How to fix Messages Failed to Load error on Discord for Windows

How to fix Messages Failed to Load error on Discord for Windows, Discord isn't fun if you can't read what other people write. Here's how to fix Messages error

How to display the This PC icon on the Windows 11 desktop

How to display the This PC icon on the Windows 11 desktop

How to display the This PC icon on the Windows 11 desktop, During the process of using Windows 11, many users need to access This PC (management).

How to find information in the Windows Registry quickly

How to find information in the Windows Registry quickly

How to find information in the Windows Registry quickly, Do you find it difficult to find information in the Windows Registry? So below are quick ways to find the registry

How to limit the number of failed login attempts on Windows 10

How to limit the number of failed login attempts on Windows 10

How to limit the number of failed login attempts on Windows 10. Limiting the number of failed password login attempts on Windows 10 helps increase computer security. Here's how

How to create fake error messages in Windows

How to create fake error messages in Windows

How to create fake error messages in Windows, Windows can come up with some pretty creative error messages but why don't you try creating your own content for them to make fun of?

Ways to open Windows Tools in Windows 11

Ways to open Windows Tools in Windows 11

Ways to open Windows Tools in Windows 11, Windows Administrative Tools or Windows Tools are still useful on Windows 11. Here's how to find Windows Tools in Windows 11.

How to fix Windows Quick Assist not working error

How to fix Windows Quick Assist not working error

How to fix Windows Quick Assist not working error, Windows Quick Assist helps you connect to a remote PC easily. However, sometimes it also generates errors. But,

How to pin Word, Excel and PowerPoint files to the corresponding app icon on the Windows 11 taskbar

How to pin Word, Excel and PowerPoint files to the corresponding app icon on the Windows 11 taskbar

How to pin Word, Excel and PowerPoint files to the corresponding app icon on the Windows 11 taskbar, How to pin Office files to the taskbar icon on Windows 11? Invite

How to fix the error of not being able to install software on Windows

How to fix the error of not being able to install software on Windows

How to fix the error of not being able to install software on Windows, Why can't you install apps or software on Windows 10/11? Here's everything you need to know about how to fix it

Instructions for deleting or changing PIN on Windows 11

Instructions for deleting or changing PIN on Windows 11

Instructions for deleting or changing the PIN code on Windows 11, In Windows 11, the PIN code is a very useful and convenient security tool for users. However some people

How to fix There Are Currently No Power Options Available error in Windows 10

How to fix There Are Currently No Power Options Available error in Windows 10

How to fix There Are Currently No Power Options Available error in Windows 10, Can't select power mode in Windows 10, what should I do? Here's how to fix the error

The simplest way to fix Photos application errors on Windows 10

The simplest way to fix Photos application errors on Windows 10

The simplest way to fix Photos app errors on Windows 10, what should I do if Microsoft Photos doesn't work? Don't worry about ways to fix Photos app errors on Windows

Instructions for installing keyboard shortcuts to switch input languages ​​on Windows 11

Instructions for installing keyboard shortcuts to switch input languages ​​on Windows 11

Instructions for installing shortcuts to switch input languages ​​on Windows 11. During the process of using Windows, users will often have to switch between methods.

How to check supported power status on Windows 11

How to check supported power status on Windows 11

How to check power status is supported on Windows 11, Windows 11 can handle many different power states. Here's how to check the power status

How to switch from 2.4GHz to 5GHz in Windows 10

How to switch from 2.4GHz to 5GHz in Windows 10

How to switch from 2.4GHz to 5GHz in Windows 10, If you want to find a quick and simple way to speed up the Internet, changing the WiFi band from 2.4GHz to 5GHz may help.

How to fix Not Enough Memory to Run Microsoft Excel error on Windows

How to fix Not Enough Memory to Run Microsoft Excel error on Windows

How to fix Not Enough Memory to Run Microsoft Excel error on Windows, Are you having an error of not enough memory to run Microsoft Excel? So, how to fix Not Enough Memory error

Ways to find all video files on Windows

Ways to find all video files on Windows

Ways to find all video files on Windows, Can't find recorded and saved videos on PC. Below are ways to help you find all clips on Windows.