Rounding is common in pricing, tax and reports. Excel has several functions for it.
ROUND
=ROUND(number, num_digits)
=ROUND(3.14159, 2) gives 3.14=ROUND(1234, -2) gives 1200 (a negative number rounds to hundreds, tens and so on)
ROUNDUP and ROUNDDOWN
=ROUNDUP(2.1, 0) gives 3. It always rounds away from zero.=ROUNDDOWN(2.9, 0) gives 2. It always rounds toward zero.
Round to a multiple
=MROUND(47, 5) gives 45 (nearest multiple of 5).=CEILING(47, 5) gives 50 (always up).=FLOOR(47, 5) gives 45 (always down).
Formatting is not rounding
Showing 2 decimals with the number format only changes how the value looks. The cell still holds the full number, and totals use it. When the stored value matters, use ROUND.
Remove decimals
=INT(5.9) gives 5 and =TRUNC(5.9) also gives 5. They differ only for negative numbers.
Worked example
For 3.14159: =ROUND(A2,2) is 3.14, =ROUNDUP(A2,2) is 3.15, =ROUNDDOWN(A2,2) is 3.14. A negative digit rounds left: =ROUND(1234,-2) is 1200.
Common mistakes
- Formatting to 2 decimals and thinking the value changed. Formatting only hides digits.
- Using ROUND on every step and adding rounding errors.
- Confusing ROUNDUP with rounding half up.
Frequently asked questions
ROUND or format?
ROUND changes the value. Formatting only changes how it looks.
How do I round to the nearest 5?
=MROUND(A2,5).
How do I round to whole thousands?
=ROUND(A2,-3).
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: ROUND, ROUNDUP, ROUNDDOWN, MROUND.
Start Excel practice → Case studies
