Here’s a simple dataset that contains numbers in column B, which we’ll round in column C or D.
Method 1 – Using the ROUND Function to Round to the Nearest Whole Number in Excel
Steps
- Select cell D5 and enter the following formula.
=ROUND(B5,0)
- Drag the Fill Handle button in the corner of cell D5 to cell D12.
Method 2 – Applying EVEN and ODD Functions for Rounding to the Nearest Whole Number
Steps
- Select cell D5 and enter the following.
=EVEN(B5)
- Select cell D6 and enter the following formula.
=ODD(B6)- Enter the EVEN or ODD function for the remaining cells depending on whether the whole portion is even or odd.
Read More: How to Round Down to Nearest Whole Number in Excel
Method 3 – Inserting the TRUNC Function to Round in Excel
TRUNC cuts off the decimal portion of a number.
Steps
- Select cell D5 and enter the following formula.
=TRUNC(B5,0)
- Drag the Fill Handle button in the corner of cell D5 to cell D12.
Read More: Round to Nearest 5 or 9 in Excel
Method 4 – Rounding to the Nearest Whole Number Using the INT Function
Steps
- Select cell D5 and enter the following formula:
=INT(B5)- The INT Function will round down the number to its nearest integer.
- Drag the Fill Handle button in the corner of cell D5 to cell D12.
Read More: How to Round Numbers to the Nearest Multiple of 5 in Excel
Method 5 – Applying the MROUND Function for Rounding in Excel
With the MROUND function, we can round values to the multipliers.
Steps
- Select cell D5 and enter the following:
=MROUND(B5,1)- The Multiple here is chosen as 1, so the number will be rounded to a multiple of 1. The numbers will round closer to the nearest integer value in every case.
- Drag the Fill Handle icon in the corner of cell D5 to cell D12.
Note:
The Number argument and the Multiple argument must have the same sign.
Method 6 – Inserting the FLOOR Function to Round to the Nearest Whole Number
Steps
- Select cell D5 and enter the following.
=FLOOR(B5,1)- The Multiple here is chosen as 1, so the 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.
- Drag the Fill Handle button in the corner of cell D5 to cell D12.
Note:
- If any of the arguments in the FLOOR function are not numeric, the FLOOR function will return the #VALUE! error.
- If the number value is negative, the formula rounds up. If the number is positive, the formula rounds down.
- If the Significance number has fractions or decimal parts, the rounding will not result in an integer number.
- You can use the FLOOR.MATH function to automatically round the number down to an integer.
Read More: Round Down to Nearest 10 in Excel
Method 7 – Using the CEILING Function to Round to the Nearest Whole Number in Excel
The CEILING function will round up the numbers.
Steps
- Select cell D5 and enter the following:
=CEILING(B5,1)- The significance here is chosen as 1, so the original number will be rounded down to a multiple of 1 in every case.
- AutoFill the column.
Note:
You can also use the CEILING.MATH function to round the numbers directly.
Read More: How to Round to Nearest 100 in Excel
Method 8 – Applying the Decrease Decimal Command to Round in Excel
Steps
- Copy the data from the range of cells B5:B12 to the range of cells C5:C12.
- Select the new cells.
- From the ribbon menu, click twice on the Decrease Decimal command from the Number group in the Home tab.
- The numbers in the range of cells C5:C12 are now free from the fractional part.
- Use the command again if needed.
Read More: How to Round Numbers to Nearest 10000 in Excel
Method 9 – Rounding to the Nearest Whole Number Through the Built-in Number Format
Steps
- Copy the cells from the range of cells B5:B12 and paste them to the range of cells C5:C12.
- Select the range of cells D5:D12, right-click, and select Format Cells.
- A new window will open. Click on Number from the Category section.
- Set the decimal places to 0.
- Click OK.
- Here are the results.
Read More: How to Round to Nearest 1000 in Excel
Download the Practice Workbook
<< Go Back to Rounding in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
