How To Round In Google Sheets

Kieran Dixon Updated:  March 19, 2021

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):

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

• Functions
  • Pros:
    • You can round to a specific number of decimal places.
    • You can round to multiples of numbers (e.g. nearest 5, 10, 100, 1000)
    • You can control the rounding throughout a multi-step calculation.
  • Cons:
    • Rounding in multi-step calculations can result in inaccuracies.
• Formatting
  • Pros:
    • You can round to a specific number of decimal places.
    • The underlying numbers remain the same, they simply look rounded (no inaccuracies in multi-step calculations).
  • Cons:
    • You can only round to decimal places (not multiples).
    • The results can sometimes look incorrect.

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

• Functions
• Formatting

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:

• Round up or down based on the number (e.g. 0.4 = down, 0.5 = up)
• Just round down
• Just round up

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 is for rounding to a specific number of decimal places or to the nearest power of 10 (e.g. nearest 10, 100, 1000).
• MROUND is for rounding up or down to the nearest multiple of the specified factor (e.g. nearest 5, 10, 1000, 0.5).

ROUND Function

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

• The digit to the right of the rounding point is considered with the number rounded up if it's >=5 and down if <=4.
• Negative numbers are rounded down towards 0 and rounded up away from 0.

[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:

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:

MROUND Function

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

• Both value and factor must both be positive or both be negative.
• If either value or factor is zero, MROUND will return 0.
• If value is equally close to two multiples of factor, the greater multiple (using absolute value) will be returned. (This is the same as the ROUND function: Negative numbers are rounded down towards 0 and rounded up away from 0.)

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

Round Down With Google Sheets Functions

For rounding down you can choose one of the following:

• ROUNDDOWN is for rounding down to a specific number of decimal places or to the nearest power of 10 (e.g. nearest 10, 100, 1000).
• FLOOR is for rounding down to the nearest multiple of a specified factor (e.g. nearest 5, 10, 1000, 0.5).
• INT is for rounding down to the nearest whole number.

ROUNDDOWN Function

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.

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

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:

FLOOR Function

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

• value can be positive or negative.
• When value is positive, [factor] must be positive (and not 0).
• When value is negative, [factor] can be:
  • Positive or -1: value is rounded away from 0.
  • Negative (<= -2): value is rounded toward 0.
  • Not equal to 0.

[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).

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

INT Function

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.

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

Round Up With Google Sheets Functions

For rounding up you can choose between:

• ROUNDUP is for rounding up to a specific number of decimal places or to the nearest power of 10 (e.g. nearest 10, 100, 1000).
• CEILING is for rounding up to the nearest multiple of a specified factor (e.g. nearest 5, 10, 1000, 0.5).
• ODD is for rounding down to the nearest whole, odd number.
• EVEN is for rounding down to the nearest whole, even number.

ROUNDUP Function

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.

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

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:

CEILING Function

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

• value can be positive or negative.
• When value is positive, [factor] must be positive (and not 0).
• When value is negative, [factor] can be:
  • Positive: value is rounded toward 0.
  • Negative: value is rounded away from 0.
  • Not equal to 0.

[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).

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

ODD Function

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:

EVEN Function

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:

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:

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

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

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:

• the increase and decrease decimal places buttons
• custom number formats

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:

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

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

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

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:

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:

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

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.

STEP 2: Touch the formatting icon above the sheet:

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:

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

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:

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

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

Or the main menu (Format ➜ Number ➜ More Formats ➜ Custom number format):

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

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:

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:

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:

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

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

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:

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

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

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:

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

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

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.

You might like:

How To Subtract In Google Sheets

Google Sheets is good at subtracting... you can be too. Learn how to subtract numbers, columns, dates, times, percentages, all of it!

Generate A Random Item From A List In Google Sheets

Want Google Sheets to give you a random item from a list? Here are the exact formulas to get single or multiple outputs with no repeats.

Google Sheets Animation

You can animate an image in Google Sheets! Using pixel art and Apps Script you can create a short gif inside a spreadsheet.