# How To Round In Google Sheets

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

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

A | B | C | D | E | |

1 | value | [places] | Formula | Output | Rounding |

2 | 3.1415 | 0 | =ROUND(3.1415) | 3 | Down |

3 | 3.1415 | 1 | =ROUND(3.1415,1) | 3.1 | Down |

4 | 3.1415 | 2 | =ROUND(3.1415,2) | 3.14 | Down |

5 | 3.1415 | 3 | =ROUND(3.1415,3) | 3.142 | Up |

6 | -3.1415 | 3 | =ROUND(-3.1415,3) | -3.142 | Up (away from 0) |

7 | 3.1415 | 4 | =ROUND(3.1415,4) | 3.1415 | None |

8 | 3.1415 | 5 | =ROUND(3.1415,5) | 3.1415 | None |

Notice that when [places] is greater than the number of decimal places present, zeros aren't appended to the number (e.g. 3.1415**0**). 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:

A | B | C | D | E | |

1 | value | [places] | Formula | Output | Rounding |

2 | 31415 | 0 | =ROUND(31415) | 31,415 | None |

3 | 31415 | -1 (nearest 10) | =ROUND(31415,-1) | 31,420 | Up |

4 | 31415 | -2 (nearest 100) | =ROUND(31415,-2) | 31,400 | Down |

5 | 31415 | -3 (nearest 1,000) | =ROUND(31415,-3) | 31,000 | Down |

6 | 31415 | -4 (nearest 10,000) | =ROUND(31415,-4) | 30,000 | Down |

7 | 31415 | -5 (nearest 100,000) | =ROUND(31415,-5) | 0 | Down |

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 = 10
^{1}= 1**0** - [places] : -2 = 10
^{2}= 1**00** - [places] : -3 = 10
^{3}= 1**000** - [places] : -4 = 10
^{4}= 1**0000** - [places] : -5 = 10
^{5}= 1**00000**

#### 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__.)

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:

A | B | C | D | E | |

1 | value | factor | Formula | Output | Rounding |

2 | 17.86 | 1 | =MROUND(17.86,1) | 18 | Up |

3 | 17.86 | 3 | =MROUND(17.86,3) | 18 | Up |

4 | -17.86 | -5 | =MROUND(-17.86,-5) | -20 | Up |

5 | -17.86 | -15 | =MROUND(-17.86,-15) | -15 | Down |

6 | 17.86 | 2.5 | =MROUND(17.86,2.5) | 17.5 | Down |

7 | 17.86 | 7 | =MROUND(17.86,7) | 21 | Up |

### 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.

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:

A | B | C | D | E | |

1 | value | [places] | Formula | Output | Rounding |

2 | 3.1415 | 0 | =ROUNDDOWN(3.1415) | 3 | Down |

3 | 3.1415 | 1 | =ROUNDDOWN(3.1415,1) | 3.1 | Down |

4 | 3.1415 | 2 | =ROUNDDOWN(3.1415,2) | 3.14 | Down |

5 | 3.1415 | 3 | =ROUNDDOWN(3.1415,3) | 3.141 | Down |

6 | -3.1415 | 3 | =ROUNDDOWN(-3.1415,3) | -3.141 | Down (towards 0) |

7 | 3.1415 | 4 | =ROUNDDOWN(3.1415,4) | 3.1415 | None |

8 | 3.1415 | 5 | =ROUNDDOWN(3.1415,5) | 3.1415 | None |

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.1415**0**). 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:

A | B | C | D | E | |

1 | value | [places] | Formula | Output | Rounding |

2 | 31415 | 0 | =ROUNDDOWN(31415) | 31,415 | None |

3 | 31415 | -1 (nearest 10) | =ROUNDDOWN(31415,-1) | 31,410 | Down |

4 | 31415 | -2 (nearest 100) | =ROUNDDOWN(31415,-2) | 31,400 | Down |

5 | 31415 | -3 (nearest 1,000) | =ROUNDDOWN(31415,-3) | 31,000 | Down |

6 | 31415 | -4 (nearest 10,000) | =ROUNDDOWN(31415,-4) | 30,000 | Down |

7 | 31415 | -5 (nearest 100,000) | =ROUNDDOWN(31415,-5) | 0 | Down |

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 = 10
^{1}= 1**0** - [places] : -2 = 10
^{2}= 1**00** - [places] : -3 = 10
^{3}= 1**000** - [places] : -4 = 10
^{4}= 1**0000** - [places] : -5 = 10
^{5}= 1**00000**

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

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:

A | B | C | D | E | |

1 | value | [factor] | Formula | Output | Rounding |

2 | 17.86 | 1 | =FLOOR(17.86,1) | 17 | Down |

3 | 17.86 | 3 | =FLOOR(17.86,3) | 15 | Down |

4 | -17.86 | -5 | =FLOOR(-17.86,-5) | -15 | Toward 0 |

5 | -17.86 | -15 | =FLOOR(-17.86,-15) | -15 | Toward 0 |

6 | -17.86 | -15 | =FLOOR(-17.86,15) | -30 | Away from 0 |

7 | 17.86 | 7 | =FLOOR(17.86,7) | 14 | Down |

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

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

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

A | B | C | D | |

1 | value | Formula | Output | Rounding |

2 | 9.4 | =INT(9.4) | 9 | Down |

3 | -16.2 | =INT(-16.2) | -17 | Away from 0 |

4 | 21 | =INT(21) | 21 | None |

5 | -18.2 | =INT(-18.2) | -19 | Away from 0 |

