M. Shaiq Ansari
Today’s article is going to be about google sheets stop rounding numbers. Most of us face this problem when dealing with the numbers in google sheets, we want to get the desired round of a specific number, so we will see how to stop google sheets from rounding.
What is Rounding Numbers in Google Sheets?
Rounding in google sheets is about the decimal points any number can show after the point. Sometimes we want to show two decimals, sometimes one, and sometimes more than two, so it depends on our condition and requirements. Although, google sheets have a default behavior that converts all the decimals into it. But many times, we need these decimal points to be customized. So, there are two methods to do it, one is by using the option of “custom number format” inside the format menu. Another one is using a built-in formula TRUNC(value, [places]). Don’t get confused with this formula, we will discuss it in detail later in the step-by-step procedure.
Why do we need to learn – How to stop google sheets rounding numbers?
We all need to interact with numbers, a lot of numbers in data collection, manipulation, we need to manage large figures, calculations, and formulas managing the numeric data. Most of the time we need to add desired numbers of decimal points at different places. For instance, we want to show two decimal points for our numbers, three decimal points for our currency, and one decimal point for the percentages. Now the default behavior will not work here. We need some features or formulas to do the custom decimal points in our data.
- To show the desired number of decimal points in our currencies
- To design our data with more catchy figures such as $99.99
Download Workbook of Google Sheets Stop Rounding Numbers
How to Stop Rounding Numbers in Google Sheets
Method 1: How to stop google sheets from rounding using pre-defined options
Step 1
Add your numeric data with original decimal points.
Step 2
Keep the cell selected, go to format and then click on custom number format.
Step 3
Pick an option for the decimals points and click ok to apply the settings.
Method 2: How to stop google sheets from rounding using Formula
Step 1
Repeat step one same as above method.
Step 2
Click on any cell where the formula will apply, and start writing the formula; Formula: =TRUNC (value, [places]), in the places you can specify how many decimal points you want to show.
Step 3
You can also use this formula for mathematical operations such as Addition, Subtraction, Multiplication, Division, etc., and also it can be used with variables.
How to stop rounding in google sheets for Currencies
Select the cell in which you have placed your number in the form of a currency, go to format > click on numbers > click on the custom currency. Now select your currency. For different currency options, go 1 step back and click on “custom number format” pick any format from the list and click apply.
To recap, you can add your numbers directly into the formulas to stop them from rounding, or if the data is already made you can add custom formulas and select the text by the value of variables using the TRUNC formula. Understanding the formula is very important. Try to understand it and then you can use it in a variety of manners to perform your tasks accordingly.
Notes
- Carefully write the formula and the number of decimal points.
- Always remember to select the cell before applying any changes to it.
- You can use both the values and the variables in the TRUNC formula.
- You can use the values from corresponding sheets as well to stop rounding.
- =TRUNC(sheet name! cell address, number of decimal points)
- =TRUNC(sheet1!A4,3)
- Remember that the formulas are not “CAsE SenSitive”
- You can use currency or currency rounded, to unround and round currency values.
Conclusion
In this article, we learned how to stop google sheets from rounding, Rounding is one of the most important topics when we are using numeric data and working on mathematical or currency calculations. We learned about rounding, we learned how to stop rounding, how to increase or decrease decimal points, and much more. This article is designed for newbies and experts as well. If you like this information and you learned something new out of this, share this article with your classmates, your office colleagues, and your friends. Also, don’t forget to subscribe to the OfficeDemy blog for future updates.
