How to Round Numbers in Excel
The article considered how to round number in excel using various functions such as ROUND, ROUNDDOWN, ROUNDUP and other rounding methods. There are also examples of formulas, how to round to a whole number, to tenths, to thousands, to 5, 10 or 100, how to round to a multiple, and many other examples.
Round a number in by changing the cell format
If you want to round numbers in excel purely for visual presentation, you can change the format of a cell by following these steps:
- Select the cell with the numbers you want to round.
- Open the Format Cells dialog box by pressing Ctrl+1 or right-click on a cell and select Format Cells from the context menu.
How to round a number in Excel - Format Cells
- On the Number tab, select Numeric or Currency Format and enter the number of decimal places you want to display in the field. Number of decimal places". preview how it will be rounded number will be displayed in the Sample section.
- Click the OK button to save your changes and close the dialog box.
How to round a number in Excel - Round a number by changing the cell format
Note! This method changes the display format without changing the actual value stored in the cell. If you refer to this cell in any formulas, then the original number will be used in all calculations without rounding. If you actually need round number in a cell, then use Excel's rounding functions.
How to round a number with the ROUND function
ROUND is the basic number rounding function in Excel that rounds a number to a specified number of decimal places.
Syntax :
Number - any real number you want to round. It can be a number or a cell reference.
Number_digits - the number of digits to round the number. You can specify a positive or negative value for this argument:
- If num_digits is greater than 0, the number is rounded to the specified number of decimal places. For example, =ROUND(17.25, 1) rounds 17.25 to 17.3.
To round number to tenths , set the number_digits argument to 1.
How to round a number in Excel - How to round a number to tenths
If it is needed round up to hundredths , set the number_digits argument to 2.
How to round a number in Excel - How to round a number to hundredths
In order to round number to thousandths , enter 3 in num_digits.
How to round a number in Excel - How to round a number to thousandths
- If num_digits is less than 0, all decimal places are removed and the number is rounded to the left of the decimal point (to tenths, to hundreds, to thousands, and so on). For example, =ROUND(17.25, -1) rounds 17.25 to the nearest multiple of 10 and returns 20.
- If num_digits is 0, the number is rounded to the nearest whole number (no decimal places). For example, =ROUND(17.25, 0) rounds 17.25 to 17.
The following image shows some examples, how to round number in excel in the ROUND formula:
How to round a number in Excel - Examples of formulas, how to round a number using the ROUND function
How to round a number up with the ROUNDUP function
The ROUNDUP function rounds a number up (from 0) to the specified number of digits.
Syntax :
Number - number to round.
num_digits - the number of digits to which you want to round the number. You can specify either positive or negative numbers in this argument, and it works like the number_digits of the ROUND function above, except that the number is always rounded up.
How to round a number in Excel - Examples of formulas, how to round a number up using the ROUNDUP function
How to round a number down with the ROUNDDOWN function
The ROUNDDOWN function in Excel does the opposite of what ROUNDUP does, i.e. it rounds the number down.
Syntax :
Number - the number to be rounded.
num_digits - the number of digits to which you want to round the number. Works like the number_digits argument of the ROUND function, except that the number is always rounded down.
The following image demonstrates, how to round number in excel down with the ROUNDDOWN function in action.
How to round a number in Excel - Examples of formulas, how to round a number down using the ROUNDDOWN function
This is how it works rounding numbers in excel . I hope now you know how among all these ways, how to round number in excel, choose the most suitable for your needs.
