How to Round Numbers in Google Sheets

Web banner showing math symbols from the Insert Special Characters Add-On
Insert any Math Symbol with One Click

Rounding numbers in a spreadsheet can be pretty straightforward. However, you must carefully use the correct method as many choices exist. We will start with the easiest way, simply changing the cell format to show fewer significant digits.

Grab a copy of the template to work with the examples in your Google Drive.

Rounding with Number Formatting

Changing the number format changes how Google Sheets displays a number in a cell. It does not change the actual value of the number. For example, if you have the number 1.5 in a cell and change the number format to display no decimal places, the number will be displayed as 2. However, the actual value of the number in the cell is still 1.5.

You can safely round numbers for display purposes, even if you plan to use them in calculations later. But if you use a rounded number in a calculation, the actual value used will differ from what’s displayed.

Adding two rounded numbers and coming up with an unexpected result
Summing Numbers Rounded with Formatting

This image shows that 2 + 2 = 3. Is that right? Well, sort of. Next, let’s look at how to change numbers instead of just changing their displayed values.

Rounding with Functions

Depending on your needs, there are three main ways to round numbers: regular rounding, rounding up, and rounding down. Let’s start with regular rounding.

Regular Rounding (Up or Down)

Regular rounding approximates a value by moving it slightly up OR down. Google Sheets provides two ways to do this. You can use the ROUND function to apply standard rounding to a specified number of decimal places, or MROUND to round to a specified multiple. We’ll look at the ROUND function first.

ROUND Function

=ROUND(value, [places])

  • value – The number that you want to round.
  • places – Optional. The number of decimal places that you want to round to. If you don’t specify a place value, the default is 0.


ROUND applies standard rounding principles, which we all learned in math class. It rounds everything below five down and everything five and above up. Let’s look at an example.

ROUND Function Example 1

Round 123.4567 to two decimal places:

=ROUND(123.4567, 2)

This function will decrease the decimals from four to two and return the value of 123.46.

ROUND Function Example 2

Round 123.4567 to the nearest integer:

=ROUND(123.4567, 0)

This example removes four levels of precision from 123.4567 by changing it to 123.

ROUND Function Example 3

The function accepts different inputs for the places argument to control how many decimal places Google Sheets shows. Using negative numbers removes significant digits to the left of the decimal place.

Using the ROUND function with different inputs for places
Rounding to Different Places

You may want to round up or down to a multiple other than ten. Let’s look at that next.

MROUND Function

=MROUND(value, factor)

value – The number for the function to round

factor – The multiple to round with

MROUND Function Example 1

The MROUND function rounds a number to a specified factor instead of a place. For example, you may round currency to the nearest nickel. Let’s look at an example.

Round $1.21 to the nearest nickel:

=MROUND(1.21, .05)

This rounding example would change $1.21 to $1.20.

MROUND Function Example 2

The factor argument changes the multiple to which Google Sheets rounds the values. You can round negative or positive numbers. Here is a table showing both with different factors.

The MROUND function rounding positive and negative numbers with different factors
Rounding Using Different Factors

The positive and negative values move toward the same absolute values.

These two functions cover the options for rounding up or down. Now let’s look at rounding only in one direction.

Rounding Up

There are several options for rounding numbers up in Google Sheets. Let’s break them down and discuss each function.

ROUNDUP Function

=ROUNDUP(value, [places])

  • value – The number that you want to round.
  • places – Optional. The number of decimal places that you want to round to. If you don’t specify a place value, the default is 0.

The ROUNDUP function in Google Sheets rounds a number up to a specified number of decimal places.

ROUNDUP Example 1

ROUNDUP is simple to understand compared to the other functions in this tutorial. This table illustrates its usage.

Using the ROUNDUP function with different inputs for places
Rounding Up to Different Places

Rows 2 and 3 have the same result even though the places arguments (3 and 2) are different. This difference is because Google Sheets only shows decimal places to the last significant digit unless you apply formatting to force insignificant digits to display.

Google Sheets rounds the results in rows 4 and 5 to 1 and 0 decimal places, respectively. The 123.5 and the 124 are the new values, with the decimal values discarded.

The negative values for places in rows 6 through 8 are removing significant digits. These results show that the ROUNDUP function can remove more than decimals if you use a negative number for the places argument.

ROUNDUP Example 2

This function also rounds negative numbers. Let’s take the previous example and change the sign to negative.

=ROUNDUP(-123.4567, 0)

This formula returns the value -124. If you want more control over how the function rounds negative numbers, consider using the CELING.MATH or FLOOR.MATH.

Note: Google Sheets replaced the CEILING function with CEILING.MATH and CEILING.PRECISE for more control over negative numbers.

CEILING.MATH Function

=CEILING.MATH(number, [significance], [mode])

number – The number for the function to round

significance – Optional. The multiple to round up to. The default value is 1.

mode – Optional. The rounding direction for negative numbers. This argument has no impact on positive numbers.

  • 0 – Round toward zero
  • 1 – Round away from zero

The CEILING.MATH function rounds a number up to the nearest multiple of a given significance while giving you control over how it rounds negative numbers. Let’s break this down.

CEILING.MATH Example 1

The CEILING.MATH function rounds a number up to a specified significance. For example, you may round times up to the next half-hour. Let’s look at an example.

Round 1:20 PM up to the nearest half-hour:

