Round to Nearest Whole Number in Excel (9 Easy Methods)

We use numbers in Excel in different cases. In those cases, we may get numbers in long decimals. But in most real cases, those long decimals are not that significant. We often curtail those numbers to make those numbers round to the whole number. To become more accessible and easier to understand. In this article, we will discuss how you can round decimals to the nearest whole number in Excel.


Download Practice Workbook

Download this practice workbook below.


9 Suitable Methods to Round a Number to Nearest Whole Number in Excel

We are going to use the below dataset for the demonstration reason. On the left side is the numbers that are not rounded, and on the right column, the Rounded number will have the number rounded as shown. How we manage to do this will discuss here with adequate examples.

Round to nearest whole number in Excel


1. Use of ROUND Function

The ROUND function is an effective function to round numbers down to the nearest integer.

In this function, we need to enter the number of digits to which our number argument will round. The number will round to the nearest integer if the value is 0.

Steps

  • At first, select cell D5 and enter the following code.
=ROUND(B5,0)
  • After entering the code, you will notice that cell D5 now contains the rounded number in cell B5. The number now rounds up from 973.5 to 974.

Use of ROUND Function to r

  • Now, drag the Fill Handle button in the corner of cell D5 to cell D12.
  • After that, you will notice that the range of cells D5 to D10 is now filled with the rounded-up numbers from the range of cells B5:B11.

Use of ROUND Function to round to nearest whole number

Read More: How to Roundup a Formula Result in Excel (4 Easy Methods)


2. Applying EVEN and ODD Functions

Here the ODD and EVEN functions will round the number to the closest even or odd integer number depending on the value of the original number.

Steps

  • At first, select cell D5 and enter the following code.
=EVEN(B5)
  • After entering the code, you will notice that cell D5 now contains the rounded number in cell B5. The number is rounded up from 973.5 to 974.

Applying EVEN and ODD Functions to round to nearest whole number

  • Now select cell D6 and enter the following formula.
=ODD(B6)

We enter the odd function instead of the EVEN Function because of rounding the number to its nearest whole number. The number in cell D5 is close to an even number in the upward direction, more than an odd number. This is why we choose the Even function to make it 974.

  • Instead, the ODD function will round the number to 975, which is not the nearest integer of 973.5 in the upward direction.
  • On the other hand, function ODD used to make 102.5 rounded to 103 as 102.5 is closer to the odd number 103 in the upward direction.
  • Now enter the appropriate function in the range of cells D5:D12. If the number is closer to the odd number in the upward direction, use the ODD function. Otherwise, use the EVEN function.

Applying EVEN and ODD Functions to round to nearest whole number

Read More: Round Time to Nearest 5 Minutes in Excel (4 Quick Methods)


3. Utilizing TRUNC Function

In this method, the TRUNC function will curtail the decimal part of the fraction and round that number. In the argument value, We set [num_digits]=0 to convert the number to an integer. Stripping only the fractional part. There will be no rounding up or down in this case.it works similarly to the INT function. But the TRUNC function just removes the fractional part; it won’t round up or down any number.

Steps

  • At first, select cell D5 and enter the following code.
=TRUNC(B5,0)
  • After entering the code, you will notice that cell D5 now contains the rounded number in cell B5. The number is now truncated from 973.5 to 973.

Utilizing TRUNC Function to round to nearest whole number

  • Now, drag the Fill Handle button in the corner of cell D5 to cell D12.
  • After that, you will notice that the range of cells D5 to D10 is now fille with the rounded numbers from the range of cells B5:B11. All of them are stripped of their fractional part.

Utilizing TRUNC Function to round to nearest whole number

Read More: How to Round Excel Data to Make Summations Correct (7 Easy Methods)


4. Using INT Function

We will use the INT function to convert fractional values to Integer values. The INT function essentially makes the number stripped away from the decimal part and rounds down, permanently.

Steps

  • At first, select cell D5 and enter the following code.
=INT(B5)
  • After entering the code, you will notice that cell D5 now contains the rounded number in cell B5. The number is rounded from 973.5 to 973.
  • In this case, the number is rounded down, and in all of the cases, INT Function will round down the number to its nearest integer.

Using INT Function to round to nearest whole number

  • Now, drag the Fill Handle button in the corner of cell D5 to cell D12.
  • After that, you will notice that the range of cells D5 to D10 is now fille with the rounded down numbers from the range of cells B5:B11 to their closest integer number.

Using INT Function to round to nearest whole number

Read More: Round off Formula in Excel Invoice (9 Quick Methods)


5. Applying MROUND Function

With the MROUND function, we can round values to the multipliers. Hence this method provides better freedom compared to the other methods. The second argument of this function is multiple, to whose multiple we are going to round up the original number.

Steps

  • At first, select cell D5 and enter the following code.
=MROUND(B5,1)
  • After entering the code, you will notice that cell D5 now contains the rounded number in cell B5. The number is now rounded from 973.5 to 973.
  • The Multiple here is chosen as 1, so the parent number will be rounded to a multiple of 1 in every case. The numbers will round closer to the nearest integer value in every case.

