How to Remove Decimals with Rounding in Excel

This guide will show and compare several Excel functions that we can use to remove decimals with rounding.

We will explain the differences between functions such as ROUND with more advanced functions such as MROUND, FLOOR.MATH, and CEILING.MATH.

First, let’s take a look at an example where we must use Excel functions to remove all decimal places in a given numeric value.

Suppose you have a spreadsheet that computes how many orders you should sell to make a decent profit. Because of various factors, the spreadsheet sometimes returns an order count with decimal values. Because the number of orders should always be an integer, you would like to find a formula that removes decimals from the computed number of orders to sell.

There are multiple functions in Excel that can remove decimals and return an integer. First, we can use the ROUND, ROUNDUP, and ROUNDDOWN functions to calculate a rounded value from the original value. The MROUND and INT functions can be used to convert a decimal into a valid integer.

We can also use the CEILING and FLOOR functions to return an integer that is either lower or greater than the original value. In the next section, we will discuss the differences between each of these functions.

Now that we’ve shown when you might need to remove decimal places and the various functions we can use, let’s look at a sample spreadsheet that uses each of these formulas.

A Real Example of Removing Decimals with Rounding in Excel 

Let’s take a look at a real example of the Excel functions being used in a spreadsheet.

In the example below, we have a list of prices for products in an inventory. Suppose we wanted to remove all decimals in the prices to simplify the price and avoid dealing with change lower than a dollar. The table below shows the result of various methods to remove decimal places from a given input price.

First, we’ve used the ROUND function. This function rounds a number to an indicated number of digits. Each number can be rounded up or down depending on the value of the right-most digit included as a decimal place. If we set the second argument of ROUND to zero, we will be removing all decimal places in the result.

To get the values in Column B, we just need to use the following formula:

=ROUND(A2, 0)

If the user wants the results to always be rounded up or rounded down, they can use the ROUNDUP or ROUNDDOWN function, respectively.

The MROUND function is another rounding formula that works a bit differently. MROUND will convert the number to the nearest multiple of a given number. For example, =MROUND(50, 6) will return 48 because it is the value closest to the number 50 that is also a multiple of 6.

If we were to set our second argument to 1, then we can easily remove decimal places in our prices.

To get the values in column E, we will use the following formula:

=MROUND(A2, 1)

Lastly, we can use the FLOOR.MATH and CEILING.MATH function to set the number of decimal places to zero. Note that the floor function will always round down. For example, =FLOOR.MATH(1.999) will return 1. Conversely, the ceiling function will always round up. The expression CEILING.MATH(2.0001) will round up our value to 3.

By default, the FLOOR.MATH and CEILING.MATH functions round the numbers to the nearest multiple of 1. 

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try out these Excel functions yourself, follow the guide in the next section!

How to Remove Decimals with Rounding in Excel 

This section will go through each step needed to remove decimals through rounding in Excel with various formulas. 

First, we’ll show how to use the ROUND function to convert a decimal into an integer. 

1. Select the cell that will hold the result of our ROUND function. In this example, we’ll start with cell B2.
   
2. Next, type the equal sign ‘=‘ to begin the function, followed by ‘ROUND(‘.
   
3. Add the original price as the first argument and place 0 as the number of decimal places. Afterward, hit the Enter key to evaluate the function.
   
4. Fill in the entire column using AutoFill by dragging down the formula we placed in the first cell.
   
5. Instead of ROUND, we can also use ROUNDUP, ROUNDDOWN, MROUND, INT, FLOOR, and CEILING functions to provide the necessary values. You can look at the example below to compare how each of these functions round various decimal inputs.
   

That’s all you need to remember to start using the ROUND function and others in Excel. This step-by-step guide explains how we can remove decimal places in Excel easily. 

Depending on your use case, you may want to either use functions that always round down, such as ROUNDDOWN or FLOOR, functions that always round up like ROUNDUP or CEILING, or functions that round to the nearest integer like MROUND.

The functions shown in this article are just some of the useful mathematical functions available in Excel. With so many other Excel functions, you can surely find some that you can take advantage of in your spreadsheets.

Are you interested in learning more about what Excel can do? Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us. 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe