Google Sheets Cheat Sheet

How To Round In Google Sheets

You can round numbers using functions in formulas or formatting.

The functions you can use are ROUND, MROUND, ROUNDDOWN, FLOOR, INT, ROUNDUP, CEILING, ODD, and EVEN.

If you want to round using formatting instead, select the cell/s you want to round and click these icons in the toolbar (left for fewer decimal places, right for more):

shows the rounding icons in the main toolbar of google sheets

There's a couple of ways to round numbers in Google Sheets, each with pros and cons:

Knowing the pros and cons, click on the rounding method you want to use:

Rounding Using Functions In Google Sheets

There are quite a few Google Sheets functions to help round numbers.

Click on the type of rounding you want:

Just remember to be careful when rounding using functions because of the potential issues.

Round Either Up Or Down With Google Sheets Functions

For this you can use either the ROUND or MROUND functions:

ROUND Function

=ROUND(value,[places])

The ROUND function takes a number (value) and rounds it up or down to the specified number of decimal places using specific rules:

[places] is an optional argument that is assumed to be 0 if left out.

Here's a few examples to show you how it works:

ABCDE
1value[places]FormulaOutputRounding
23.14150=ROUND(3.1415)3Down
33.14151=ROUND(3.1415,1)3.1Down
43.14152=ROUND(3.1415,2)3.14Down
53.14153=ROUND(3.1415,3)3.142Up
6-3.14153=ROUND(-3.1415,3)-3.142Up (away from 0)
73.14154=ROUND(3.1415,4)3.1415None
83.14155=ROUND(3.1415,5)3.1415None

Notice that when [places] is greater than the number of decimal places present, zeros aren't appended to the number (e.g. 3.14150). Instead, insignificant zeros aren't displayed. (Formatting applied to a cell can change this.)

One cool trick to the ROUND function is that if you make [places] negative, it will round up or down to a specific number of places to the left of the decimal point:

ABCDE
1value[places]FormulaOutputRounding
2314150=ROUND(31415)31,415None
331415-1
(nearest 10)
=ROUND(31415,-1)31,420Up
431415-2
(nearest 100)
=ROUND(31415,-2)31,400Down
531415-3
(nearest 1,000)
=ROUND(31415,-3)31,000Down
631415-4
(nearest 10,000)
=ROUND(31415,-4)30,000Down
731415-5
(nearest 100,000)
=ROUND(31415,-5)0Down

An easy way to remember what you're rounding to when using negative [places] is to think about the negative number as the number of zeros after 1. This is the same as saying it's rounding to the nearest 10|[places]|:

  • [places] : -1 = 101 = 10
  • [places] : -2 = 102 = 100
  • [places] : -3 = 103 = 1000
  • [places] : -4 = 104 = 10000
  • [places] : -5 = 105 = 100000

MROUND Function

=MROUND(value,factor)

The MROUND function takes a number (value) and rounds it up or down to the nearest multiple of the specified factor following specific rules:

The value and factor do not have to be whole numbers. You can round a decimal to a multiple of another decimal.

Here's a few examples to show you how it works:

ABCDE
1valuefactorFormulaOutputRounding
217.861=MROUND(17.86,1)18Up
317.863=MROUND(17.86,3)18Up
4-17.86-5=MROUND(-17.86,-5)-20Up
5-17.86-15=MROUND(-17.86,-15)-15Down
617.862.5=MROUND(17.86,2.5)17.5Down
717.867=MROUND(17.86,7)21Up

Round Down With Google Sheets Functions

For rounding down you can choose one of the following:

ROUNDDOWN Function

=ROUNDDOWN(value,[places])

The ROUNDDOWN function takes a number (value) and rounds it down to the specified number of decimal places.

[places] is an optional argument that is assumed to be 0 if left out.

Negative numbers are rounded towards 0.

The ROUNDDOWN function works just like the ROUND function except that it always rounds down.

Here's a few examples to show you how it works:

ABCDE
1value[places]FormulaOutputRounding
23.14150=ROUNDDOWN(3.1415)3Down
33.14151=ROUNDDOWN(3.1415,1)3.1Down
43.14152=ROUNDDOWN(3.1415,2)3.14Down
53.14153=ROUNDDOWN(3.1415,3)3.141Down
6-3.14153=ROUNDDOWN(-3.1415,3)-3.141Down (towards 0)
73.14154=ROUNDDOWN(3.1415,4)3.1415None
83.14155=ROUNDDOWN(3.1415,5)3.1415None

As with the ROUND function, when [places] is greater than the number of decimal places present, zeros aren't appended to the number (e.g. 3.14150). Instead, insignificant zeros aren't displayed. (Formatting applied to a cell can change this.)

If [places] is negative, ROUNDDOWN will round down to a specific number of places to the left of the decimal point:

ABCDE
1value[places]FormulaOutputRounding
2314150=ROUNDDOWN(31415)31,415None
331415-1
(nearest 10)
=ROUNDDOWN(31415,-1)31,410Down
431415-2
(nearest 100)
=ROUNDDOWN(31415,-2)31,400Down
531415-3
(nearest 1,000)
=ROUNDDOWN(31415,-3)31,000Down
631415-4
(nearest 10,000)
=ROUNDDOWN(31415,-4)30,000Down
731415-5
(nearest 100,000)
=ROUNDDOWN(31415,-5)0Down

Remember what you're rounding to when using negative [places] by thinking about the negative number as the number of zeros after 1. This is the same as saying it's rounding to the nearest 10|[places]|:

  • [places] : -1 = 101 = 10
  • [places] : -2 = 102 = 100
  • [places] : -3 = 103 = 1000
  • [places] : -4 = 104 = 10000
  • [places] : -5 = 105 = 100000

FLOOR Function

=FLOOR(value,[factor])

The FLOOR function takes a number (value) and rounds it down to the nearest multiple of the factor following specific rules:

[factor] is an optional argument that is assumed to be 1 if left out (so the function rounds down to the nearest whole number and away from 0 if negative).

FLOOR is often used in financial formulas to get currency values to the nearest $0.05 or $0.25.

Here's a few examples to show you how it works:

ABCDE
1value[factor]FormulaOutputRounding
217.861=FLOOR(17.86,1)17Down
317.863=FLOOR(17.86,3)15Down
4-17.86-5=FLOOR(-17.86,-5)-15Toward 0
5-17.86-15=FLOOR(-17.86,-15)-15Toward 0
6-17.86-15=FLOOR(-17.86,15)-30Away from 0
717.867=FLOOR(17.86,7)14Down

FLOOR has two sister functions (FLOOR.PRECISE and FLOOR.MATH). These functions have similar arguments but can provide more flexibility, particularly when dealing with negative numbers and varying the way they round (towards or away from 0).

INT Function

=INT(value)

The INT function takes a number (value) and rounds it down to the nearest whole number (also known as an INTeger).

Negative numbers are rounded down away from 0.

For positive numbers, another way to describe this function is that INT simply cuts off the decimals: 3.141 cuts to 3.141 = 3.

Because INT rounds down away from 0, it is effectively the same as:

  • FLOOR with a negative value and factor of ±1.
  • CEILING with a negative value and factor of -1.

Here's a few examples to show you how INT works:

ABCD
1valueFormulaOutputRounding
29.4=INT(9.4)9Down
3-16.2=INT(-16.2)-17Away from 0
421=INT(21)21None
5-18.2=INT(-18.2)-19Away from 0
66.1=INT(6.1)6Down
7-2.5=INT(-2.5)-3Away from 0

Round Up With Google Sheets Functions

For rounding up you can choose between:

ROUNDUP Function

=ROUNDUP(value,[places])

The ROUNDUP function takes a number (value) and rounds it up to the specified number of decimal places.

[places] is an optional argument that is assumed to be 0 if left out.

Negative numbers are rounded away from 0.

The ROUNDUP function works just like the ROUND function except that it always rounds up.

Here's a few examples to show you how it works:

