To round a number in Excel, use the ROUND function: =ROUND(A2,2) rounds the value in A2 to two decimal places. Change the second number to control the precision: 0 rounds to a whole number, and negative numbers round to the left of the decimal point, so =ROUND(A2,-2) rounds to the nearest hundred. Use ROUNDUP or ROUNDDOWN to force the direction, and MROUND, CEILING.MATH, or FLOOR.MATH to round to a multiple such as 5 or 0.25.
How to Use the ROUND Function
- Click the cell where you want the rounded result.
- Type
=ROUND( - Click the cell with the number (or type a number), then type a comma.
- Type the number of digits: 2 for two decimal places, 0 for a whole number, -1 for the nearest ten.
- Type
)and press Enter.
The syntax is =ROUND(number, num_digits). Values of 5 or more round up, and values below 5 round down. Excel rounds halves away from zero, so 2.5 becomes 3 and -2.5 becomes -3. You can also round inside another formula, for example =ROUND(B2*C2,2) to round a price times quantity. If you are new to formulas, see how to use a formula in Excel.
Examples With 1234.5678
=ROUND(1234.5678,2)returns 1234.57=ROUND(1234.5678,1)returns 1234.6=ROUND(1234.5678,0)returns 1235=ROUND(1234.5678,-1)returns 1230=ROUND(1234.5678,-2)returns 1200=ROUND(1234.5678,-3)returns 1000
Always Round Up or Down: ROUNDUP and ROUNDDOWN
These take the same arguments as ROUND but ignore the 5-or-more rule:
=ROUNDUP(1234.5612,2)returns 1234.57, away from zero.=ROUNDDOWN(1234.5678,2)returns 1234.56, toward zero.=ROUNDUP(12.1,0)returns 13, useful for things like “how many boxes do I need?”
Round to a Multiple: MROUND, CEILING.MATH, and FLOOR.MATH
=MROUND(17,5)returns 15 and=MROUND(18,5)returns 20, the nearest multiple of 5.=CEILING.MATH(17,5)returns 20, always rounding up to the next multiple.=FLOOR.MATH(17,5)returns 15, always rounding down.=MROUND(A2,0.25)rounds prices or hours to the nearest quarter.=MROUND(A2,"0:15")rounds a time to the nearest 15 minutes. Format the result as a time. For more on time math, see how to calculate hours in Excel.
MROUND returns #NUM! if the number and the multiple have different signs; use CEILING.MATH or FLOOR.MATH for negative numbers.
Other Rounding Functions
- INT rounds down to the nearest integer:
=INT(3.7)is 3 and=INT(-3.7)is -4. - TRUNC cuts off decimals without rounding:
=TRUNC(-3.7)is -3, and=TRUNC(3.789,2)is 3.78. - EVEN and ODD round up to the next even or odd integer:
=EVEN(3)is 4 and=ODD(2)is 3.
Rounding vs. Changing the Display
The Increase Decimal and Decrease Decimal buttons on the Home tab, and the Number format in Format Cells, only change how a number looks. The full value is still used in calculations. That is why a column of displayed values can appear to add up to a different total than the one Excel shows. If calculations must use the rounded values, use ROUND in a formula.
Excel also has a workbook-wide Set precision as displayed option under File > Options > Advanced, but it permanently changes stored values to match what is shown, so use it with caution and keep a backup.
Round a Whole Column at Once
- Insert an empty column next to your numbers.
- Enter the rounding formula in the first row, such as
=ROUND(A2,2). - Double-click the fill handle to copy it down.
- To replace the originals, copy the new column, then right-click the original column and choose Paste Special > Values. Delete the helper column.
Troubleshooting
The result shows more decimals than expected
The cell’s number format may be showing extra digits. Set the format to Number with the decimals you want, or General.
A sum of rounded values is off by a cent
Rounding each line and rounding the total can give slightly different answers. Decide which one your report should match, and round consistently at the same step, for example rounding each line item and then summing the rounded values.
#VALUE! error
The cell contains text that looks like a number. Convert it with VALUE or Data > Text to Columns first. Numbers in scientific notation are still numbers; see how to get rid of E+ in Excel to display them normally.
Totals do not match the visible numbers
The display is rounded but the values are not. Use ROUND on each value, or round the total.
Frequently Asked Questions
What is the difference between ROUND, ROUNDUP, and ROUNDDOWN?
ROUND follows normal rounding rules, ROUNDUP always rounds away from zero, and ROUNDDOWN always rounds toward zero.
How do I round to the nearest whole number?
Use =ROUND(A2,0). Use INT or ROUNDDOWN to drop the decimal instead.
How do I round to the nearest 10, 100, or 1,000?
Use negative digits: -1 for tens, -2 for hundreds, and -3 for thousands.
How do I round a percentage?
Percentages are decimals, so 12.345% is 0.12345. Use =ROUND(A2,4) to keep two decimals of the percentage. See how to multiply by a percentage in Excel.
Does ROUND work in Google Sheets?
Yes. ROUND, ROUNDUP, ROUNDDOWN, MROUND, INT, and TRUNC work the same way.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.