How to Remove Decimals in Excel with Rounding (10 Easy Methods)

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”.

Intro: how to remove decimals in excel with rounding


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.

Button to Remove Decimals with RoundingRead 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.

Use of ROUND Function to Remove Decimals in Excel

  • Secondly, press ENTER.

The nearest whole number to 23.99 is 24. Therefore, we’ve got 24 as our result.

Use of ROUND Function to Remove Decimals in Excel

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.

Utilizing ROUNDUP Function to Remove Decimals in Excel with Rounding

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.

Remove Decimals Using ROUNDDOWN Function with Rounding in Excel

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.

Employing MROUND Function to Remove Decimals in Excel with Rounding

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


Similar Readings


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.

Use of EVEN Function to Remove Decimals in Excel with Rounding

  • 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.

Remove Decimals in Excel with Rounding by Using ODD Function

  • 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.

Employing INT Function to Remove Decimals in Excel with Rounding

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.

CEILING Functions to Remove Decimals in Excel with Rounding

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.

Remove Decimals in Excel with FLOOR Function with Rounding in Excel

  • 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.

Remove Decimals in Excel with Rounding


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

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo