When working with numerical data, rounding values to the nearest whole number is something you need to be able to do quickly and efficiently.
Luckily, Excel has several options for rounding numbers to the nearest whole number, making it easy to manage your data effectively.
To round to the nearest whole number in Excel, you can use the ROUND function or the MROUND function. The ROUND function rounds a number to a specified number of digits. For example, =ROUND(A2,0) rounds the number in cell A2 to the nearest whole number. The MROUND function, on the other hand, rounds a number to the nearest multiple. For example, =MROUND(A2,1) rounds the number in cell A2 to the nearest whole number.
This article will walk you through 5 quick ways how to round to the nearest whole number.
Let’s start!
1. Using the ROUND Function in Excel
In Excel, the ROUND function is a powerful tool that allows you to round numbers to a specified number of decimal places or significant digits.
The syntax for the ROUND function is as follows:
number: The number you want to round.
num_digits: The number of digits to which you want to round (use 0 to round up to the nearest integer).
Example 1
Suppose you have a number in cell B5, 3.141592, and you want to round it to the nearest whole number.
To achieve this, you can use the ROUND function as shown below:
=ROUND(B5,0)
The above formula rounds the number 3.141592 to 3.
Example 2
Now, let’s say you have a number in cell B6, 0.666666, and you want to round it to 5 decimal places.
To do this, use the same formula, but modify num_digits argument to 5 instead of 0:
=ROUND(B6,5)
The above formula rounds the number 0.666666 to 0.66667, which is 5 decimal places.
Example 3
Lastly, let’s assume you have several rows of values you want rounded. Simply drag the original cell containing the formula down to auto-fill the formula for the other values!
Using auto-fill, Excel rounds the values in cells B6 to B10 to the nearest whole number.
By mastering the ROUND function, you can easily and accurately round numbers to the nearest whole number or to any desired decimal places.
Now, lets check out the second method, using MROUND.
2. Using the MROUND Function in Excel
Another method to round numbers to the nearest whole number in Excel is by using the MROUND function.
This function allows you to round a number to the nearest specified multiple.
The syntax for the MROUND function is as follows:
number: The number you want to round to the nearest multiple.
multiple: The multiple to which you want to round the number.
Example 1
Let’s assume you have 15.75 in B5, and you want to round it to the nearest whole number.
To do this, you can use the MROUND function as follows:
=MROUND(B5,1)
The above formula rounds the number 15.75 to the nearest whole number (denoted by the multiple 1) and returns 16.
Example 2
Suppose you have a number in cell B6, 7.89, which you want to round to the nearest multiple of 10, and another number in cell B7, 102.55, which you want to round to the nearest multiple of 100.
You can use the following formula to achieve this:
The above formula rounds the number 7.89 to the nearest multiple of 10, which is 10, and the second number, 102.55, to the nearest multiple of 100, which is 100.
By mastering the MROUND function, you can easily round a number upward or downward to the nearest whole number or to any specified multiple.
Now, although not as popular, there are two other methods that you should be aware of.
3. Using ROUNDDOWN and ROUNDUP
When rounding numbers in Excel, there are two other useful functions you can use to round to the nearest whole number: ROUNDDOWN and ROUNDUP.
ROUNDDOWN: This function rounds a number down to the nearest multiple of significance, specified by the user. If the number is already a multiple of the specified significance, no rounding occurs. For example, the formula =ROUNDDOWN(14.8, 0) would return 14.
ROUNDUP: This function rounds a number up to the nearest multiple of significance, specified by the user. If the number is already a multiple of the specified significance, no rounding occurs. For example, the formula =ROUNDUP(14.3, 0) would return 15.
Example 1
Suppose you have numbers in cells B5 and B6, 34.9 and 39.2, and you want to round them down to the nearest whole number.
To do this, you can use the ROUNDDOWN function as follows:
The above formula rounds down the number 34.9 to the nearest whole number, which is 34, and the number 39.2 to 39, which is its nearest number.
Example 2
Suppose you want to round up the numbers in B5 and B6 to the nearest whole number.
You can use the ROUNDUP function as follows:
The above formula rounds up the number 34.9 to the nearest whole number, which is 35, and 39.2 is rounded up to 40.
By mastering the ROUNDDOWN and ROUNDUP functions, you can easily round numbers to the nearest whole number in Excel, making your data more accurate and easier to work with.
5. Rounding Numbers in Excel Without a Function
Rounding numbers to the nearest whole number in Excel can also be achieved without using a specific function.
You can do this by simply formatting the cells to display whole numbers.
This method does not change the actual value in the cell, but it changes the way the number is displayed.
To round a number to the nearest whole number using cell formatting, follow these steps:
Step 1: Select the cell or range of cells containing the numbers you want to round.
Step 2: Right-click on the selected cells and choose “Format Cells” from the context menu.
Step 3: In the Format Cells dialog box, go to the “Number” tab.
Step 4: In the Decimal places box, enter 0. This will display the numbers as whole numbers.
Step 5: Click “OK” to apply the changes.
After following these steps, the numbers in the selected cells will be rounded to the nearest whole number without changing their actual values.
Final Thoughts
Rounding numbers to the nearest whole number in Excel is an essential skill for any skill level or industry you find yourself in.
Whether you use the ROUND function, MROUND function, or even cell formatting, mastering the art of rounding can greatly enhance the accuracy and readability of your spreadsheets.
Remember that precision is key, and with these rounding techniques at your disposal, you can ensure that your Excel work remains polished and professional.
If you’d like to learn more about Microsoft Excel and other data analysis tools, check out our video below:
Frequently Asked Questions
How do I round up to the nearest whole number in Excel?
To round up to the nearest whole number in Excel, you can use the ROUNDUP function.
For example, if you want to round the number in cell A1 to the nearest whole number, you can use the formula =ROUNDUP(A1, 0).
How do I round down to the nearest whole number in Excel?
To round down to the nearest whole number in Excel, you can use the ROUNDDOWN function.
For example, if you want to round the number in cell A1 to the nearest whole number, you can use the formula =ROUNDDOWN(A1, 0).
What is the formula to round a number to the nearest 10 in Excel?
To round a number to the nearest 10 in Excel, you can use the MROUND function.
For example, if you want to round the number in cell A1 to the nearest 10, you can use the formula =MROUND(A1, 10).
How do I round to the nearest 5 in Excel?
To round to the nearest 5 in Excel, you can use the MROUND function.
For example, if you want to round the number in cell A1 to the nearest 5, you can use the formula =MROUND(A1, 5).
