How to Round Numbers in Excel

Whether you’re new to spreadsheets or a pro, it’s important to get your numbers looking sharp. In this quick guide, we’ll explore easy ways to round numbers in Excel, making sure your data is not only accurate but also looks great. Let’s make those decimals neat and your spreadsheets stand out!

How to Round Numbers in Excel by Changing the Cell Format

One can just change the number of displayed decimal values without changing the underlying value. This is a quick way to display data for presentations. We can use the Increase Decimal or Decrease Decimal command on the home tab in the number group:

Using Increase Decimal and Decrease Decimal feature

Another way is:

Step 1: Select the Cell

Click on the cell that contains the number(s) you want to round.

Selecting the cell

Step 2: Open Format Cells Dialog

Either press Ctrl + 1 or right-click on the cell(s) and choose “Format Cells…” from the context menu to open the Format Cells dialog.

Opening the Format Cells Dialog

Step 3: Choose a Number of Decimal Places

In the Format Cells window, navigate to either the Number or Currency tab.

In the “Decimal places” box, enter the desired number of decimal places you want to display. A preview of the rounded number will appear under “Sample.”

Selecting the number of places that needed to be rounded off

Step 4: Save Changes

Click the OK button to apply the changes and close the Format Cells dialog.

Rounded result

Excel Functions to Round Numbers

Rounding numbers is super useful for clear data. With simple functions, you can round to specific digits or the nearest whole number. This is key for precise financial models and clean spreadsheets. Get ready to make your numbers look sharp and accurate in just a few clicks!

Excel ROUND Function

The ROUND function rounds a number to a specified number of digits.

=ROUND(number, num_digits)

number = number that needs to be rounded

num_digits = number of digits to which the number has to be rounded

Points to note

• If place is 0, then the number is rounded to the nearest integer
• If place is less than 0, then the number is rounded to the left of the decimal point.

Examples of usage of the ROUND function

Excel ROUNDUP Function

The excel ROUNDUP function rounds the number upwards to a specified number of digits. 

=ROUNDUP(number, num_digits)

number = number that needs to be rounded

num_digits = number of digits to which the number has to be rounded

Usage of ROUNDUP Function

Excel ROUNDDOWN Function

The Excel ROUNDDOWN function rounds the number downwards to a specified number of digits.

=ROUNDDOWN(number, num_digits)

number = number that needs to be rounded

num_digits = number of digits to which the number has to be rounded

Usage of ROUNDDOWN Function

Excel MROUND Function

This function rounds a given number up or down a specified multiple.

=MROUND(number, factor)

Number = number to be rounded

Factor = the number whose multiple is to be achieved

For example, the formula rounds 7 to the nearest multiple of 3:

MROUND(7, 3) = 6

Examples of Usage of MROUND Function in Excel

Points to note:

• If the remainder is equal to or greater than half the value of the multiple argument, MROUND rounds the last digit up.
• If the remainder is less than half the value of the multiple argument, the last digit is rounded down.
  

The MROUND function is useful, especially when rounding prices to the nearest nickel (5 cents) or dime (10 cents), simplifying transactions and eliminating the need for dealing with pennies as change.

Excel FLOOR Function

This function is used to round a given number down, to the nearest multiple of specified significance.

=FLOOR(number, significance)

Number = number to be rounded

Significance = the multiple to which the number has to be rounded

For example, FLOOR(5.5, 2) will round the number to 4, which is a multiple of 2.

Examples on the usage of Floor Function in Excel

Points to note:

• If the number is positive and the significance is negative then the function returns a #NUM! error
• If the number is negative and the significance is positive then the number is always rounded down
• If both are negative then the number is rounded up
• If the number happens to be an exact multiple of significance, then it is not rounded.

Excel CEILING Function

This function is used to round a given number up, to the nearest multiple of specified significance.

=CEILING(number, significance)

Number = number to be rounded

Significance = the multiple to which the number has to be rounded

For example, CEILING(5.5, 2) will round the number to 6, which is a multiple of 2.

Examples of the Usage of Ceiling Function in Excel

Points to note

• If the number is positive and the significance is negative then the function returns a #NUM! error
• If the number is negative and the significance is positive then the number is rounded up
• If both are negative then the number is rounded down

Excel INT Function

This function rounds the number down to the nearest integer. It requires only one argument. Positive numbers are rounded closer to 0 while negative numbers are rounded away from 0. For example, =INT(3.5) returns 3 and =INT(-3.5) returns -4.

=INT(number)

number = number that needs to be rounded

Usage of INT Function in Excel

