Round Excel Cell Values: Formulas and Display Options

To round a number in an Excel cell and change the value used in calculations, use the ROUND function: with the number in A2, enter =ROUND(A2,2) in another cell to round it to two decimal places. To change only how many decimals are shown, use the decimal-place buttons on the Home tab; that leaves the stored value unchanged.

What rounding a cell value means

Excel can display a number differently from the number it stores. For instance, a cell might show 12.35 while its underlying value is 12.345. Applying a number format can create that appearance without changing the underlying value. A formula such as ROUND, by contrast, returns a rounded result that other formulas can use.

This difference matters when a workbook calculates totals, invoices, percentages, or measurements. If a displayed value is 12.35 but its stored value is 12.345, a total may use 12.345 unless the calculation explicitly rounds it. Conversely, rounding a value with a formula changes the result available to later calculations, though the original value remains in its source cell unless you replace it.

So first decide what you mean by “round the cell”: do you want a cleaner display, or do you want calculations to use a rounded number? Formatting is usually appropriate for presentation. A rounding formula is appropriate when the rounded value is part of the calculation rules.

Choose between rounding and formatting

Use number formatting when readers need to see fewer decimals but calculations should retain the original precision. Use ROUND or a related function when the calculation itself must use a rounded result. For example, a measurement report may show two decimal places while retaining more precise source measurements; a financial process may instead require rounding each item before adding the items.

Format the cell: changes the visible number of decimal places, not its stored value.
Use a formula: returns a rounded value in the formula cell and leaves the source cell alone.
Replace the original values: calculate rounded results, then copy and paste values over the original range if you have confirmed that losing the extra precision is intended.

Formatting is reversible and is normally the safer first step if the goal is simply a tidier worksheet. Replacing source data is a more consequential action: keep an untouched copy or save a separate workbook before doing it.

Use ROUND for ordinary rounding

ROUND rounds to a chosen number of decimal places. The second argument tells Excel how many places to keep: a positive number means places to the right of the decimal point, zero means the nearest integer, and a negative number means rounding to the left of the decimal point.

With the original number 18.376 in A2, enter =ROUND(A2,2) in B2; the result is 18.38. With 18.376 in A2, =ROUND(A2,1) in B2 returns 18.4. With 18.376 in A2, =ROUND(A2,0) in B2 returns 18. And with 1,234 in A2, =ROUND(A2,-2) in B2 returns 1,200.

In the usual halfway case, ROUND rounds away from zero. With 1.25 in A2, =ROUND(A2,1) in B2 returns 1.3; with -1.25 in A2, the same formula returns -1.3. This is a useful distinction from “round half to even,” a rule used in some other settings. If a policy, contract, or external system specifies a particular rounding convention, check that Excel’s function matches it before relying on the results.

Round up, down, or to a multiple

Excel has other rounding functions for cases where the ordinary nearest-value rule is not what you need. ROUNDUP moves away from zero, while ROUNDDOWN moves toward zero. These functions do not mean “toward positive infinity” and “toward negative infinity.” For example, with -2.1 in A2, =ROUNDUP(A2,0) in B2 returns -3, while =ROUNDDOWN(A2,0) returns -2.

To round to a specified increment, use MROUND. With 17.63 in A2, =MROUND(A2,0.25) in B2 returns 17.75, the nearest multiple of 0.25. With 12.37 in A2, =MROUND(A2,0.05) in B2 returns 12.35, the nearest five-cent increment. The number and the multiple should have compatible signs; MROUND returns an error when they have opposite signs.

For whole-number operations, INT and TRUNC are not interchangeable in every case. INT rounds down toward negative infinity, while TRUNC removes the fractional part toward zero. With -2.3 in A2, =INT(A2) in B2 returns -3, whereas =TRUNC(A2) in B2 returns -2. If the requirement is ordinary rounding to the nearest integer, use ROUND with zero decimal places instead.

Round a range with a formula

