Sometimes when you want your data to look uniform in Google Sheets, you have to round your numbers.
Having all your numbers rounded to the same amount of decimal places will make your spreadsheet much easier to read.
Learning how to round numbers is an important skill for all Google Sheets users, and there are a few different ways to do this in the program.
In this tutorial, I will cover everything you need to know about how to round numbers in Google Sheets.
Table of Contents
- 1 Using the Increase or Decrease Decimal Places Tool
- 2 Rounding Using Custom Formatting
- 3 The Different Round Functions Google Sheets
- 4 Closing Thoughts
Using the Increase or Decrease Decimal Places Tool
The fastest way to round your numbers in Google Sheets is to use the increase or decrease decimal places tool in the top toolbar menu.
Here’s how this is done:
- Select the cells that you want to round
- To round up select the Decrease decimal places option. If you want to increase decimal places, select that option
Using this method is a fast way to get all your numbers to look uniform, but you don’t have as many options if you’re trying round a specific way. For more control over who your sheet is rounded, you will need to use one of the round fucntions below
Rounding Using Custom Formatting
Another way to round is by changing the format of your data.
Here’s how this is done:
- Select the cells you want to round
- In the top menu select Format
- Then select Number in the drop-down menu
- If you want to round to two decimal places, you can select the Number option
If you want to round to more than two decimal places, instead of selecting the number option in step 4, you have to select More Formats, Custom number format.
From here you will enter this is a custom number format: #,##0.000. To keep more decimal places in your rounded number, you would add more zeroes at the end and then click the Apply button.
The Different Round Functions Google Sheets
In Google Sheets, there are 4 round functions that you should learn.
Using these round functions will provide you with greater flexibility on how you round numbers.
The 4 round functions are what they do:
- ROUND: Standard rounding rules
- ROUNDUP: Rounds a number up
- ROUNDDOWN: Rounds down a number
- MROUND: Rounds a number up to the nearest integer
Below I will show you how to use each of these round functions in Google Sheets.
How to Round Using the Round Function
The Round function takes two arguments: the first is what number you want to round, the second is how many decimal places to round.
Here’s how to use this formula
- Firstly, to start the function, select a cell and type the equals sign on your keyboard (=)
- Then type “round” and press tab on your keyboard
- Select the cell that contains the number that you want to round and then press a comma on your keyboard (,)
- Next, you will type how many decimal places you want to round to. 1 for 1 decimal place, 2 for 2 decimal places, 3 for 3 decimal places and so on.
- Close out your formula with an ending parenthesis “)” and press Enter on your keyboard
- You can copy and paste your formula down if you want to round for additional rows of data
The second argument in your function tells Google Sheets how many decimal places to round your number to.
Positive numbers mean rounding to the right of the decimal point, negative numbers mean rounding to the left of your decimal point.
Check out the rounded numbers in the image below to see how changing your number will affect the number decimal places that you are rounding to.
The ROUND function using standard rounding rules where anything greater than equal to or greater than 5 rounds up, and anything less than 5 rounds down.
How to Round Up with ROUNDUP
If you want to force Google Sheets to always round up, the ROUNDUP function is a great option.
The syntax for this function is the same as ROUND. The formula takes two arguments: the value you that you want to round, and how many places you want it to round to.
Check out the image below to see how changing the second value affects the rounded value.
How to Round Down with ROUNDDOWN
If you want to always round down in Google Sheets, you can use the ROUNDDOWN function. This works very similarly to the previous function we covered.
The formula syntax will be the same as the other function
Check out the example below to see how changing the second parameter of the function changes the number of decimal places that the formula is rounding to.
How to Round to the Nearest Whole Number with MROUND
Using the MROUND function will allow you to round to the nearest integer multiple of a value.
The syntax for MROUND is similar to the other round functions
The value is the number that you want to round, and the factor is the multiple that the value will be rounded to.
One important thing to note is that with MROUND your second parameter cannot be negative unless the first parameter is negative.
This is different from the other ROUND functions.
Here are some examples of how changing the second parameter affects your MROUND calculations:
So you can see in the examples above, the second parameter of the function controls what number Google Sheets is rounding to. It is rounding to multiples of the second parameter with MROUND.
- To round to the nearest multiple of 5: =MROUND(value, 5)
- To round to the nearest multiple of 10: =MROUND(value, 10)
- To round to the nearest multiple of 100: =MROUND(value, 100)
Whichever number you want to round to, you would set that as the second parameter in your formula.
If you’ve gone through this entire tutorial, you should now have a decent understanding of how to round numbers in Google Spreadsheets.
You can quickly limit the number of decimal places with the tools in the toolbar, use a custom number format, or use one of the four ROUND functions.
Which one you end up using will ultimately depend on what you are trying to accomplish.