Excel Round Not Working? Fix the Formula and Display

Excel’s rounding function is =ROUND(number,num_digits). If it seems not to work, check whether the source is a real number, whether the cell’s displayed decimal places hide the result, and whether Excel is recalculating formulas.

What Excel ROUND does

ROUND changes a numeric result to a specified number of decimal places. It does not simply alter how the number looks: its returned value is rounded, so formulas that use that result will use the rounded value. The second argument, num_digits, controls the place to round to.

A positive num_digits rounds to the right of the decimal point. For example, 2 rounds to hundredths.
Zero rounds to the nearest whole number.
A negative number rounds to the left of the decimal point. For example, -2 rounds to the nearest hundred.

With the value 12.345 in A2, =ROUND(A2,2) returns 12.35. The digit after the hundredths place is 5, so Excel rounds the hundredths digit up. Excel rounds halfway cases away from zero: with -2.5 in A2, =ROUND(A2,0) returns -3, not -2. This rule can matter when comparing Excel results with software that uses a different halfway-rounding convention.

Check the formula and its cell references

Start by selecting the cell with the formula and reading the formula bar. Confirm that the formula starts with an equals sign, contains the intended source cell, and uses the right number of decimal places. A formula can be syntactically valid and still point to the wrong cell or round at the wrong stage.

With 12.345 in A2, =ROUND(A2,2) returns 12.35, while =ROUND(A2,0) returns 12. If the value is in B2 rather than A2, update the reference. When copying a formula down a column, check whether the references should move with each row or be fixed with dollar signs, such as $A$2. An incorrect reference is a common reason the result looks unrelated to the value being checked.

On Windows, select the result cell and inspect the formula bar; pressing F2 lets you edit the formula in place. On Mac, the equivalent editing shortcut is Control+U in many Excel configurations, though keyboard settings can vary. You can also click in the formula bar and edit there. If Excel displays the formula as text in the cell instead of calculating it, check the cell format and whether an apostrophe was entered before the equals sign.

Distinguish rounding from number formatting

A frequent source of confusion is that formatting changes the display, not the stored value. If A2 contains 12.345 and you set the cell to show two decimal places, Excel may display 12.35 while retaining 12.345 for later calculations. A sum or another formula can therefore appear to disagree with the visible figures.

To round the value used in later calculations, use ROUND in the formula. To change only how many digits appear, select the cells and use Home > Number > Decrease Decimal or Increase Decimal. You can also open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, choose the Number category, and set Decimal places. Those display options do not replace the original values with rounded ones.

For example, suppose A2 contains 1.234 and B2 contains 1.234. If A2 is only formatted to show one decimal place, it displays as 1.2 but still contributes 1.234 to a total. If B2 is actually rounded with =ROUND(A2,1), the formula result is 1.2. Use the formula approach when the rounded number itself must feed into a later calculation; use formatting when you only want a cleaner report.

Check whether the input is stored as text

Imported data can look like numbers while being stored as text. Text values may be left-aligned, show a warning marker, or fail when used in a numeric formula. If ROUND returns #VALUE!, inspect the source cell rather than assuming the rounding function is at fault. Extra spaces, nonbreaking spaces, a leading apostrophe, or a decimal separator that does not match the computer’s regional settings can all interfere with conversion.

With the text value 12.345 in A2, =ROUND(VALUE(A2),2) returns 12.35 if Excel recognizes that text as a number under the current regional settings. VALUE converts recognizable numeric text; it cannot reliably fix arbitrary words or inconsistent separators. If the data uses a decimal comma while Excel expects a decimal point, check the import and regional conventions before converting an entire column.

For a whole imported column, first make a copy or test a few representative rows. Depending on how the file is structured, options include converting the source with VALUE, using the warning menu’s Convert to Number option, or using Data > Text to Columns and completing the conversion. Check dates, identifiers, and values with leading zeros before converting: those may resemble numbers but need to remain text. Do not convert indiscriminately if the column contains mixed data.

Understand negative digits and rounding direction

Negative values in the second argument are valid; they do not mean round downward. With 1,234.5 in A2, =ROUND(A2,-2) returns 1,200 because it rounds to the nearest hundred. With 1,234.5 in A2, =ROUND(A2,-1) returns 1,230. If you wanted a whole number, use zero as the second argument; if you wanted cents, use 2.

Also distinguish ROUND from ROUNDUP and ROUNDDOWN. ROUNDUP moves away from zero, whereas ROUNDDOWN moves toward zero; neither means simply “toward positive infinity” or “toward negative infinity.” With -2.341 in A2, =ROUNDUP(A2,2) returns -2.35, while =ROUNDDOWN(A2,2) returns -2.34. These functions are useful when a rule specifically requires a direction rather than the nearest value.

If a formula needs rounding to a multiple, such as the nearest five cents, ordinary decimal-place rounding is not the same operation. A possible approach is to divide by the increment, round to a whole number, and multiply back. With 12.37 in A2, =ROUND(A2/0.05,0)*0.05 returns 12.35, the nearest 0.05 multiple. Check the required halfway rule for your use case before relying on this pattern.

Check calculation settings and formula errors

If a source value changes but the rounded result does not, the workbook may use manual calculation. On Windows, choose Formulas > Calculation Options > Automatic. Press F9 to recalculate formulas; Ctrl+Alt+F9 forces a fuller recalculation. On Mac, check the Calculation options on the Formulas tab; shortcut availability can differ with the keyboard and Excel version. In Excel for the web, formulas generally recalculate automatically, though workbook settings and complex links can affect what appears.

Look at the actual cell result, not just a warning elsewhere in the workbook. #VALUE! often points to text or an invalid argument. #NAME? can indicate a misspelled function name or a formula entered in a way Excel does not recognize. If a formula displays literally, change the cell from Text to General, then re-enter the formula; changing the format alone may not trigger recalculation of text already entered. Check for a leading apostrophe as well.

Excel installations can use different list separators based on regional settings. The examples here use commas between function arguments. If Excel flags the comma-separated formula as invalid, your installation may require semicolons instead; follow the separator Excel accepts rather than changing the rounding logic. Also check that parentheses are balanced and that the formula has two arguments: the number to round and the number of digits.

Know the limits of decimal precision

Excel stores most numbers using binary floating-point arithmetic, and many decimal fractions cannot be represented exactly in binary. As a result, a calculation involving decimals may contain a tiny internal difference from the decimal value you expect. This is one reason a value that looks like a halfway case can sometimes produce a surprising result when more complicated calculations precede ROUND.

For ordinary worksheet work, use ROUND at the point where your rule requires a rounded value, and inspect the unrounded value in the formula bar when a result is unexpected. Avoid treating a display format as proof that the stored value is exact. Excel’s Set precision as displayed option can permanently discard precision across a workbook, so it is not a casual fix for a rounding discrepancy. Keep a backup before changing workbook-wide precision settings.

For money calculations, decide which step the applicable rule says to round: each line item, each tax calculation, or only the final total can produce different results. For example, three amounts that each round to 0.01 can total differently from rounding their combined unrounded value once. This is a calculation-policy question, not something Excel can decide for you. Verify the required method against the relevant contract, accounting policy, or qualified professional.

Choose the right rounding method

Use the method that matches what the result is supposed to mean:

Use ROUND when you need the nearest value at a chosen decimal place.
Use ROUNDUP or ROUNDDOWN when the rule specifies a direction away from or toward zero.
Use number formatting when you only need fewer digits on screen and should preserve the underlying value.
Use a formula that rounds to a multiple when the target is an increment such as 0.05 or 10, rather than a decimal place.

Before changing a workbook, compare the rounded result with the source value and with a hand-checked example, including a negative value if negatives occur in the data. Then inspect any downstream totals: they may change when you replace display formatting with actual rounding. That check helps identify whether the issue was a formula, a text input, or simply the difference between what Excel stores and what it shows.

Frequently Asked Questions

Why does Excel show more decimal places after ROUND?
Cell formatting controls how many decimals are displayed. Select the result and use Home > Number > Decrease Decimal, or set the decimal places in Format Cells. This changes the display; the ROUND formula controls the returned value.

Why does ROUND return #VALUE!?
Check whether the referenced cell contains text, spaces, or a number written with an unrecognized decimal separator. Convert valid numeric text with VALUE, then confirm the result. If the input contains words or mixed symbols, clean it before conversion.

Does ROUND(A2,2) round to two significant digits?
No. It rounds to two decimal places. For example, 12.345 becomes 12.35. Significant figures depend on the size of the number and require a different calculation.

Why is my rounded total different from the sum of the displayed amounts?
The displayed amounts may only be formatted to fewer decimals, while their underlying values retain additional precision. Alternatively, the workbook may round each line before totaling. Check the formulas and decide whether the rule requires rounding individual amounts or only the final total.

Can I make Excel always round every cell automatically?
Number formatting can make values look rounded, but it does not change their stored values. To use rounded numbers in calculations, include a rounding function in the relevant formulas. Avoid Set precision as displayed unless you deliberately intend to discard precision throughout the workbook.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.