Zahid Hasan
While working in Excel, we often need to round off decimals in our worksheets. Dealing with mathematical calculations in Excel can lead to getting a number with a lot of decimal places. But to present data in a clean and compact way, we need to round off the decimal places of the number. In this article, we will learn 10 convenient methods to round off decimals in Excel. So, let’s start this article and explore these methods.
Download Practice Workbook
10 Suitable Methods to Round Off Decimals in Excel
In this section of the article, we will discuss 10 suitable methods to round off decimals in Excel. Let’s say, we have the Marks of 6th Grade Students as our dataset. In the dataset, we have the Average Marks of the students. Here, the cells of the Average Marks column have 8 decimal points. Our goal is to round off these decimal points.
Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.
1. Using INT Function
Using the INT function is one of the simplest ways to round off decimals in Excel. The INT function simply rounds a number down to its nearest integer. Now, let’s follow the steps mentioned below.
Steps:
- Firstly, use the following formula in cell G5.
=INT(F5)Here, cell F5 indicates the first cell of the Average Marks column.
- Then, press ENTER.
As a result, you will have 75.33333 rounded down to its nearest integer as shown in the following image.
- Finally, use the AutoFill option of Excel to obtain the remaining outputs.
Read More: How to Change Decimal Places in Excel (3 Effective Ways)
2. Applying TRUNC Function
Using the TRUNC function also allows us to round off decimals in Excel. The TRUNC function keeps a specified number of decimal points and omits the rest without rounding up or rounding down. For instance, if we use the TRUNC function for the number 5.56783 and keep 2 decimal points, it will simply return 5.56. Let’s use the instructions outlined below to do this.
Steps:
- Firstly, apply the formula given below in cell G5.
=TRUNC(F5,2)Here, 2 indicates the number of decimal points we want to keep.
- After that, hit ENTER.
Subsequently, you will have the following output on your worksheet.
- Lastly, use Excel’s AutoFill option to get the rest of the Rounded Average Marks as demonstrated in the image below.
Read More: How to Stop Excel from Rounding Up Decimals (4 Easy Methods)
3. Utilizing ROUNDUP Function
Using the ROUNDUP function is another smart way to round off decimals in Excel. Let’s follow the steps mentioned below to use the ROUNDUP function to round off decimal points in Excel.
Steps:
- Firstly, use the following formula in G5.
=ROUNDUP(F5,2)Here, 2 indicates the number of digits we want to keep after the decimal point.
- Following that, press ENTER.
As a result, the Average Marks of Peter will be rounded up from 75.33333333 to 75.34.
- Finally, use the AutoFill feature of Excel to get the rest of the outputs.
Read More: How to Fix Decimal Places in Excel (7 Simple Ways)
4. Using ROUNDDOWN Function
Using the ROUNDDOWN function is almost similar to the ROUNDUP function. Although the arguments of both functions are the same, their outputs are totally different. Now, let’s follow the procedure discussed in the following section.
Steps:
- Firstly, apply the formula given below in cell G5.
=ROUNDDOWN(F5,2)Here, 2 represents the number of decimal places we want to keep.
- Then, press ENTER.
Consequently, the Average Marks of Peter will be rounded down to 75.33.
- Lastly, you can get the remaining outputs by using the AutoFill feature of Excel.
Read More: How to Set Decimal Places in Excel with Formula (5 Effective Ways)
5. Employing ROUND Function
The ROUND function is one of the most used functions to round off decimals in Excel. The ROUND function rounds up the last significant digit when it is greater or equal to 5. If the last significant digit is less than 5, then it is rounded down. Now, let’s use the instructions outlined below.
Steps:
- Firstly, use the following formula in cell G5.
=ROUND(F5,2)- Following that, hit ENTER.
As a result, 75.33333333 will be rounded down to 75.33 as the last significant digit is less than 5 here.
- Now, you can use the AutoFill option to obtain the remaining outputs as demonstrated in the following picture.
Read More: How to Remove Decimals in Excel with Rounding (10 Easy Methods)
6. Applying MROUND Function
Applying the MROUND function allows us to round off decimals to a multiple of a specified fraction. For instance, if we use 0.25 as a multiple, then the number will be rounded off to the nearest multiple of 0.25. Now let’s follow the steps discussed below.
Steps:
- Firstly, apply the formula below in cell G5.
=MROUND(F5,0.25)Here, 0.25 indicates the multiple argument of the MROUND function.
- Afterward, press ENTER.
Subsequently, you will have the following output on your worksheet.
- Finally, use the Autofill feature of Excel to get the rest of the Rounded Average Marks as shown in the following image.
Read More: How to Get 2 Decimal Places Without Rounding in Excel (4 Ways)
7. Using CEILING and CEILING.MATH Functions
In this section of the article, we will use CEILING and CEILING.MATH functions to round off decimals in Excel. Both CEILING and CEILING.MATH functions round up a number according to specified significance. But the difference between these two functions is the way they handle the negative numbers. The CEILING function rounds off both the positive and negative numbers away from 0. On the other hand, the CEILING.MATH function rounds off the positive numbers away from 0 but the negative numbers towards 0.
Let’s say, we have the Temperature of 3 Days of different times of a day as our dataset. In the dataset, we have the Average Temperatures. Our goal is to round off the decimals of the Average Temperatures. Now, let’s follow the steps mentioned below to do this.
Steps:
- Firstly, use the following formula in cell F6.
=CEILING(E6,1)Here, cell E6 refers to the first cell of the Average Temperature column.
- After that, press ENTER.
As a result, you will have the following output on your worksheet.
- Then, use the formula given below in cell G5.
=CEILING.MATH(E6,1)- Now, hit ENTER.
Consequently, you will have the rounded average temperature as shown in the image below.
- Now, select the cells F6 and G6. Then, drag the Fill Handle up to cell G11 and you will have the following output.
Now, let’s how these 2 functions handle the negative numbers.
- Use the following formula in cell F12.
=CEILING(E12,-1)- Then, press ENTER.
As a result, you will see that the CEILING function has rounded off the number away from 0 and returned -4 as shown in the following image.
- Next, apply the formula given below in cell G12.
=CEILING.MATH(E12,-1)- After that, hit ENTER.
Consequently, the CEILING.MATH function will round off the number towards 0 and return -3.
- Lastly, select cells F12 and G12 together and drag the Fill Handle to get the remaining outputs as demonstrated in the following picture.
Read More: How to Change Decimal Separator in Excel (7 Quick Methods)
8. Incorporating FLOOR with FLOOR.MATH Functions
The FLOOR and FLOOR.MATH functions generally round down a number according to a specified significance. For positive numbers both functions yield the same result. But for the negative numbers, the FLOOR function rounds off a number toward 0. On the other hand, the FLOOR.MATH function rounds off a negative number away from 0. Now let’s use the steps mentioned below.
Steps:
- Firstly, use the following formula in cell F6.
=FLOOR(E6,1)- Following that, press ENTER.
As a result, you will have the Average Temperature rounded down to 6 as shown in the following image.
- After that, use the formula given below in cell G6.
=FLOOR.MATH(E6,1)- Then, press ENTER.
Consequently, you will have the following output in cell G6.
- Next, select cells F6 and G6. Afterward, drag the Fill Handle up to cell G11 and you will get the following outputs.
Now, let’s apply these functions for negative numbers.
- Use the formula below in cell F12.
=FLOOR(E12,-1)- After that, hit ENTER.
As a result, the FLOOR function will round off the Average Temperature towards 0 and return -3.
- Now, apply the following formula in cell G12.
=FLOOR.MATH(E12,-1)- Following that, press ENTER.
Subsequently, the FLOOR.MATH function will round off the Average Temperature away from 0 and return -4 as demonstrated in the following picture.
- Lastly, select cells F12 and G12. Then, drag the Fill Handle to copy down the formula.
Consequently, you will have the rounded Average Temperature as shown in the image below.
Read More: How to Add Decimals in Excel (3 Easy Ways)
9. Using Increase Decimal and Decrease Decimal Commands from Ribbon
In this section of the article, we will use the Increase Decimal and Decrease Decimal option of Excel to round off decimals. We can find these 2 options in the Number group of the Ribbon in Excel. Now, let’s follow the guidelines discussed below.
Steps:
- Firstly, copy and paste the cells of the Average Marks column in the Increase Decimal and in the Decrease Decimal columns.
- After that, select the cells of the Increase Decimal column and go to the Home tab from Ribbon.
- Then, click on the Increase Decimal option as marked in the image below.
Note: Each time you click on the Increase Decimal option, one extra decimal place will add the existing number in the selected cell.
Consequently, the number of decimal places will increase as shown in the following image.
- Following that, select the cells of the Decrease Decimal column.
- Then, click on the Decrease Decimal option from the Number group.
Note: Here, we clicked on the Decrease Decimal option 6 times to keep 2 decimal places.
As a result, you will have the following outputs in the Decrease Column as demonstrated in the following picture.
Read More: How to Remove Decimal Places in Excel (5 Easy Methods)
10. Utilizing Format Cells Option
Utilizing Format Cells option is another efficient way to round off decimals in Excel. Let’s follow the steps mentioned below to round off decimals.
Steps:
- Firstly, copy and paste the cells of the Average Marks in the Rounded Average Marks column.
- Now, select the cells of the Rounded Average Marks column.
- Then, right-click on any of the cell of the selected column.
- After that, select the Format Cells option.
As a result, the Format Cells dialogue box will open on your worksheet.
- Now, go to the Number tab from the Format Cells dialogue box.
- Then, insert 2 in the Decimal places field.
- Subsequently, click OK.
Consequently, you will have the Rounded Average Marks rounded off to 2 decimal places as shown in the image below.
Read More: How to Remove Decimals without Rounding in Excel (4 Suitable Ways)
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.
Conclusion
So, these are the most common & effective methods you can use anytime while working with your Excel datasheet to round off decimals in Excel. If you have any questions, suggestions, or feedback related to this article you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy.
