How to add rounding to a formula in Excel

You can watch a video tutorial here.

Excel is frequently used for calculations and has many functions to help with basic mathematical operations.  When using formulas to create a calculated column, you may need to round the result if there are too many decimal places. Excel has ROUND functions that can be used in combination with the formula to round the result and reduce the number of steps for calculation. 

There are four types of ROUND functions in Excel and these are discussed below, followed by examples of how to use each one.

1. ROUND()
   • Syntax: ROUND(number, num_digits)
     1. number: this is the number to be rounded
     2. num_digits: the number of decimal places to which the number is to be rounded
   • This follows the general rule of rounding i.e.
     1. If the number being rounded is followed by 5, 6, 7, 8, or 9, the number is rounded up to the given decimal place
     2. If the number being rounded is followed by 0, 1, 2, 3, or 4, the number is rounded down to the given decimal place
   • Examples:
     1. ROUND(23.45612,2) becomes 23.46
     2. ROUND(23.42321,2) becomes 23.42
2. ROUNDUP()
   • Syntax: ROUNDUP(number, num_digits)
     1. number: this is the number to be rounded
     2. num_digits: the number of decimal places to which the number is to be rounded
   • This rounds the number up to the given decimal place
   • Examples:
     1. ROUNDUP(23.45612,2) becomes 23.46
     2. ROUNDUP(23.42321,2) becomes 23.43
3. ROUNDDOWN()
   • Syntax: ROUNDDOWN(number, num_digits)
     1. number: this is the number to be rounded
     2. num_digits: the number of decimal places to which the number is to be rounded
   • This rounds the number down to the given decimal place
   • Examples:
     1. ROUNDDOWN(23.45612,2) becomes 23.45
     2. ROUNDDOWN(23.42321,2) becomes 23.42
4. MROUND()
   • Syntax: MROUND (number, multiple)
     1. number: this is the number to be rounded
     2. multiple: the multiple to which the number is to be rounded
   • This rounds the number to the nearest multiple
   • Examples:
     1. MROUND(23.45612,0.2) becomes 23.4
     2. MROUND(13,3) becomes 12

Option 1 – Use the ROUND() function

Step 1 – Add the ROUND() function to the formula 

• Select the cell that has the formula and press F2 to enable it for editing
• Edit the formula using cell references:

=ROUND(<formula>,2)

Step 2 – Copy the formula

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

• The numbers are rounded (either up or down, depending on the value of the number),  to 2 decimal places

Option 2 – Use the ROUNDUP() function 

Step 1 – Add the ROUNDUP() function to the formula 

• Select the cell that has the formula and press F2 to enable it for editing
• Edit the formula using cell references:

=ROUND(<formula>,2)

Step 2 – Copy the formula

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

• The numbers are rounded up to 2 decimal places

Option 3 – Use the ROUNDDOWN() function 

Step 1 – Add the ROUNDDOWN() function to the formula 

• Select the cell that has the formula and press F2 to enable it for editing
• Edit the formula using cell references:

=ROUNDDOWN(<formula>,2)

Step 2 – Copy the formula

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

• The numbers are rounded down to 2 decimal places

Option 4 – Use the MROUND() function 

Step 1 – Add the MROUND() function to the formula 

• Select the cell that has the formula and press F2 to enable it for editing
• Edit the formula using cell references:

=MROUND(<formula>,2)

Step 2 – Copy the formula

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

• The numbers are rounded to the nearest multiple of 2