Applying MROUND Function to round to nearest whole number

  • Now, drag the Fill Handle icon in the corner of cell D5 to cell D12.
  • After that, you will notice that the range of cells D5 to D10 is now filled with the rounded to the closest integer numbers from the range of cells B5:B11 to their closest integer number.

Applying MROUND Function to round to nearest whole number

Note:

The Number argument and the Multiple arguments must have the same sign; otherwise, there would be a #NUM error.

Read More: Rounding Time in Excel to Nearest Hour (6 Easy Methods)


6. Utilizing FLOOR Function

Using the FLOOR function, we can round down the number according to the multiplier set in the function.

Steps

  • At first, select cell D5 and enter the following code.
=FLOOR(B5,1)
  • After entering the code, you will notice that cell D5 now contains the rounded-down number in cell B5. The number is rounded from 973.5 to 973.
  • The Multiple here is chosen as 1, so the parent number will be rounded down to a multiple of 1 in every case. The numbers will be rounded closer to the nearest integer value in every case.

Utilizing FLOOR Function to round to nearest whole number

  • Now, drag the Fill Handle button in the corner of cell D5 to cell D12.
  • After that, you will notice that the range of cells D5 to D10 is now filled with the rounded down integer numbers from the range of cells B5:B11 to their nearest integer number.

Utilizing FLOOR Function to round to nearest whole number

Note:

1. If any of the arguments in the FLOOR function is not numeric, then the FLOOR function will return the #VALUE! error.

2. If the number value is negative, the rounding will happen towards 0. If the number is positive, then the number will round away from 0.

3. Caution must be taken while choosing the significance value. It should always be set to 1 if the intention is to make a number round to an integer. If the Significance number has fractions or

decimal parts, the rounding will not result in an integer number.

4. You can use the FLOOR.MATH function to automatically round the number down to an integer. The significance value is one by default.

Read More: Rounding Time to Nearest Quarter Hour in Excel (6 Easy Methods)


7. Using CEILING Function

The CEILING function works just like the FLOOR function. But in this case, the CEILING  function will round up the number instead of round down. And the round-up will be done according to the multiplier set in the function.

Steps

  • At first, select cell D5 and enter the following code.
=CEILING(B5,1)
  • After entering the code, you will notice that cell D5 now contains the rounded-down number in cell B5. The number is rounded from 973.5 to 973.
  • The significance here is chosen as 1, so the parent number will be rounded down to a multiple of 1 in every case. The numbers will be rounded closer to the nearest integer value in every case.

Using CEILING Function to round to nearest whole number

  • After that, you will notice that the range of cells D5 to D10 is now filled with the round-down integer numbers from the range of cells B5:B11 to their nearest whole number in Excel.

Using CEILING Function to round to nearest whole number

Note:

You can use the CEILING.MATH function to round the numbers directly; significance=1 is by default in this function.

Read More: How to Round to Nearest 10 Cents in Excel (4 Suitable Methods)


8. Applying Decrease Decimal Command

We can decide the no of decimal of the number to show in the worksheet directly in the ribbon menu. We can even make numbers with decimals reduced to integers, stripping the fractional part from the original number.

Steps

  • In the beginning. Copy the data from the range of cells B5:B12 to the range of cells C5:C12.
  • Then select the cells and from the ribbon menu, click twice on the Decrease Decimal command from the Number group in the Home tab.

  • Then you will notice the numbers in the range of cells C5:C12 is now free from the fractional part.
  • Here, you need to press the command two times in this case because some numbers carry two decimal places.
  • After clicking the Decrease Decimal, you will notice that the numbers in the range of cells C5:C12 are now free from fractions. And they round up to their nearest whole number in Excel.

Read More: How to Round Percentages in Excel (4 Simple Methods)


9. Utilizing Built-in Number Format

Using the built-in number option, we can change the decimal value of the number and can truncate the value from the fractional part.

Steps

  • In the beginning, copy the cells from the range of cells B5:B12 and paste them to the range of cells C5:C12.

Applying EVEN and ODD Functions

  • Then select the range of cells D5:D12, right-click on the mouse and click on the Format Cells.

  • After that, a new window will open, and from that window, click on the Number from the Number tab.
  • In the Number tab, set the decimal place to 0, as shown in the image.
  • Click OK after this.

  • After clicking OK, you will notice that the numbers in the range of cells are now rounded up to the nearest whole number.

By this method, you can round a decimal to the nearest whole number in Excel.

Read More: Rounding to Nearest Dollar in Excel (6 Easy Ways)


Conclusion

To sum it up, the question “how to round decimals to the nearest whole number” is answered here elaborately in nine separate ways. Using functions like ROUND, TRUNC, MROUND, EVEN and ODD, etc. Additionally, rounded by using the ribbon menu and the number formatting option.

A workbook is available for download to practice these examples for this problem.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo