Mastering how to round in Excel

The most common assumption about rounding in spreadsheets is that it uses the same 'round half up' method taught in school. That assumption is wrong for several functions and for negative numbers. Understanding how to round in Excel and Google Sheets means learning the specific rounding rule each function applies, because the same number can produce different results depending on which function you choose. The exact syntax and behaviour of every rounding function are specified here so you can pick the right one for your data.

Rounding replaces a number with a nearby simpler number that has fewer significant digits or is a multiple of a given value. No single correct method exists. Every rounding method introduces a systematic bias or a tie-breaking rule, and newcomers most often get wrong the assumption that 'round half up' is universal. It is merely the most common convention. Many programming languages and scientific standards use 'round half to even' (bankers' rounding) to avoid cumulative bias in statistical sums.

The Excel ROUND function syntax is ROUND(number, num_digits). It rounds to a specified number of decimal places using the half away from zero rule. For positive numbers this is identical to half up: 1.5 rounds to 2. For negative numbers it differs: -1.5 rounds to -2, not -1. Use a positive num_digits to round to tenths, hundredths, etc. Use a negative num_digits to round to tens, hundreds, thousands: ROUND(123, -1) equals 120, and ROUND(123, -2) equals 100. This is how to round to the nearest 10 or 100 using the standard Excel rounding function.

ROUNDUP(number, num_digits) always rounds away from zero regardless of the digit. ROUNDUP(1.001, 0) gives 2, not 1. ROUNDUP(-1.001, 0) gives -2. Use this when you need a conservative estimate that never understates the value.

ROUNDDOWN(number, num_digits) always rounds toward zero. ROUNDDOWN(1.999, 0) gives 1. ROUNDDOWN(-1.999, 0) gives -1. Use this when you need a floor that never overstates the value.

Negative Digits for Tens and Hundreds

A negative num_digits value in ROUND, ROUNDUP and ROUNDDOWN rounds to the corresponding power of ten. ROUND(123, -1) rounds to the nearest ten (120). ROUNDUP(123, -1) rounds up to 120. ROUNDDOWN(123, -1) rounds down to 120. This works for hundreds, thousands and beyond.

MROUND(number, multiple) rounds a number to the nearest multiple of a given value. MROUND(1.125, 0.25) rounds to 1.25 because 1.125 is exactly halfway between 1.00 and 1.25, and MROUND uses half away from zero. If you need to round to the nearest 5, use MROUND(123, 5) which gives 125. This is the excel round to nearest 5 function.

CEILING.MATH(number, significance, mode) rounds up toward positive infinity. CEILING.MATH(1.1, 1) gives 2. For negative numbers it rounds toward zero unless you use the optional mode argument. CEILING.MATH(-1.1, 1) gives -1.

FLOOR.MATH(number, significance, mode) rounds down toward negative infinity. FLOOR.MATH(1.1, 1) gives 1. FLOOR.MATH(-1.1, 1) gives -2.

TRUNC(number, num_digits) truncates a number by discarding the decimal part without any rounding. TRUNC(1.9) gives 1. TRUNC(-1.9) gives -1. It is identical to rounding toward zero for positive numbers, but for negative numbers it simply removes the decimal portion. TRUNC(1.5) gives 1, while ROUNDDOWN(1.5, 0) also gives 1, they agree here, but TRUNC does not consider the discarded digits at all.

INT(number) rounds down to the nearest integer, always toward negative infinity. INT(1.9) gives 1, same as TRUNC. INT(-1.9) gives -2, which differs from TRUNC(-1.9) = -1. The distinction matters when working with negative values in financial calculations or time data.

A common failure mode is formatting a cell to show two decimal places while the underlying value retains more digits. The displayed number looks rounded, but any formula that uses that cell sees the full precision. This causes totals that appear to add up incorrectly by a penny or two. The fix is to explicitly round the value using the appropriate function before using it in further calculations.

Floating-point representation in IEEE 754 binary64 cannot represent most decimal fractions exactly. The number displayed as 0.3 may be stored as 0.2999999999999999. Rounding that stored value can give a different result than rounding the displayed value. Always round the source data, not the formatted display.

Google Sheets ROUND uses the same half away from zero rule as Excel, with syntax ROUND(value, [places]). ROUNDUP, ROUNDDOWN, MROUND, CEILING, FLOOR, TRUNC and INT all behave identically to their Excel counterparts for both syntax and rounding rules. The only difference is the function names: Google Sheets uses CEILING and FLOOR without the .MATH suffix. Google Sheets MROUND matches Excel MROUND exactly.

When you need to round in Google Sheets, you can rely on the same tie-breaking behaviour as Excel. The Google Sheets function list documentation confirms these behaviours.