How to Round to the Nearest Whole Number in Excel: 5 Quick Ways

by | Excel

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

how to round to the nearest whole number 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)
Rounding up to the nearest whole number using excel round functions

The above formula rounds the number 3.141592 to 3.

The decimal point is removed and 3 is returned

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)
Using rounding functions to 5 decimal values

The above formula rounds the number 0.666666 to 0.66667, which is 5 decimal places.

Specifying num_digits rounding digit as 5 gives you a result with 5 decimal place

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!

Dragging the cell down auto-fills the formula and the number rounds

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)
MROUND is like a ceiling function that accepts multiples

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:

Using the MROUND excel functions on number argument with 10 and 100 multiples

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.

With multiples of 10 and 100, MROUND removes the decimal value and gives a whole rounded number

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 ROUNDDOWN function performs a rounding operation downward

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.

The resulting number is rounded down from 34.9 to 34 and 39.2 to 39

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 ROUNDUP function rounds a number upwards and gives the absolute value

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.

MROUND rounds up to the least significant digits

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.

Pick the Format Cells option from the 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.

How to round to the nearest whole number in excel without using rounding formula

Step 5: Click “OK” to apply the changes.

The values are rounded to the chosen number of decimal places

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

Related Posts