ABCDE
1value[places]FormulaOutputRounding
23.14150=ROUNDUP(3.1415)4Up
33.14151=ROUNDUP(3.1415,1)3.2Up
43.14152=ROUNDUP(3.1415,2)3.15Up
53.14153=ROUNDUP(3.1415,3)3.142Up
6-3.14153=ROUNDUP(-3.1415,3)-3.142Up
73.14154=ROUNDUP(3.1415,4)3.1415None
83.14155=ROUNDUP(3.1415,5)3.1415None

As with the ROUND function, when [places] is greater than the number of decimal places present, zeros aren't appended to the number (e.g. 3.14150). Instead, insignificant zeros aren't displayed. (Formatting applied to a cell can change this.)

If [places] is negative, ROUNDUP will round down to a specific number of places to the left of the decimal point:

ABCDE
1value[places]FormulaOutputRounding
2314150=ROUNDUP(31415)31,415None
331415-1
(nearest 10)
=ROUNDUP(31415,-1)31,420Up
431415-2
(nearest 100)
=ROUNDUP(31415,-2)31,500Up
531415-3
(nearest 1,000)
=ROUNDUP(31415,-3)32,000Up
631415-4
(nearest 10,000)
=ROUNDUP(31415,-4)40,000Up
731415-5
(nearest 100,000)
=ROUNDUP(31415,-5)10,0000Up

Remember what you're rounding to when using negative [places] by thinking about the negative number as the number of zeros after 1. This is the same as saying it's rounding to the nearest 10|[places]|:

  • [places] : -1 = 101 = 10
  • [places] : -2 = 102 = 100
  • [places] : -3 = 103 = 1000
  • [places] : -4 = 104 = 10000
  • [places] : -5 = 105 = 100000

CEILING Function

=CEILING(value,[factor])

The CEILING function takes a number (value) and rounds it up to the nearest multiple of the factor following specific rules:

[factor] is an optional argument that is assumed to be 1 if left out (so the function rounds up to the nearest whole number and away from 0 if negative).

CEILING is often used in financial formulas to get currency values to the nearest $0.05 or $0.25.

Here's a few examples to show you how it works:

ABCDE
1value[factor]FormulaOutputRounding
217.861=CEILING(17.86,1)18Down
317.863=CEILING(17.86,3)18Down
4-17.86-5=CEILING(-17.86,-5)-20Away from 0
5-17.86-15=CEILING(-17.86,-15)-30Away from 0
6-17.86-15=CEILING(-17.86,15)-15Toward 0
717.867=CEILING(17.86,7)21Down

CEILING has two sister functions (CEILING.PRECISE/ISO.CEILING and CEILING.MATH). These functions have similar arguments but ultimately provide the same functionality.

ODD Function

=ODD(value)

The ODD function takes a number (value) and rounds it up to the nearest whole, odd number.

Negative numbers are rounded up away from 0.

Here's a few examples to show you how ODD works:

ABCD
1valueFormulaOutputRounding
29.4=ODD(9.4)11Up
3-16.2=ODD(-16.2)-17Away from 0
421=ODD(21)21None
5-18.2=ODD(-18.2)-19Away from 0
66.1=ODD(6.1)7Up
7-2.5=ODD(-2.5)-3Away from 0

EVEN Function

=EVEN(value)

The EVEN function takes a number (value) and rounds it up to the nearest whole, even number.

Negative numbers are rounded up away from 0.

Here's a few examples to show you how EVEN works:

ABCD
1valueFormulaOutputRounding
29.4=EVEN(9.4)10Up
3-16.2=EVEN(-16.2)-18Away from 0
421=EVEN(21)22Up
5-18.2=EVEN(-18.2)-20Away from 0
66.1=EVEN(6.1)8Up
7-2.5=EVEN(-2.5)-4Away from 0

Because all even numbers are a multiple of 2, INT is effectively the same as:

  • FLOOR with a negative value and factor of 2.
  • CEILING with:
    • a positive value and factor of 2.
    • a negative value and factor of -2.

The Problem With Rounding Using Formulas

You can run into problems if you round numbers at each stage of a multi-step calculation.

Rounding too aggressively can mean your end result isn't as correct as you may want it to be.

