Rafiul Haq
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”.
Download Practice Workbook
10 Ways to Remove Decimals in Excel with Rounding
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 Add ROUND Formula to Multiple Cells in Excel (2 Easy Ways)
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.
Read More: How to Remove Decimal Places in Excel (5 Easy Methods)
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.
Read More: How to Round a Formula with SUM in Excel (4 Simple Ways)
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.
Read More: How to Fix Decimal Places in Excel (7 Simple Ways)
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.
Read More: Rounding Time in Excel to Nearest Hour (6 Easy Methods)
Similar Readings
- How to Stop Excel from Rounding Large Numbers (3 Easy Methods)
- Rounding to Nearest Dollar in Excel (6 Easy Ways)
- How to Round to Nearest 10 Cents in Excel (4 Suitable Methods)
- Convert Decimal to Days Hours and Minutes in Excel (3 Methods)
- Round Time to Nearest 5 Minutes in Excel (4 Quick Methods)
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: How to Remove Decimals Without Rounding in Excel (4 Suitable Ways)
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: Excel 2 Decimal Places without Rounding (4 Efficient Ways)
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.
Read More: Round off Formula in Excel Invoice (9 Quick Methods)
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 (5 Effective Ways)
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.
Read More: How to Reduce Decimals in Excel (10 Easy Methods)
Practice Section
We’ve added practice datasets for each method in the Excel file.
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!
Related Articles
- How to Insert Dot between Numbers in Excel (3 Ways)
- Rounding Time to Nearest Quarter Hour in Excel (6 Easy Methods)
- How to Convert Hours to Decimal in Excel (3 Easy Methods)
- Converting Time to Decimals in Excel (4 Examples)
- How to Change Decimals to Percentages in Excel (4 Easy Ways)
- Round Excel Data to Make Summations Correct (7 Easy Methods)
- How to Convert Hours and Minutes to Decimal in Excel (2 Cases)