Excel TRUNC Function

This function can be used to remove the fractional part without changing the integer part. 

TRUNC(number, place_value)

Number – number that needs to be truncated

Place_value – defines precision of truncation i.e. number of decimal places to truncate a number to

Examples of Usage of TRUNC Function in Excel

Points to note

• If the number is positive, it is truncated to the place_value number of digits to the right of the decimal point.
• If the number is negative, it is truncated to the place_value number of digits to the left of the decimal point.
• If the second argument is 0 or omitted, the function rounds the number to an integer. Even though this is similar to what the INT function does, it is not as the TRUNC function simply removes the fractional part and INT will round the number to an integer.

Excel ODD and EVEN Function

As the name suggests, the ODD function rounds the number to the nearest odd integer, and EVEN rounds the number to the nearest even integer.

Syntax:

EVEN(number)

ODD(number)

Number = number that needs to be rounded

Examples of Usage of ODD and Even Function in Excel

Points to note:

• The number that needs to be rounded needs to be a real number
• The function returns a #VALUE! Error, if the number is non-numeric
• If the number is negative it is rounded away from 0.

How to Use Rounding Formulas in Excel

How to Round Decimals in Excel to a Certain Number of Places

ROUND, ROUNDUP, and ROUNDDOWN can be used to round decimals to a certain number of places depending on the situation. Let us perform the rounding of decimals to 3 places using all these functions. In the first argument (number), we can either directly enter the number or enter a reference to the cell containing the number. In the second argument(places), you can specify the number of decimal places to round the number to.

=ROUND(A15, 3) – will round the number present in A15 to 3 decimal places, upward or downward, depending on the 4th decimal digit.

=ROUNDUP(A15, 3) – will round the number in A15 upward, to 3 decimal places

=ROUNDDOWN(A15, 3) – will round the number in A15 downward, to 3 decimal places

Rounding numbers to 3 digits of places

Rounding negative numbers (ROUND, ROUNDDOWN, ROUNDUP)

When ROUND is applied to negative numbers it will round the numbers in the same way it rounds positive numbers. But for ROUNDUP and ROUNDDOWN, the results are not something one would expect.

When ROUNDUP is applied to negative numbers, they are rounded up i.e. away from zero, even though it decreases the value. When ROUNDDOWN is applied to negative numbers, their value may increase i.e. they move towards zero. 

Rounding negative numbers using ROUND, ROUNDUP and ROUNDDOWN

How to Extract a Decimal Part of a Number

To extract the decimal part of a number, one can use the TRUNC function with the help of the following formula:

=A15 – TRUNC(A15, 0)

The result will be returned along with the respective sign of the number. If one would want to get the absolute value then the ABS function can be used:

=ABS(A15 – TRUNC(A15, 0))

Removing decimal parts of the numbers using the TRUNC Function

How to Round a Decimal to an Integer in Excel

There are various functions that can be used to round a number to a certain number of decimal places:

ROUNDUP

The ROUNDUP function can be used to round up to the nearest integer by having the second argument in the function as 0.

=ROUNDUP(6.3, 0) rounds up to 7

INT or RUNDOWN

INT or ROUNDDOWN with places = 0 can be used to round down to the nearest whole number (because numbers can be rounded to 0 as well). In the case of negative integers though, instead of rounding towards 0 INT rounds numbers away from 0.

=ROUNDDOWN(6.3, 0) and =INT(6.3) returns 5

=ROUNDDOWN(-6.3, 0) returns -6

=INT(-6.3) returns -7

TRUNC

To just remove the decimal part without altering the integer part TRUNC function can be used with the second argument place_value set to 0.

=TRUNC(6.3) returns 6 by removing 0.3

ODD or EVEN

ODD and EVEN functions can be used to round up the decimal up to the nearest odd or even integer respectively.

=ODD(6.3) returns 7

=EVEN(6.3) returns 8

Usage of various functions to round a number to an integer

Round to the nearest 0.5

The FLOOR, CEILING, and MROUND functions can be employed to round numbers to a multiple based on the factors provided.

=FLOOR(A15, 0.5) to round a number down to nearest 0.5

=CEILING(A15, 0.5) to round a number up to nearest 0.5

=MROUND(A15, 0.5) to round number up or down to nearest 0.5

how MROUND rounds a number has been discussed in the previous sections.

Usage of FLOOR, CEILING, and MROUND functions to round to a multiple of 0.5

Round to nearest 5 / 10 / 100 / 1000

Rounding to the nearest 5, 10, 100, and 1000 can be achieved in the same way as discussed for 0.5.