=CEILING.MATH("1:20 PM", ":30")

This rounding example changes 1:20 PM to 1.30 PM.

CEILING.MATH Example 2

Unlike CEILING.PRECISE, the CEILING.MATH function lets you control which direction it rounds negative numbers. In this image, we use both modes to switch between toward and away from zero.

Rounding Using Different Modes

You can see the 0 mode rounding negative numbers toward zero while a non-zero mode rounds numbers away from zero.

CEILING.PRECISE Function

=CEILING.PRECISE(number, [significance])

number – The number for the function to round

significance – Optional. The multiple to round up to. The default value is 1.

The CEILING.PRECISE function is similar to the CEILING.MATH function, except it does not have a mode argument to control the rounding of negative numbers. Use this function if you want something simpler than CEILING.MATH.

Rounding Down

Rounding down offers even more options than rounding up. Let’s explore them so you can find the best one for your spreadsheet. We’ll start with the most straightforward function, which rounds down by places.

ROUNDDOWN Function

=ROUNDDOWN(value, [places])

  • value – The number that you want to round.
  • places – Optional. The number of decimal places that you want to round to. If you don’t specify a place value, the default is 0.

The ROUNDDOWN function in Google Sheets rounds a number down to a specified number of decimal places.

ROUNDOWN Example 1

Let’s try different values for the places argument using 345.678 every time. We’ll see different levels of rounding as a result.

Using the ROUNDDOWN function with different inputs for places
Rounding Down to Different Places

Rows 2 and 3 have the same result even though the places arguments (3 and 2) are different. This difference is because Google Sheets only shows decimal places to the last significant digit unless you apply formatting to force Google Sheets to show insignificant digits. Therefore, the 456.78 in row 2 is only shown as 456.78 since the ending 0 does not impact the number’s value.

The function completely removed as all of the significant digits in the last row. Be careful when using a negative number for the places argument as the results differ by orders of magnitude.

ROUNDOWN Example 2

This function also rounds negative numbers. Let’s change value‘s sign to negative.

=ROUNDDOWN(-123.4567, 0)

This formula returns the value -123, which is a different result than the INT function provides. Read more about the difference between INT and ROUND in Google Sheets.

INT Function

=INT(value)

value – The number that you want to round down.

The INT function in Google Sheets rounds a number down to the nearest integer. This function can simplify large numbers or convert decimal values to integers. 

INT Example 1 – Round a Decimal to a Whole Number

To round down the number 12.345 to 12, you would use the following formula:

=INT(12.345)

This simple formula returns 12.

INT Example 2 – Negative Numbers

Next, let’s look at using INT on a negative number. We’ll use 12.345 again but switch the sign to -12.345.

=INT(-12.345)

The result is -13. It is essential to realize this is a different result than rounding a number with the ROUND function. See a comparison of INT and ROUND for more.

FLOOR.MATH Function

=FLOOR.MATH(number, [significance], [mode])

number – The number for the function to round

significance – Optional. The multiple to round down to. The default value is 1.

mode – Optional. The rounding direction for negative numbers. This has no impact on positive numbers.

  • 0 – Round away from zero
  • 1 – Round toward zero

The FLOOR.MATH function rounds a number down to the nearest multiple of a given significance, while giving you control over how it rounds negative numbers. Let’s take a look.

FLOOR.MATH Example 1

The FLOOR.MATH function rounds a number down to a specified significance. For example, you may round a price down to the nearest nickel. Let’s look at an example.

Round $1.23 down to the nearest nickel:

=FLOOR.MATH(1.23, .05)

This rounding example changes $1.23 to $1.20. Notice that you need to add the currency symbol with formatting; it is not part of the formula.

FLOOR.MATH Example 2

Unlike FLOOR.PRECISE, the FLOOR.MATH function lets you control which direction it rounds negative numbers. In this image, we use both modes to switch between toward and away from zero.

The FLOOR.MATH function producing different results using different modes
Rounding Using Different Significance

You can see the 0 mode rounding negative numbers away from zero while a non-zero mode rounds numbers toward zero.

FLOOR.PRECISE Function

=FLOOR.PRECISE(number, [significance])

number – The number for the function to round

significance – Optional. The multiple to round down to. The default value is 1.

The FLOOR.PRECISE function is similar to the FLOOR.MATH function, except it does not have a mode argument to control the rounding of negative numbers. Use this function if you want something simpler than FLOOR.MATH.

TRUNC Function

=TRUNC(value, [places])

value – The number that you want to truncate.

places – An optional argument specifying the number of digits. The default is 0.

The TRUNC function removes unwanted digits from a number. The TRUNC function is similar to the INT function, but TRUNC accepts a place argument and treats negative numbers differently. 

TRUNC Function – Example 1

The following formula changes the number 12.345 to 12:

=TRUNC(12.345)

TRUNC does not follow standard rounding rules. It simply removes numbers.

TRUNC Function – Example 2

Next, let’s look at using TRUNC on a negative number. We’ll switch the sign to -12.345 and keep the places argument at the default of 0.

=TRUNC(-12.345)

The result is -12. It is essential to realize this is a different result than rounding a number with the INT function, which would have returned -13.

Conclusion

Google Sheets offers a variety of rounding options. This tutorial can help you decide which option is best for rounding up or down. When done correctly, rounding can make spreadsheets easier and faster for users to understand.

YouTube Video

YouTube player