Rounding off decimals helps to improve the readability of your information. It is also simpler to work with numbers where the decimals have been rounded off. It is also very helpful when exact values are not required.
4 Methods to round off decimals in Microsoft Excel:
- The ROUND function
- The ROUNDUP function
- The ROUNDDOWN function
- The MROUND function
Let us begin by looking at each of these methods.
Method #1: The ROUND Function
This is the simplest and quickest way to round off the given numbers.
Step #1: Open your Excel workbook
Open the excel workbook on which you want to work.
Step #2: Enter the ROUND function
Place your cursor in the cell next to the field where you have a value with lots of decimal places.
Type in the formula: =ROUND(Cell number,x)
where Cell number is the cell with the decimal number, in our example A2,
and x is the number of decimal places you want to round off to.
In our example, we want two decimal places, so our formula would be:
=ROUND(A2,2)
Step #3: Get the results
Once you enter the formula, press enter on your keyboard. You will have a new field with the same value, but only showing two decimal places.
Method #2: Using the ROUNDUP Function
This function always rounds up the number to the nearest decimal specified. But the results will differ depending on the initial number, so keep this in mind:
- If the argument for the decimal number is positive, then Excel will round off to the right side of the decimal.
- If the argument for the decimal number is negative, then Excel will round off to the left side of the decimal.
- If the argument for the decimal number is zero, then Excel will round off to the nearest one.
Step #1: Open your Excel workbook
Open the excel workbook on which you want to work.
Step #2: Enter the ROUNDUP function
Place your cursor in the cell next to the field where you have a value with lots of decimal places.
Type in the formula: =ROUNDUP(Cell number,x)
where Cell number is the cell with the decimal number, in our example A2,
and x is the number of decimal places you want to round up to.
In our example, we want three decimal places, so our formula would be:
=ROUNDUP(A2,3)
Step #3: Get the results
Once you enter the formula, press enter on your keyboard. You will have a new field with the same value, but only showing three decimal places.
Alternative option:
If the number of decimal places differs for each number, you can change the formula slightly.
Add a column detailing the number of decimal points to round off each number.
Modify your formula to be =ROUNDUP(Cell number x, Cell number y)
where x is the cell number of the number available to you,
and y is the cell number of the cell detailing the number of decimal points to round off.
For our example, it will be =ROUNDUP(A2,B2)
The result will now show in column C.
Method #3: Using the ROUNDDOWN Function
This function always rounds down the number to the nearest decimal specified. But the results will differ depending on the initial number, so keep this in mind:
- If the argument for the decimal number is positive, then Excel will round off to the right side of the decimal.
- If the argument for the decimal number is negative, then Excel will round off to the left side of the decimal.
- If the argument for the decimal number is zero, then Excel will round off to the nearest one.
Step #1: Open your Excel workbook
From your device, open the excel workbook on which you want to work.
Step #2: Enter the ROUNDDOWN function
Place your cursor in the cell next to the field where you have a value with lots of decimal places.
Type in the formula: =ROUNDDOWN(Cell number,x)
where Cell number is the cell with the decimal number, in our example A2,
and x is the number of decimal places you want to round down to.
In our example, we want three decimal places, so our formula would be:
=ROUNDDOWN(A2,3)
Step #3: Get the result
Once you enter the formula, press enter on your keyboard. You will have a new field with the same value, but only showing three decimal places.
Alternative option:
If the number of decimal places differs for each number, you can change the formula slightly.
Add a column detailing the number of decimal points to round off each number.
Modify your formula to be =ROUNDDOWN(Cell number x, Cell number y)
where x is the cell number of the number available to you,
and y is the cell number of the cell detailing the number of decimal points to round off.
For our example, it will be =ROUNDDOWN(A2,B2)
The result will now show in column C.
Method #4: Using the MROUND Function
This function always rounds off the number to the nearest multiple specified.
Step #1: Open your Excel workbook
Open the excel workbook on which you want to work.
Step #2: Enter the MROUND function
Place your cursor in the cell next to the field where you have a value with lots of decimal places.
Type in the formula: =MROUND(Cell Number,y)
where Cell number is the cell with the decimal number, in our example A2,
and y is the nearest multiple to round off the number to.
In our example, we want the nearest multiple of 7, so our formula would be:
=MROUND(A2,7)
Step #3: Get the result
Once you enter the formula, press enter on your keyboard, and you will have a new field with the number rounded to the closest multiple of seven. In our example, it is 56.
Alternative option:
If the multiple you want to use differs for each number, you can change the formula slightly.
Add a column detailing the different multiples you want to use.
Modify your formula to be =MROUND(Cell number x, Cell number y)
where x is the cell number of the number available to you,
and y is the cell number of the cell detailing the multiple.
For our example, it will be =MROUND(A2,B2)
The result will now show in column C.
Conclusion
You may wonder why we don’t suggest the Increase Decimal and Decrease Decimal options located in the Home Tab ribbon.
Although these icons improve your decimals visually, they are not practical for creating formulas. If you have a quick report that you want to make pretty before printing, then these icons are perfect.
If you are going to do the same calculations often, then it is best to put in place these methods so that you get consistent results.