Round to the nearest 5

=FLOOR(A15, 5) rounds a number down to the nearest multiple of 5

=CEILING(A15, 5) rounds a number up to the nearest multiple of 5

=MROUND(A15, 5) rounds a number up or down to the nearest multiple of 5

Usage of FLOOR, CEILING, and MROUND functions to round to multiple of 5

Round to the nearest 10

=FLOOR(A15, 10) rounds a number down to the nearest multiple of 10

=CEILING(A15, 10) rounds a number up to the nearest multiple of 10

=MROUND(A15, 10) rounds a number up or down to the nearest multiple of 10

Usage of FLOOR, CEILING, and MROUND functions to round to a multiple of 10

Round to the nearest 100

=FLOOR(A15, 100) rounds a number down to the nearest multiple of 100

=CEILING(A15, 100) rounds a number up to the nearest multiple of 100

=MROUND(A15, 100) rounds a number up or down to the nearest multiple of 100

Usage of FLOOR, CEILING, and MROUND functions to round to a multiple of 100

Round to the nearest 1000

=FLOOR(A15, 1000) rounds a number down to the nearest multiple of 1000

=CEILING(A15, 1000) rounds a number up to the nearest multiple of 1000

=MROUND(A15, 1000) rounds a number up or down to the nearest multiple of 1000

Usage of FLOOR, CEILING, and MROUND functions to round to multiple of 1000

Rounding Time in Excel

There are various functions and methods that can be used to round time values for the appropriate computations:

Example 1: How to round time to the nearest hour in Excel

MROUND or ROUND can be used to round time to the closest hour (up or down)

=MROUND(A15, “1:00”)

=MROUND(A15, TIME(1, 0, 0))

=ROUND (A15*24, 0) / 24

ROUNDUP or CEILING can be used to round up the time to the nearest hour.

=CEILING(A15, “1:00”)

=CEILING(A15, TIME(1, 0, 0))

=ROUNDUP(A15*24, 0)/24

ROUNDDOWN or FLOOR can be used to round down the time to the nearest hour.

=FLOOR(A15, “1:00”)

=FLOOR(A15, TIME(1, 0, 0))

=ROUNDDOWN(A15*25, 0)/24

For the ROUND, ROUNDUP, and ROUNDDOWN formulas, the time value is multiplied by 24 which is the number of hours in a day to convert a serial number representing the time to hours. Then after rounding the decimal value to an integer with the help of a function, it is divided by 24 to change the returned value back to time format. 

Rounding to the closest hour

Example 2: Rounding time to the nearest 5, 10, 15, etc. minutes

The same rounding techniques can be used in order to time to five or ten minutes, or to the closest quarter-hour. The “1:00” can be replaced with the desired number of minutes in the formulas.

For example, to round a number in A15 to 5 minutes the formulae that can be used are:

=MROUND(A15, TIME(0,5,0)) to round time to closest 10 minutes

=CEILING(A15, TIME(0,5,0)) to round up time to nearest 10 minutes

=FLOOR(A15, TIME(0,5,0)) to round down time to nearest 10 minutes

Rounding to the closest 10 minutes

To use the ROUND, ROUNDUP, and ROUNDDOWN functions we just need to know the fraction of the day being talked about. For example, 15 minutes represents 1/96th of a day. The formulae that can be used in that case are:

=ROUND(A15*96, 0) / 96, to round to closest 15 minutes

=ROUNDUP(A15*96, 0) / 96, to round up time to nearest 15 minutes

=ROUNDDOWN(A15*96, 0) / 96, to round down time to nearest 15 minutes

FAQs

1. How do I round to 2 decimal places in Excel?

The Excel functions ROUND, ROUNDUP, ROUNDDOWN can be used to round a number to 2 decimal places by providing 2 as the input to the second argument.

2. How do I get Excel to round correctly?

To round numbers correctly in Excel, use the ROUND function: `=ROUND(number, places)`. Specify the desired precision by adjusting the `places` parameter.

3. How do I round off numbers in Excel without a formula?

Follow the following steps to round the number without using a formula: Select the cells containing the numbers to be rounded, Click on the “Home” tab, in the “Number” group, locate the “Increase Decimal”/”Decrease Decimal” icon. Click the icons to increase/reduce decimal places by one.

4. How do you round up numbers?

Rounding numbers involves preserving significant digits. If the smallest digit is less than 5, it remains unchanged; if 5 or more, the digit increases by 1. For negative numbers, the absolute value is rounded, and the negative sign is reintroduced afterward.