In this article, let’s look at how to use the ROUND function in Excel. We’ll go through step-by-step instructions on how to round numbers and decimals in Excel, as well as how to use the ROUND function in nested formulas.
The Basics of Excel’s ROUND Function
There are three functions to round numbers in Excel: ROUND, ROUNDUP, and ROUNDDOWN. Their difference lies in how they round your numbers:
ROUND uses the general Mathematic rule for rounding. The digits that are less than 5 are rounded down. The digits that are 5 or above are rounded up. ROUNDUP rounds all digits up. ROUNDDOWN rounds all digits down.
In these ROUND functions, they have the same two arguments:
Number refers to the cell or number you want to round. Num_digits lets Excel know the nearest place to which you want to round your number.
How to Insert a ROUND Formula in Excel
There are two ways to insert a rounding formula in Excel. The first method is to select a cell where you want your rounded number to be displayed. Then, key in the formula containing the ROUND function in the Formula Bar.
The other method is to go to the Formulas tab. Select Math & Trig. Scroll down until you find the ROUND, ROUNDUP, or ROUNDDOWN function. This is helpful when you’re new to the ROUND function in Excel and still getting the hang of entering the right num_digit.
The Function Arguments dialog box will show you the rounded number as you key in the arguments. You can easily check that you have the correct number before you confirm and insert the formula into your Excel sheet.
But in this guide, we’ll use the first method, so you’ll have a complete understanding of how to type the formula yourself.
How to Use Excel’s ROUND Function to Round to the Nearest Whole Number
Now, let’s use examples to illustrate the different ways you can utilize Excel’s ROUND function in your spreadsheet.
Round to the Nearest 10 and More With the ROUND Function in Excel
When working out an estimate, you’ll probably need to round your figures to the nearest whole number, as it’s easier to calculate with approximates.
In this first example, let’s look at how to round the stock quantity to the nearest 10:
Select an empty cell to display your rounded number. In this case, it’s C2. In the Formula Bar, key in the formula =ROUND(B2,-1). B2 is the cell containing the stock quantity. To round it to the nearest 10, you need to use -1 for num_digit. Press Enter. Since the digit is 9, the ROUND function rounds 119 up to 120.
To fill in the rest of the cells with the formula, use these tricks to autofill the cells in your Excel spreadsheet. For Excel’s ROUNDUP or ROUNDDOWN function, the steps are the same. Simply replace ROUND with ROUNDUP or ROUNDDOWN in the formula.
Below is a comparison of how the results will differ between the three functions. All numbers are rounded to the nearest 10:
To round to the nearest 100, the num_digit is -2. To the nearest 1000, it’s -3, and so on. Notice that the num_digit corresponds to the number of zeros in the place value. You can use this to remember what num_digit you should use.
Remove Decimals With the Round Function in Excel
Next, let’s look at how you can round to the nearest whole number by getting rid of decimal places:
Like the first example, select an empty cell to display your rounded number. In this case, it’s C9. In the Formula Bar, key in the formula =ROUND(B9,0). B9 is the cell containing the cat’s weight. To round to the nearest whole number and get rid of decimals, use 0 for num_digit. Press Enter. Since the digit is 3, Ginny’s weight is rounded down from 4. 315 to 4.
Again, you can apply ROUNDUP or ROUNDDOWN depending on your needs. Here’s another comparison between the three functions:
How to Use Excel’s ROUND Function to Round Decimals
Rounding to the nearest whole number is not always ideal. Sometimes, more precision is needed—just not too much. Let’s look at how you can use Excel’s ROUND function to round decimals.
Let’s use the weight table again. This time, we want to round each cat’s weight to two decimal places:
Select an empty cell (C16). In the Formula Bar, key in the formula =ROUND(B16,2). B16 is the cell containing the cat’s weight. To round to two decimal places, use 2 for num_digit. Press Enter. Ginny’s weight is rounded up from 4. 315 to 4. 32.
To round to one decimal place, use 1 for the num_digit. To round to three decimal places, use 3, and so on.
How to Use Excel’s ROUND Function in Nested Formulas
In Excel, a nested formula is a formula that contains more than one function. Let’s take a look at how you can use another function within a ROUND formula in Excel.
In this example, let’s sum up the stock quantity we have and round the total to the nearest 100:
Select an empty cell (B6). Key in the formula =ROUND(SUM(B2:B5),-2). The SUM function is used to add numbers in Excel. The num_digit used is -2 to round to the nearest 100. Press Enter. The total of 1010 is rounded to 1000.
Like all the examples we discussed earlier, ROUNDUP and ROUNDDOWN can also be applied for nested formulas.
Let Excel’s ROUND Function Round Your Numbers
Avoid feeling overwhelmed by your spreadsheet’s data by rounding to the nearest whole number in Excel. Or, at least, round off those decimals if absolute accuracy to the last digit isn’t a priority for your data set.
Excel’s ROUND function is very simple to use once you’ve familiarized yourself with it. By using functions and formulas in Excel, you can save time and boost your productivity.
