Excel Tutorials
Round a number up, down, or to a specific number of decimal places without losing the underlying precision. This lesson uses the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
Rounding changes how a number displays or calculates, cutting it down to a set number of decimal places instead of showing every digit. Excel offers two different ways to round: changing the display format, which only affects how the number looks, or using a function like ROUND, which actually changes the stored value used in further calculations.
Knowing which one you're using matters, a number that looks rounded because of formatting can still throw off a total if the underlying full-precision value is what's actually being added up.
Step-by-step, matching the video above.
Formatting only changes how a number looks on screen. The ROUND function changes the actual value Excel uses in later calculations.
Type =ROUND(, click the cell with your number, add a comma, then the number of decimal places you want, like =ROUND(A2,2) for two decimal places.
Use 0 as the decimal place argument, like =ROUND(A2,0), to round to the nearest whole number.
A negative number works too, =ROUND(A2,-2) rounds to the nearest hundred.
=ROUNDUP(A2,2) always rounds away from zero, regardless of what the next digit would normally round to.
=ROUNDDOWN(A2,2) always rounds toward zero, the opposite of ROUNDUP.
Select the cell, press Ctrl+1, and adjust the decimal places under Number format, the stored value stays exactly the same, only the display changes.
| Formula on 12.847 | Result |
|---|---|
| =ROUND(12.847,2) | 12.85 |
| =ROUND(12.847,1) | 12.8 |
| =ROUND(12.847,0) | 13 |
| =ROUNDUP(12.841,1) | 12.9 |
| =ROUNDDOWN(12.849,1) | 12.8 |
| =ROUND(12.847,-1) | 10 |
| =MROUND(12.847,5) | 15 |
| =FLOOR(12.847,5) | 10 |
| =CEILING(12.847,5) | 15 |
| =FLOOR(12.847,0.1) | 12.8 |
| =CEILING(12.847,0.1) | 12.9 |
| =INT(12.847) | 12 |
| =TRUNC(12.847,1) | 12.8 |
| =TRUNC(12.847) | 12 |
| =ODD(12.847) | 13 |
| =EVEN(12.847) | 14 |
Notice ROUND(12.847,1) gives 12.8, not 12.9, since the third decimal (7) only affects the second decimal place, not the first. ROUNDUP, ROUNDDOWN, FLOOR and CEILING all ignore normal rounding rules and just go in their stated direction, MROUND(12.847,5) lands on 15 because 15 is closer to 12.847 than 10 is, and TRUNC(12.847,1) happens to match ROUNDDOWN(12.847,1) here since the digit being cut (4) is below 5 either way.
ROUND, ROUNDUP, ROUNDDOWN and MROUND cover most day-to-day needs, but Excel has a few more specialized rounding functions worth knowing.
=FLOOR(A2,5) always rounds down to the nearest multiple of 5, no matter how close the number is to the next one up. Different from MROUND, which rounds to whichever multiple is closer.
=CEILING(A2,5) is FLOOR's opposite, always rounding up to the next multiple of 5. Useful for a minimum order quantity or a price you never want to underestimate.
=INT(A2) removes everything after the decimal point, always rounding toward negative infinity. For a positive number that's the same as just cutting off the decimals, but it behaves differently from TRUNC on negative numbers.
=TRUNC(A2,1) chops a number down to a set number of decimal places without applying any rounding logic at all, it simply deletes the extra digits. TRUNC and ROUNDDOWN often agree, but TRUNC never rounds, it only truncates.
=ODD(A2) and =EVEN(A2) both round away from zero to reach the nearest odd or even whole number, useful any time a value needs to land on a specific numeric pattern, like scheduling in two-day increments.
Worth its own callout, since it's the function most people don't know exists but reach for constantly once they do.
=MROUND(A2,5) rounds A2 to the nearest multiple of 5, useful for things like rounding prices to the nearest nickel or quantities to the nearest case size.
ROUND works in decimal places. MROUND works in any increment you specify, 5, 10, 0.25, whatever your situation calls for.
Rounding order quantities to the nearest case of 12, or rounding a price to the nearest 25 cents, both are one MROUND formula rather than a workaround.
A cell that displays 2 decimal places because of formatting still holds its full unrounded value underneath, which can make totals look slightly off. Use the ROUND function if the calculation itself needs to change.
Rounding a number before using it in further formulas compounds small errors across a chain of calculations. Round only the final displayed result when possible.
ROUNDUP(-2.1, 0) returns -3, not -2, since it rounds away from zero in both directions. This trips people up on negative numbers.
On positive numbers they usually match, but on negatives they diverge: INT(-2.5) returns -3 (it rounds toward negative infinity), while TRUNC(-2.5) returns -2 (it just chops the decimal off toward zero). Use TRUNC if you specifically want to discard decimals without any rounding behavior.
Precision shouldn't depend on remembering the right formula.
Free 14-day trial. No credit card required.
Use the ROUND function, like =ROUND(A2,2), where 2 is how many decimal places to round to.
Decimal formatting only changes how a number displays, the stored value keeps its full precision. ROUND actually changes the value itself.
Use ROUNDUP, like =ROUNDUP(A2,2), which always rounds away from zero rather than to the nearest value.
Use ROUNDDOWN, like =ROUNDDOWN(A2,2), which always rounds toward zero.
Use MROUND, like =MROUND(A2,5) to round to the nearest multiple of 5.
Use a negative number of decimal places, like =ROUND(A2,-2) for the nearest hundred, or =ROUND(A2,-3) for the nearest thousand.
This usually means cells are formatted to show fewer decimals than they actually store. Use the ROUND function if you need the displayed and calculated values to match exactly.
FLOOR always rounds down to a set multiple and CEILING always rounds up, while MROUND rounds to whichever multiple is closer, in either direction.
They often produce the same result, but TRUNC never actually rounds, it simply deletes digits past the point you specify. ROUNDDOWN is a true rounding function that always moves toward zero.
ODD rounds a number away from zero to the nearest odd whole number, and EVEN does the same to the nearest even whole number.
Every business starts with a spreadsheet. Updoot is where you scale past it, totals calculate precisely, automatically.
Start Your Free Trial →