For a list, put the formula beside the first value and fill it down. Suppose A2:A20 contains measurements and you want two-decimal results in B2:B20. Enter =ROUND(A2,2) in B2, then copy or drag the fill handle down through B20. Excel adjusts the reference in each row: the formula in B3 refers to A3, and so on.

To use the results in place of the original values, check the rounded column first. Then copy B2:B20 and choose Home > Paste > Paste Special > Values over A2:A20. The values are replaced, rather than the formulas being copied back. This operation is difficult to reverse after you close or continue working in the workbook, so save a backup before replacing source data. If you want to preserve the original precision, keep the rounded results in their own column.

For a total, be clear about where rounding belongs. With item amounts in A2:A10, =SUM(A2:A10) adds the stored amounts. If each item must first be rounded to two decimal places, a separate helper column makes that rule visible: put =ROUND(A2,2) in B2 and fill down, then total B2:B10 with =SUM(B2:B10). The rounded-item total can differ from a total calculated first and rounded afterward. Which result is correct depends on the applicable accounting, tax, pricing, or reporting rule; Excel cannot decide that policy for you.

Change displayed decimal places

To show fewer decimal places without changing values, select the cells and use Home > Number > Decrease Decimal. Each click reduces the displayed decimal places. Use Increase Decimal in the same group to show more again. The buttons affect the selected cells’ number format, not the formula or stored number.

You can set a specific number of displayed places through Format Cells. Select the cells, open the dialog with Ctrl+1 on Windows, choose the Number tab, select Number or another suitable category, and set Decimal places. On a Mac, Command+1 opens Format Cells; ribbon labels and dialog details can vary somewhat by Excel version. In Excel for the web, select the cells and use the Home tab’s number-format controls; some desktop dialog options may not be available in the same form.

Formatting also lets you control details beyond decimal places, such as commas, currency symbols, and percentages. A percentage format changes how a stored fraction is presented: for example, a stored value of 0.125 can display as 12.5%. If you need a rounded percentage for a later calculation, use ROUND on the underlying value at the appropriate precision rather than assuming that the displayed percentage has changed.

Common rounding problems and misconceptions

A frequent surprise is that a total does not equal the sum of the rounded figures shown on screen. This happens when the cells are formatted to hide decimals but the total uses the more precise stored values. If the required total is the sum of rounded line items, round each line in a formula or helper column before summing. If the requirement is to sum precise values and round only the final total, round the total instead.

Another source of confusion is floating-point precision. Excel stores many decimal fractions in binary form, where some fractions cannot be represented exactly. Calculations may therefore produce tiny differences beyond the digits displayed. For ordinary worksheet work, use a suitable number format for presentation and apply ROUND at the point where a defined calculation rule requires it. Avoid treating a display format as a guarantee that the underlying value has been rounded.

Excel also offers a workbook calculation setting called Set precision as displayed under File > Options > Advanced, in the calculation options for the workbook. It changes stored values to match displayed precision and can permanently discard digits. It can affect more than the selected cells, so it is not a substitute for a carefully designed formula. Save a separate copy before changing it, and use it only when you understand the consequences for the entire workbook.

Frequently Asked Questions

How do I round a number in Excel to two decimal places?
With the value in A2, enter =ROUND(A2,2) in another cell. The formula returns a number rounded to two decimal places; it does not alter A2.

How do I round Excel cells without changing the underlying values?
Select the cells and use Home > Number > Decrease Decimal, or set Decimal places in Format Cells. These options change what is displayed, not the stored values used in calculations.

How do I round a whole column in Excel?
Enter a ROUND formula next to the first value, fill it down beside the rest of the data, and inspect the results. If you intend to replace the originals, copy the results and paste them as values, after saving a backup.

Why does my Excel total differ from the rounded numbers shown?
The cells may only be formatted to display fewer decimals, while the total still uses their more precise stored values. Round each item before summing if the rule requires a total of rounded items.

How do I round to the nearest five cents?
Use MROUND with a multiple of 0.05. With 12.37 in A2, =MROUND(A2,0.05) in another cell returns 12.35. Confirm that this increment and rounding rule fit the requirements for your calculation.

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.