We’re going to show you 10 methods of how to remove decimals in Excel with rounding. To demonstrate our methods, we’ve picked a dataset with 3 columns: “Company”, “Product”, and “Price”.
How to Remove Decimals in Excel with Rounding: 10 Ways
1. Button to Remove Decimals with Rounding
For the first method, we’re going to use the Decrease Decimal button from the Number group to remove decimals. Remember, this is only a cosmetic change, if you use this value to calculate other things, then avoid using this method.
Steps:
- Firstly, copy and paste the values from cell C5:D10 to E5:E10 range.
- Secondly, from the Home tab >>> from Number group >>> select the Decrease Decimal button.
This will round up or round down the values depending on the values. For example, here 23.99 is closer to 24, hence it will round up, and so on.
Read More: How to Remove Decimals Without Rounding in Excel
2. Use of ROUND Function to Remove Decimals in Excel
For the second method, we’re going to use the ROUND function to remove decimals in Excel. Unlike the first method, from now on all methods will modify the number. Therefore, we can use these for further calculation purposes.
Steps:
- Firstly, type the following formula in cell E5.
=ROUND(D5,0)We’re instructing to round the number from cell D5 to the nearest whole number with that 0 in our formula.
- Secondly, press ENTER.
The nearest whole number to 23.99 is 24. Therefore, we’ve got 24 as our result.
- Finally, use the Fill Handle to AutoFill the formula.
3. Utilizing ROUNDUP Function
In this section, we’re going to use the ROUNDUP function to remove Decimals.
Steps:
- Firstly select cell range E5:E10 and type this formula.
=ROUNDUP(D5,0)We’re using 0 here to round up the value from cell D5 to the nearest whole number.
- Then, press CTRL + ENTER.
This will AutoFill the formula. We can see that all numbers are rounded up to the nearest whole number.
4. Remove Decimals Using ROUNDDOWN Function
For this method, we’re going to use the ROUNDDOWN function to remove decimals.
Steps:
- Firstly select cell range E5:E10 and type this formula.
=ROUNDDOWN(D5,0)We’re using 0 here to round down the value from cell D5 to the nearest whole number.
- Then, press CTRL + ENTER.
Thus, we can see that all numbers are rounded down to the nearest whole number.
5. Employing MROUND Function to Remove Decimals in Excel
In this section, we’ll use the MROUND function to remove decimals with rounding.
Steps:
- Firstly select cell range E5:E10 and type this formula.
=MROUND(D5,2)This function returns the rounded number from a defined multiple. We’ve defined our multiple as 2. Therefore, we’ll round our values for the multiple of 2. Hence, we’ll get 24 for the value of 23.99 (as 12*2=24).
- Then, press CTRL + ENTER.
Thus, we can see that all numbers are rounded up or down to the nearest whole number.
6. Use of EVEN Function to Remove Decimals in Excel with Rounding
For this method, we’re gonna use the EVEN function to remove decimals.
Steps:
- Firstly, type the following formula in cell E5.
=EVEN(D5)This function returns the next even number. The next number for the value from cell D5 is 24. Thus, we’ll get 24.
- Secondly, press ENTER.
We’ve got 24 as described above.
- Finally, use the Fill Handle to AutoFill the formula.
Read More: Rounding to Nearest Dollar in Excel
7. Remove Decimals in Excel with Rounding by Using ODD Function
In this section, we’ll use the ODD function in Excel to remove decimals.
Steps:
- Firstly select cell range E5:E10 and type this formula.
=ODD(D5)This function returns the next odd number. The next number for the value from cell D5 is 25. Thus, we’ll get 25.
- Then, press CTRL + ENTER.
Thus, we can see that all numbers are rounded up to the next odd number.
Read More: How to Get 2 Decimal Places Without Rounding in Excel
8. Employing INT Function to Remove Decimals in Excel with Rounding
We’ll use the INT function to remove decimals in this section.
Steps:
- Firstly select cell range E5:E10 and type this formula.
=INT(D5)This function returns a rounded-down number without the decimal part. Therefore, we’ll get 23 as output for the first cell.
- Then, press CTRL + ENTER.
Thus, we’ve rounded down all numbers by removing all the decimals.
9. CEILING Functions to Remove Decimals
In this method, we’re going to use the CEILING function and its variant CEILING.MATH to remove decimals from a number in Excel.
9.1. Use of CEILING Function
For this section, we’ll use the CEILING function to remove decimals in Excel with rounding.
Steps:
- Firstly select cell range E5:E10 and type this formula.
=CEILING(D5,2) This function works similarly to the MROUND function. However, this only rounds up to the defined multiple, which is 2 here. We’ll get 24 as the output for cell E5.
- Then, press CTRL + ENTER.
Thus, we can see that all numbers are rounded up by removing all the decimals.
9.2. Using CEILING.MATH Function
Microsoft has listed the CEILING function as a “compatibility function”. The newer CEILING.MATH function will replace the earlier function. To use this function –
- Type this formula in cell E5.
=CEILING.MATH(D5)In this case, the function’s default multiple is set to 1. Hence we’ll round up the number to the next whole number.
This is what the function looks like.
Read More: How to Set Decimal Places in Excel with Formula
10. Remove Decimals in Excel with FLOOR Functions
For the last method, we’ll use the FLOOR function and its variant FLOOR.MATH to remove decimals from a number.
10.1. Using FLOOR Function
In this section, we’re going to use the FLOOR function to remove decimals.
Steps:
- Firstly select cell range E5:E10 and type this formula.
=FLOOR(D5,2)This function works similarly to the MROUND function. However, this only rounds down to the defined multiple, which is 2 here. We’ll get 22 as the output for cell E5.
- Then, press CTRL + ENTER.
Thus, we can see that all numbers are rounded down by removing all the decimals.
10.2. Using FLOOR.MATH Function
Microsoft has listed the FLOOR function as a “compatibility function”. The newer FLOOR.MATH function will replace the earlier function. To use this function –
- Type this formula in cell E5.
=FLOOR.MATH(D5)In this case, the function’s default multiple is set to 1. Hence we’ll round down the number to the previous whole number.
In conclusion, this is what the function looks like.
Practice Section
We’ve added practice datasets for each method in the Excel file.
Download Practice Workbook
Conclusion
We’ve shown you 10 methods of how to remove decimals in Excel with rounding. Moreover, if you face any problems, feel free to comment below. Finally, thanks for reading, keep excelling!
