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.
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.
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.
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.
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.
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.
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.
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.