Let's look at an example of purchasing some items from overseas.

There's the quantity and price, but you also have to think about the exchange rate (which can go to many decimal places depending on the currency).

If you round to 2 decimal places for the dollar value of each item before totaling, you might get a different answer than if you simply round at the very last step.

Here's some numbers that show this happening:

ABCDE
1QuantityPriceXRTotalsRounded
2Formulas:=A3*B3*C3=ROUND(D3,2)
34$10.491.278986$53.666253$53.67
49$6.281.278986$72.288289$72.29
517$4.231.278986$91.971883$91.97
618$1.651.278986$37.985884$37.99
74$5.951.278986$30.439867$30.44
8$286.35$286.36

When the prices are rounded at each step in Column E, the total is $286.36.

When the prices are rounded at the end in Column D, the total is $286.35.

That's only a $0.01 difference.

But what if there were more items, higher prices, and more transactions?

These rounding errors can add up quickly.

The lesson: Be careful when rounding with functions and if you must, do it at the last step.

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Rounding Using Formatting In Google Sheets

You can 'round' numbers up and down in Google Sheets by changing the formatting of the cell in which the number lives.

With this rounding method, you don't actually change the number (like functions do).

Instead, the number appears as rounded to the user but the full number is still available for calculations.

There are two ways to round using formatting in Google Sheets:

Both methods prevent the errors with rounding in multi-step calculations. However, it does present problems of its own.

Rounding Using Increase / Decrease Decimal Places Buttons

Not on desktop? Click for mobile instructions: iPhone/iPad and Android.

STEP 1: Select the cell, range, column, and/or row you want to round:

how to select cells to format in google sheets

Don't worry if there's text data in your selection, changing the decimal formatting won't affect it.

You can make multiple separate selections by holding down Ctrl (Windows, Chrome OS) or ⌘ (Mac) while you click.

STEP 2: Click on the 'Decrease/Increase decimal places' buttons in the toolbar:

where to find the increase and decrease decimal places button in the google sheets toolbarusing the increase and decrease decimal places buttons in google sheets

TRY IT: You can practice using the 'Decrease decimal places' and 'Increase decimal places' toolbar buttons right now:

AB
1OriginalFormatted
20.577210.57721
31.6181.618
42.718282.71828
53.1415926543.141592654
64.66924.6692
777

When changing the decimal formatting of a range of cells up or down, the number of places is determined by the top-left cell.

If the top-left cell is currently rounded to 5 places, decreasing the decimal places will change the entire range to be rounded to 4 decimal places.

Similarly, increasing the decimal places will change the entire range to be rounded to 6 decimal places.

Rounding Using Increase / Decrease Decimal Places Buttons On The Google Sheets iPhone and iPad App

STEP 1: Select the cell, range, column, or row you want to round by touching them.

STEP 2: Touch the formatting icon above the sheet:

location of the formatting icon in the google sheets iphone and ipad app

STEP 3: Navigate to the 'Cell' tab of the formatting menu, scroll to the 'Decimal places' section, and use the up and down arrows to increase or decrease the decimal places:

where to find the increase and decrease decimal places button in the google sheets iphone and ipad app

Here's a clip of the iPhone/iPad decimal place rounding process:

shows the complete process of increasing and decreasing decimal places using formatting in the google sheets iphone and ipad app

Rounding Using Increase / Decrease Decimal Places Buttons On The Google Sheets Android App

STEP 1: Select the cell, range, column, or row you want to round by touching them.

When using the Android app be careful not to select a range that includes text data. Doing so means the decimal place increase and decrease buttons won't work.

I suspect this is a bug in the software that will be fixed in the future as on all other platforms text data is simply ignored.

STEP 2: Touch the formatting icon above the sheet:

location of the formatting icon in the google sheets android app

STEP 3: Navigate to the 'Cell' tab of the formatting menu, scroll to the 'Decimal places' section, and use the up and down arrows to increase or decrease the decimal places:

where to find the increase and decrease decimal places button in the google sheets android app

Here's a clip of the Android decimal place rounding process:

