8.9 min read|Last Updated: December 24th, 2023|Categories: excel|
table of content
There are several reasons you might encounter a number with decimal values in Excel. It might be due to some accounting procedures or any calculations you have done. But now you don’t want to see the decimal values anymore, so you need to know how to round numbers in Excel. There are different methods for doing so, which we intend to explain in this post.
What does rounding numbers in Excel mean?
Rounding numbers in Excel means changing the numbers’ format. You can either hide the decimal part of the number or round it up or down. Round up means rounding the number to an upper level. For example, if your number is 9.55, you can round it up to 10. If you round down this number, the result will be 9. There are three functions you can use to do these calculations on Excel: Round, Roundup, and Rounddown.
Round Off Decimal Values in Excel
One of the methods of rounding numbers in Excel is by changing the numbers’ decimal digits. In this method, the operation will automatically round up or round down a number based on its decimal value. If the value is close to the next number, it will be rounded up, and if it is near the previous amount, it will be rounded down.
The round function in Excel is as follow:
Select the number that has decimal digits.
Go to the Home tab and find the Number group, which has a drop-down menu.
Open the menu and select Number.
Figure 1- Round function in Excel.
The Number format rounds the number with 2 digits by default. But you can still increase or decrease the digits and display your preferred number.
There are two options in the Number group, one to increase the decimal places and the other to decrease the decimal places.Figure 2- Increasing decimal places
Now, if you want to round the number using this method, you need to decrease the decimal places. Therefore, click on the “Decrease decimal places” icon until your number is rounded without any decimal digits.Figure 3- The number is automatically rounded up if the decimal value is more than 5.
As you see in this example, when you select the cell, the original value remains the same (shown in the function bar), but the rounded value is displayed in the cell.
There is another way to round a number in Excel with the same result, which is as simple as follow:
Right-click on the cell that has a value and select “Format Cells” from the opened menu. A dialogue box will be displayed. Select Number from its Category.
Figure 4 – Round numbers in Excel using format cells.
As you can see, the default number has two digits. To round the number, you can simply write the number “0” (zero) in the Decimal places field, or just decrease or increase it using up and down arrows.
Figure 5 – The result of the rounded number in the Sample frame.
Simply click “OK” to see the result in the Excel sheet.
How to round up in Excel?
In the previous method, we couldn’t round up or round down numbers to our liking. Now we can have control over this process by using ROUNDUP and ROUNDDOWN functions. First, we explain how to round up in Excel as follow:
Select a cell where you want to see the result of the Roundup function.
Go to the Formulas tab and click on the Insert Function. A dialogue box will be displayed which has different functions. Select ROUNDUP and click OK.
Figure 6- Roundup your number by selecting the ROUNDUP function.
A dialogue box will open that has two fields, one to select the number and the other to change the number of digits.
Figure 7- Select the number and change the number of digits to round it up.
You can easily write the cell number in the field in front of the Number, or click on the button at the end of the field and select the cell. Then, write 0 in the Num-digits field to remove all its decimal digits and round it up. You can see the result in the same dialogue box (in our example, the rounded-up number is 10). Finally, click OK and see the result in the worksheet.
Figure 8- You can see the ROUNDUP formula in the selected cell and the function bar. The result will be shown in the dialogue box.
If you want to apply the formula to all other cells, simply double-click on the first cell or select and drag.
Figure 9- You can apply the Roundup function to other cells by double-clicking on the first result or just drag it down.
How to round down in Excel?
To round down a number in Excel, you need to follow the same steps, except, this time you need to select the ROUNDDOWN function from the functions list.
Figure 10- Go to Formula tab, click on Insert Function, select ROUNDDOWN function and click OK.Figure 11- You can now see the difference between Round Up and Round Down functions in Excel.
Round Up/ Round Down in Excel by writing the formula
There is another method of rounding up or down value in Excel, which gives you the same result. In this method, we are going to write the formula ourselves.
Select the cell where you want to see the result.
Write an equal sign (=) in the cell. Select your preferred function (round down or round up) from the drop-down menu on the top left corner, next to the formula bar.
Figure 12- You can easily click on the function next to the formula bar and select your preferred function, or select the round up or round down function from the drop-down menu.
Select the function and follow the same steps you took in the previous method.
Figure 13- Select the number and write the number of digits you want it to have like the previous method.
If you are familiar with the formulas, just type the syntax of the function followed by “=” in the formula bar then press Enter. For example, to use the ROUNDUP function type: =ROUNDUP(B5,0).
Figure 14- You can write the preferred rounding function in the formula bar.
Round a Number in Excel via Function Library
The other method of rounding a number in Excel is going to the Formula tab and selecting Math&Trig from the Function Library.
Figure 15- Round a number in Excel using options in the Function Library.
Choosing either of the functions from the list will lead you to the same dialogue box, where you should select the cell and choose its digits.
More Advanced Methods to Round Numbers in Excel
There are other functions to round numbers in Excel that are more advanced than what we explained in this post. They include MROUND, CEILING, FLOOR, INT, ODD, TRUNC, etc. However, the result of these functions is not exactly the same as ROUND, ROUNDUP, and ROUNDDOWN according to their functionality.
Rounding Based on Coefficient in Excel
Note: The Coefficient means that if a number is divisible by a Multiple argument number, the remainder must be zero.
We have seven rounding formulas in excel that round numbers based on a coefficient. Before we mention what these seven are and how to use them, we are going to explain a few arguments.
Multiple: Multiplier we use for rounding.
Significance: The number we want to round the first argument’s value to a multiple of. The default for this argument is 1.
Mode: Round negative numbers to zero or farther from zero whose default number is zero.
a. If the mode is zero (or does not mention), negative numbers are rounded to zero.
b. If the numeric mode is zero, the negative numbers are rounded away from zero.
1.MROUND(number, multiple)
This function is used to round numbers to an arbitrary multiple and round the desired number to the nearestmultiple arguments.
2.CEILING(number, significance)
This function processes the value of the number argument to the first number that is a factor of the significance argument and is greater than the number argument.
3.FLOOR(number, significance)
This function processes the value of the number argument to the first number that is a coefficient of the significance argument and is smaller than the number argument.
4.CEILING.MATH(number, [significance], [model])
This function processes the value of the number argument to the first number that is a factor of the significance argument and is greater than the number argument.
5.FLOOR.MATH(number, [significance], [model])
This function processes the value of the number argument to the first number that is a factor of the significance argument and is smaller than the number argument.
6.FLOOR.PRECISE(number, [significance])
This function processes the value of the number argument to the first number that is a factor of the significance argument and is smaller than the number argument.
7.CEILING.PRECISE(number, [significance])
This function processes the value of the number argument to the first number that is a factor of the significance argument and is greater than the number argument.
Also, we can round numbers based on even numbers, odd numbers, etc. for example:
The EVEN(number) function rounds the number to the first even number greater than itself.
The ODD(number) function rounds the number to the first odd number greater than itself.
The TRUNC(number, [num_digits]) function removes the number decimal.
The INT(number) function Returns the integer component of a number.
Bottom Line
As you see, there are several methods you can follow to round a number in Excel. Whether it’s a Round, Round-Up, or Round-Down function, all the methods will lead to the same result. You can choose the one that you find easier to remember.
Our experts will be glad to help you, If this article didn't answer your questions.
We believe this content can enhance our services. Yet, it's awaiting comprehensive review. Your suggestions for improvement are invaluable. Kindly report any issue or suggestion using the "Report an issue" button below. We value your input.
