When working in Excel, sometimes you need to use values that are a little less precise. For example, if you’re working with the average prices of products, you probably don’t need them to be accurate to 7 decimal places. The same is probably true of the results of many other calculations. Luckily, Microsoft Excel has three related functions to help you with this: ROUND, ROUNDUP, and ROUNDDOWN. You can specify exactly how you want a number rounded, including rounding to the left of the decimal point or the nearest multiple of any multiple of 10.
In this guide, you’ll learn about Microsoft Excel’s ROUND function, as well as the related functions ROUNDUP and ROUNDDOWN. You will learn how to use them to round numbers to fewer decimal places, to whole numbers, to the nearest multiple of ten and beyond, as well as to one decimal place to the left of the decimal point. Each example is worked out step-by-step, so you can see how the different options affect the results.
ROUND Formula in Excel
The syntax for Excel’s ROUND function is ROUND(number; num_digits). It has two required parameters: the number you want to round and the number of decimals to which you want to round it. For example, you can round 1.932 to two decimals as follows: ROUND(1.932; 2) = 1.93.
The ROUND function rounds to the nearest whole number: decimal values in the 0 - 4 range are rounded down, while those in the 5 - 9 range are rounded up. However, if you need to ensure that it rounds in a specific direction, you can use the ROUNDUP or ROUNDDOWN functions, both of which have the same parameters as ROUND. ROUNDUP rounds away from zero, and ROUNDDOWN towards zero.
How to Round a Number in Excel?
To round a number in Excel, use the ROUND(number; num_digits). For ‘number’, type the number you want to round. For ‘num_digits’, type the number of decimal places you want. Using “0” will round to the nearest integer, while negative numbers will round to the left of the decimal point. You can find the ROUND function under Formulas > Maths & Trig > ROUND.
- 1. Click on an empty cell and go to Formulas > Maths & Trig > ROUND. Alternatively, type in the name of the function after the equal sign.
- 2. Add the number you want to round as the first parameter in the ‘Formula Builder’.
- 3. Add a positive integer under ‘Num_digits’ to specify the number of decimal places you want in the result.
- 4. Click ‘Done’ to see the results.
Examples of How to Use the ROUND Function in Excel
Below, you have examples showing the different rounding options available with ROUND, ROUNDUP, and ROUNDDOWN.
When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.READ MORE
Use ROUND with Positive and Negative Numbers
The examples below illustrate how ROUND works with positive and negative values. Moreover, you can see and compare the results of ROUNDUP and ROUNDDOWN.
- 1. In separate cells, type in the values of the numbers you want to round. In this case, I will use the positive and negative values of the same numbers.
- 2. In an empty cell, type the ROUND function and select the first value you want to round. Add a semicolon and type the number of decimal places you want to round to.
- 3. Grab the fill handle and drag it down to the last value. As you can see, the first and third values are rounded down - towards zero - while the second and fourth are rounded up - away from zero.
- 4. Repeat steps 2 and 3 for the ROUNDUP and ROUNDDOWN functions. As you can see, ROUNDUP has rounded up all the values, while ROUNDDOWN has rounded down.
Use ROUND to Get a Whole Number
Follow the instructions below to round numbers to the nearest integer. I will use the same values as before to better illustrate the effect.
- 1. In an empty cell, type the ROUND function. Select the cell with the number to round and use “0” for the ‘num_digits’ parameter.
- 2. Grab the fill handle and drag it down to the last number.
- 3. Repeat steps 1 and 2 with the ROUNDUP and ROUNDDOWN functions. As you can see, the results are quite different in this case.
If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google SheetREAD MORE
Use ROUND to the Left of the Decimal Point
Follow the steps below to see how rounding to the left of the decimal point affects the values.
- 1. Type the ROUND function and select the first value. For the second parameter, use a negative integer. In this case, I will use “-1” to ge the nearest multiple of ten.
- 2. Grab the fill handle and drag it down to the last value.
- 3. Repeat steps 1 and 2 with ROUNDUP and ROUNDDOWN.
Use ROUND with Other Functions
If you want to round the results of other functions, you can wrap the ROUND function around it. Below, I will use the AVERAGE function on a set of values and wrap the ROUND function around it.
- 1. Type the ROUND function, and after the parenthesis, type in the function name: in this case, AVERAGE.
- 2. Select the range of values for which you want the average and close the parenthesis.
- 3. Add a semicolon and type the number of decimal places you want to round to. Close the parenthesis and press ‘Enter’ to see the results.
- 4. As you can see below, the average value without rounding has 8 decimal places.
The Excel ROUND function lets you round numbers in any way you want, including to the left of the decimal point. By default, the function rounds down decimals from zero to four, while decimals from five to nine are rounded up. However, you can use the ROUNDUP and ROUNDDOWN functions to choose the direction in which the number is rounded.
You now know how to use the ROUND function in Microsoft Excel, as well as the related functions ROUNDUP and ROUNDDOWN, to specify the direction in which it's rounded. You also know how to use the different options available for the num_digits parameter. Using positive integers allows you to specify the number of decimal places you want to be returned, and using zero rounds to the nearest integer, while negative integers round to the left of the decimal point.