Excel does not normally change a number just because it displays fewer decimal places: formatting rounds the appearance, while the stored value remains available for calculations. To change the value itself, use a rounding formula such as =ROUND(A2,2), with the number in A2; this returns a value rounded to two decimal places.
Display rounding versus changing a value
The phrase auto round off can describe two different things in Excel. The first is display rounding: a cell contains a more precise number than the one shown on the screen. The second is calculation rounding: a formula creates a new number with fewer decimal places. Keeping these separate is important when totals, prices, percentages, or measurements must be consistent.
For example, suppose A2 contains 12.345 and its number format shows two decimal places. The cell appears as 12.35, but the underlying value is still 12.345. You can see more of the value by selecting the cell and looking at the formula bar, or by increasing the displayed decimal places. Formatting alone does not make the value 12.35.
By contrast, a formula that rounds A2 to two decimal places returns 12.35 as its result. That rounded result can then be used by later calculations. If a workbook seems to be rounding by itself, first check the cell format and then inspect the formulas that produce the value.
How to change the displayed decimal places
In Excel for Microsoft 365 on Windows, select the cells and use Home > Number > Increase Decimal or Decrease Decimal. These buttons change how many decimal places are displayed; they do not normally rewrite the stored numbers. You can also press Ctrl+1 to open Format Cells, choose the Number tab, select a category such as Number or Currency, and set Decimal places. On a Mac, Command+1 opens Format Cells; the decimal controls are on the Home tab as well, though the ribbon layout can vary by release.
For a quick format, choose a number format from the Number Format list in the Home tab’s Number group. Number and Currency formats let you specify decimal places, while Percentage displays a value as a percentage. For example, a stored value of 0.25 displayed as Percentage appears as 25%. The format changes the presentation, not the underlying value.
- Use Increase Decimal when you want to inspect more precision on the sheet.
- Use Decrease Decimal when you want a cleaner display, but remember that calculations still use the stored value.
- Use Format Cells when you need a particular number of decimal places, a thousands separator, or a specific currency style.
If a cell shows hash marks instead of a number, the column may be too narrow for the selected format. Widen the column or choose a shorter format; hash marks do not mean Excel has rounded the value to zero.
How Excel’s rounding formulas work
Use ROUND when you want a formula to return a value rounded to a chosen number of decimal places. Assume A2 contains 12.345. The formula =ROUND(A2,2) returns 12.35. The second argument specifies decimal places: 2 means hundredths, 0 means the nearest whole number, and -1 means the nearest ten.
Excel’s ROUND function rounds a midpoint away from zero. For example, when A2 contains 12.345, =ROUND(A2,2) returns 12.35. When A2 contains -12.345, =ROUND(A2,2) returns -12.35. This is different from some rounding methods that round midpoint values to the nearest even digit.
Other functions are useful when the direction matters. Assume A2 contains 12.341. =ROUNDUP(A2,2) returns 12.35, rounding away from zero. With the same value, =ROUNDDOWN(A2,2) returns 12.34, rounding toward zero. These functions describe direction, not whether the result is numerically larger or smaller for every negative number.
To round to a multiple rather than a decimal place, use MROUND. With 12.33 in A2, =MROUND(A2,0.05) returns 12.35, the nearest multiple of five cents. The two arguments must have the same sign; otherwise MROUND returns an error. For a value that needs to be cut off at a certain number of decimal places without rounding, TRUNC is the relevant function. With 12.349 in A2, =TRUNC(A2,2) returns 12.34.
Do not confuse TRUNC with INT for negative values. INT rounds down toward the next lower integer, whereas TRUNC removes the fractional part toward zero. If A2 contains -12.3, =INT(A2) returns -13, but =TRUNC(A2) returns -12. This distinction matters when formulas handle negative quantities or adjustments.
What happens to totals and other calculations?
A common surprise occurs when individually displayed figures do not add up to the displayed total. Suppose A2 and A3 each contain 1.234 and both cells are formatted to show two decimal places. Each appears as 1.23. If A4 contains the formula =SUM(A2:A3), the result is 2.468 and, with the same two-place display format, appears as 2.47. Adding the visible figures gives 2.46, but Excel calculated the total from the more precise stored values.
This is not automatically an error. It may be preferable to keep precision in the source values and round only the final total. But if a process requires every line item to be rounded before it is added, format alone is not enough. The calculation must explicitly round each item, for example by using a helper column with a ROUND formula and summing that column. Decide which rule matches the relevant accounting, pricing, measurement, or reporting requirements before changing the workbook.
For a small example, if A2:A3 contain 1.234 and 1.234, respectively, then =ROUND(A2,2)+ROUND(A3,2) returns 2.46. In contrast, =ROUND(SUM(A2:A3),2) returns 2.47. Both formulas are valid; they answer different questions. The first rounds each item before adding, while the second adds the original values and rounds only the final result.
Can Excel permanently round numbers automatically?
Excel has a workbook calculation option called Set precision as displayed. On Windows desktop, find it under File > Options > Advanced, in the section labeled When calculating this workbook. When enabled, Excel changes stored numeric values to the precision shown by their cell formats. This is not just a visual adjustment: information beyond the displayed precision can be discarded, and changing the format later will not restore it.
Use this setting cautiously, if at all. Before enabling it, save a separate copy of the workbook and check whether formulas, source data, and future reports depend on greater precision. It applies to the workbook’s calculations and can affect many cells, not just the currently selected range. On Mac, the Windows File > Options path does not apply; Excel’s settings interface differs by release. Excel for the web also has a different interface from the Windows desktop instructions.
For most workbooks, an explicit formula is easier to inspect and control than a workbook-wide precision setting. It makes the rounding rule visible in the cell formula, and it allows one column or calculation to be rounded without silently changing unrelated inputs.
Why a value can look slightly different than expected
Excel stores numbers using binary floating-point representation. Some decimal fractions cannot be represented exactly in binary, so a calculation can internally produce a value extremely close to the expected decimal rather than an exact match. A cell’s number format may hide that tiny difference, while formulas that test exact equality can reveal it.
For example, a formula that compares two calculated decimal results with an exact equals sign may return FALSE even when the displayed values look identical. If the task is to compare values to a chosen precision, round the values to that precision or compare whether their difference is within an appropriate tolerance. Choose the tolerance based on the units and purpose of the data; there is no single suitable tolerance for every workbook.
Excel also limits numeric precision to 15 significant digits. This is different from formatting a value to two decimal places. If a calculation or imported identifier needs more than 15 significant digits, Excel may not preserve all of them as a numeric value. Long account numbers, product codes, and similar identifiers are usually better stored as text, because they are labels rather than quantities to calculate.
How to choose the right approach
Before changing a workbook, identify what the result is supposed to mean. Display formatting is appropriate when the underlying precision should remain available but the sheet needs to be easier to read. A rounding formula is appropriate when the rounded result itself must feed into later calculations. A workbook-wide precision setting is a broad, potentially destructive choice and is rarely the first fix for a single cell.
- For presentation: change the number format or decimal-place buttons.
- For rounding to a specified decimal place: use ROUND, ROUNDUP, or ROUNDDOWN according to the required rule.
- For rounding to a cash or measurement increment: consider MROUND, after confirming the required multiple and sign behavior.
- For truncating rather than rounding: use TRUNC, and check how negative values should behave.
- For calculations using visible rounded line items: round each item in the calculation, rather than only formatting the cells.
When a workbook is shared, document the chosen rule in a column heading, note, or instructions. A reader should be able to tell whether displayed figures are merely formatted or have been rounded before calculation. That small clarification can prevent disagreements when someone checks a total using the visible rows.
Frequently Asked Questions
Does Excel automatically round numbers?
Excel may display fewer decimal places than a cell contains, but ordinary number formatting does not normally change the stored value. Use a rounding formula or the Set precision as displayed option if you specifically need to change stored precision; treat the latter with care.
How do I stop Excel from rounding a number?
Select the cells and use Home > Number > Increase Decimal, or open Format Cells with Ctrl+1 on Windows or Command+1 on Mac and set more decimal places. This reveals more of the stored value. If a formula already uses ROUND or another rounding function, changing the display format will not undo that formula’s result.
Why does my Excel total differ from the visible numbers?
The source cells may contain extra decimal places hidden by formatting, so Excel adds the more precise values. If the required method is to add rounded line items, use formulas that round each item before summing; if only the final result should be rounded, round the sum instead.
What is the difference between ROUND and ROUNDUP?
ROUND uses the nearest value at the requested decimal place, with midpoint values rounded away from zero. ROUNDUP always moves away from zero at that place, while ROUNDDOWN moves toward zero. For negative values, away from zero means more negative and toward zero means less negative.
Can I make Excel round every cell permanently?
Set precision as displayed can change stored values to match their displayed precision, but it can discard digits and is not a simple undoable display choice. Save a separate copy first and use it only when that permanent change is intentional.
Related
- Excel Array Length: Count Elements, Rows, and Columns
- Excel Column Length: Rows, Width, and Limits
- Excel Middle Value Extraction: Find the Median
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Data Analysis Books: How to Choose the Right OneGood Excel data-analysis books include Microsoft Excel Data Analysis and Business Modeling by Wayne L. Winston for business…
- Excel INDEX Function Examples for Everyday LookupsExcel’s INDEX function returns the value at a specified row and column within a range. For example, with…
- SUMPRODUCT in Excel: How Its Array Calculations WorkIn Excel, SUMPRODUCT multiplies corresponding values in one or more arrays and adds the resulting products. It is…
- Sort in Excel and Move Entire Rows Without Splitting DataTo sort in Excel and move entire rows together, select the full data range, then choose Data >…
- Excel NPV Formula for Monthly Cash Flows, ExplainedFor equally spaced monthly cash flows in Excel, use the NPV function with a monthly discount rate, then…
- Excel MID String: How to Extract Text by Position in CellsIn Excel, use the MID function to extract a specified number of characters from the middle of a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.