Precision is important, but so is clarity. Whether you’re preparing a financial report, managing inventory, or analyzing sales data, knowing how to round numbers in Excel can streamline your spreadsheets and make your data easier to understand. Excel offers several functions tailored for different rounding needs, from rounding up to the nearest hundred to trimming a number to a specific number of decimal places.
This article will walk you through the most useful Excel rounding functions with simple, step-by-step instructions. Perfect for beginners and seasoned users alike, you’ll learn how to utilize these tools to make your data presentation as polished as possible. Let’s look into the essentials of rounding numbers in Excel.
How to Round Numbers in Excel?
How to Round Numbers in Excel
Here we have provided a list of ways to round numbers in Excel:
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!
1. 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
2. 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
3. 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
4. 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.
5. 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.
6. 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
7. 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
8. 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.
9. 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
1. 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
2. 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
3. Extracting 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
4. Rounding 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
5. Rounding 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
6. Rounding to the 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
7. 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
How to Round Numbers in Excel – FAQs
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.
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.
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.
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.