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

Get FREE Advanced Excel Exercises with Solutions!

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


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.

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

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


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


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


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


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

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

Employing INT Function to Remove Decimals in Excel with Rounding


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


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.


Practice Section

We’ve added practice datasets for each method in the Excel file.

Remove Decimals in Excel with Rounding


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!


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel projects.
Rafiul Haq
Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo