Share on facebook
Share on twitter
Share on pinterest

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:

  1. Select the number that has decimal digits.
  2. Go to the Home tab and find the Number group, which has a drop-down menu.
  3. Open the menu and select Number.
    Round function in Excel.
    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. 
  4. There are two options in the Number group, one to increase the decimal places and the other to decrease the decimal places.How to round numbers in Excel by Increasing decimal places
    Figure 2- Increasing decimal places
  5. 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.The number is automatically rounded up if the decimal value is more than 5.
    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:

  1. 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.
    Round numbers in Excel using format cells option
    Figure 4 – Round numbers in Excel using format cells.
  2. 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.
    You can see the result of the rounded number in the Sample frame.
    Figure 5 – The result of the rounded number in the Sample frame.
  3. 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:

  1. Select a cell where you want to see the result of the Roundup function. 
  2. 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. 
    You can roundup your number by selecting ROUNDUP function.
    Figure 6- Roundup your number by selecting the ROUNDUP function.
  3. A dialogue box will open that has two fields, one to select the number and the other to change the number of digits.
    Select the number and change the number of digits to round it up
    Figure 7- Select the number and change the number of digits to round it up.
  4. 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.
    You can see the ROUNDUP formula in the selected cell and the function bar. The result will be shown in the dialogue box.
    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.

You can apply the Roundup function to other cells by double-clicking on the first result or just drag it down.
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.

Go to Formula tab, click on Insert Function, select ROUNDDOWN function and click OK.
Figure 10- Go to Formula tab, click on Insert Function, select ROUNDDOWN function and click OK.
See the difference between Round Up and Round Down functions in Excel.
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. 

  1. Select the cell where you want to see the result. 
  2. 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.
    You can easily click on the function next to the formula bar and select your preferred function.
    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.
  3. Select the function and follow the same steps you took in the previous method. 
    Select the number and change the number of digits to round it up.
    Figure 13- Select the number and write the number of digits you want it to have like the previous method.
  4. 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).
    You can write the preferred rounding function in the formula bar.
    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. 

Round a number in Excel using options in 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. 

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.

Subscribe to our Newsletter

Share this post with your friends

Share on facebook
Share on google
Share on twitter
Share on linkedin

Leave a Reply

Your email address will not be published. Required fields are marked *