6 | 6.1 | =INT(6.1) | 6 | Down |

7 | -2.5 | =INT(-2.5) | -3 | Away from 0 |

### 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.

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:

A | B | C | D | E | |

1 | value | [places] | Formula | Output | Rounding |

2 | 3.1415 | 0 | =ROUNDUP(3.1415) | 4 | Up |

3 | 3.1415 | 1 | =ROUNDUP(3.1415,1) | 3.2 | Up |

4 | 3.1415 | 2 | =ROUNDUP(3.1415,2) | 3.15 | Up |

5 | 3.1415 | 3 | =ROUNDUP(3.1415,3) | 3.142 | Up |

6 | -3.1415 | 3 | =ROUNDUP(-3.1415,3) | -3.142 | Up |

7 | 3.1415 | 4 | =ROUNDUP(3.1415,4) | 3.1415 | None |

8 | 3.1415 | 5 | =ROUNDUP(3.1415,5) | 3.1415 | None |

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.1415**0**). 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:

A | B | C | D | E | |

1 | value | [places] | Formula | Output | Rounding |

2 | 31415 | 0 | =ROUNDUP(31415) | 31,415 | None |

3 | 31415 | -1 (nearest 10) | =ROUNDUP(31415,-1) | 31,420 | Up |

4 | 31415 | -2 (nearest 100) | =ROUNDUP(31415,-2) | 31,500 | Up |

5 | 31415 | -3 (nearest 1,000) | =ROUNDUP(31415,-3) | 32,000 | Up |

6 | 31415 | -4 (nearest 10,000) | =ROUNDUP(31415,-4) | 40,000 | Up |

7 | 31415 | -5 (nearest 100,000) | =ROUNDUP(31415,-5) | 10,0000 | Up |

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 = 10
^{1}= 1**0** - [places] : -2 = 10
^{2}= 1**00** - [places] : -3 = 10
^{3}= 1**000** - [places] : -4 = 10
^{4}= 1**0000** - [places] : -5 = 10
^{5}= 1**00000**

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

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:

A | B | C | D | E | |

1 | value | [factor] | Formula | Output | Rounding |

2 | 17.86 | 1 | =CEILING(17.86,1) | 18 | Down |

3 | 17.86 | 3 | =CEILING(17.86,3) | 18 | Down |

4 | -17.86 | -5 | =CEILING(-17.86,-5) | -20 | Away from 0 |

5 | -17.86 | -15 | =CEILING(-17.86,-15) | -30 | Away from 0 |

6 | -17.86 | -15 | =CEILING(-17.86,15) | -15 | Toward 0 |

7 | 17.86 | 7 | =CEILING(17.86,7) | 21 | Down |

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

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:

A | B | C | D | |

1 | value | Formula | Output | Rounding |

2 | 9.4 | =ODD(9.4) | 11 | Up |

3 | -16.2 | =ODD(-16.2) | -17 | Away from 0 |

4 | 21 | =ODD(21) | 21 | None |

5 | -18.2 | =ODD(-18.2) | -19 | Away from 0 |

6 | 6.1 | =ODD(6.1) | 7 | Up |

7 | -2.5 | =ODD(-2.5) | -3 | Away from 0 |

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

A | B | C | D | |

1 | value | Formula | Output | Rounding |

2 | 9.4 | =EVEN(9.4) | 10 | Up |

3 | -16.2 | =EVEN(-16.2) | -18 | Away from 0 |

4 | 21 | =EVEN(21) | 22 | Up |

5 | -18.2 | =EVEN(-18.2) | -20 | Away from 0 |

6 | 6.1 | =EVEN(6.1) | 8 | Up |

7 | -2.5 | =EVEN(-2.5) | -4 | Away 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:

A | B | C | D | E | |

1 | Quantity | Price | XR | Totals | Rounded |

2 | Formulas: | =A3*B3*C3 | =ROUND(D3,2) | ||

3 | 4 | $10.49 | 1.278986 | $53.666253 | $53.67 |

4 | 9 | $6.28 | 1.278986 | $72.288289 | $72.29 |

5 | 17 | $4.23 | 1.278986 | $91.971883 | $91.97 |

6 | 18 | $1.65 | 1.278986 | $37.985884 | $37.99 |

7 | 4 | $5.95 | 1.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

__12 exclusive tips__ to make user-friendly sheets from today:

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:

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.

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:

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

A | B | |

1 | Original | Formatted |

2 | 0.57721 | 0.57721 |

3 | 1.618 | 1.618 |

4 | 2.71828 | 2.71828 |

5 | 3.141592654 | 3.141592654 |

6 | 4.6692 | 4.6692 |

7 | 7 | 7 |

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:

**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.

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:

**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.

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 ( dropdown ➜ ➜ ):

Or the main menu (

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

A | B | C | D | |

1 | Original | Formatting | Description | Output |

2 | 0.57721 | #,##0 | Whole number | 1 |

3 | 1.618 | #,##0.0 | 1 decimal place | 1.6 |

4 | 2.71828 | #,##0.00 | 2 decimal places | 2.72 |

5 | 3.141592265 | #,##0.000 | 3 decimal places | 3.142 |

6 | 4.6692 | #,##0.0000 | 4 decimal places | 4.6692 |

7 | 7 | #,##0.00000 | 5 decimal places | 7.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

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:

A | B | C | |

1 | 1 | 1.55 | 1.6 |

2 | 2 | 3.26 | 3.3 |

3 | Total | 4.81 | 4.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

__12 exclusive tips__ to make user-friendly sheets from today:

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