shows the complete process of increasing and decreasing decimal places using formatting in the google sheets android app

Rounding Using Custom Number Formats

Not on desktop? Click for mobile instructions: iPhone/iPad and Android.

Custom number formats are a very powerful and versatile tool in Google Sheets.

Just one of the many things they can accomplish is rounding numbers.

STEP 1: Select the cell, range, column, and/or row you want to round:

how to select cells to format in google sheets

Don't worry if there's text data in your selection, changing the custom number format won't affect it.

You can make multiple separate selections by holding down Ctrl (Windows, Chrome OS) or ⌘ (Mac) while you click.

STEP 2: Access the custom number formatting popup using either the toolbar (123 dropdown ➜ More FormatsCustom number format):

where to find the custom number format option using the google sheets toolbar

Or the main menu (FormatNumberMore FormatsCustom number format):

where to find the custom number format option using the google sheets format menu

STEP 3: You'll now see the custom number formatting popup. Here you can change the input text to change the number formatting:

changing custom number formatting to round to a specific number of decimal places in google sheets

Notice that the 'Sample' provides you with a live update of how your entry will affect a sample number.

Here are some examples of custom number formats and their output:

ABCD
1OriginalFormattingDescriptionOutput
20.57721#,##0Whole number1
31.618#,##0.01 decimal place1.6
42.71828#,##0.002 decimal places2.72
53.141592265#,##0.0003 decimal places3.142
64.6692#,##0.00004 decimal places4.6692
77#,##0.000005 decimal places7.00000

The hashes (#) hide insignificant zeros and the comma (,) helps readability for large numbers. You can change this if it's not suitable for your specific situation.

There is a quick way to format to two decimal places.

You can simply choose the standard Number formatting:

how to access the standard number formatting to round to two decimal places in google sheets

You achieve the same thing using the following keyboard shortcuts:

  • Windows or Chrome OS:
    • Ctrl+Shift+1
    • Alt+H,K
    • Alt+O,N,N
  • Mac:
    • Ctrl+Shift+1

Rounding Using The Number Format On The Google Sheets iPhone and iPad App

Unfortunately, specifically designed custom number format inputting (e.g. #,##0.0000) is not accessible on the iPhone and iPad app.

However, you can still access the standard Number format (e.g. #,##0.00) to quickly round to two decimal places.

STEP 1: Select the cell, range, column, and/or row you want to round.

STEP 2: Touch the formatting icon above the sheet:

location of the formatting icon in the google sheets iphone and ipad app

STEP 3: Navigate to the 'Cell' tab of the formatting menu, scroll down and touch the 'Number format' section:

where to find the number formatting options in the google sheets iphone and ipad app

STEP 4: In the new menu, select 'Number':

how to apply standard number formatting in the google sheets iphone and ipad app

Rounding Using The Number Format On The Google Sheets Android App

Unfortunately, specifically designed custom number format inputting (e.g. #,##0.0000) is not accessible on the Android app.

However, you can still access the standard Number format (e.g. #,##0.00) to quickly round to two decimal places.

STEP 1: Select the cell, range, column, and/or row you want to round.

STEP 2: Touch the formatting icon above the sheet:

location of the formatting icon in the google sheets android app

STEP 3: Navigate to the 'Cell' tab of the formatting menu, scroll down and touch the 'Number format' section:

where to find the number formatting options in the google sheets android app

STEP 4: In the new menu, select 'Number':

how to apply standard number formatting in the google sheets android app

The Problem With Rounding Using Formatting

Rounding using formatting gets you the most correct answer because the numbers within calculations aren't rounded throughout the process.

However, this can lead to results that don't look correct.

Take a look at these numbers:

ABC
111.551.6
223.263.3
3Total4.814.8

Column B works and makes sense.

Column C contains the numbers from Column B rounded to 1 decimal place.

Here we see the problem:

1.6 + 3.3 ≠ 4.8

The calculation is 100% correct. But it looks like the spreadsheet is getting the wrong answer.

Be mindful of the potential for this issue when rounding using formatting in Google Sheets.


FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.