When Excel rounds numbers you never asked it to round, it is almost always rounding only the display, and the fix is to give the display room: widen the column, click Home > Number > Increase Decimal, or set more decimal places in Format Cells. The stored value is intact: select the cell and the formula bar shows it in full, and calculations run on what is stored, not on what is shown. The grid renders a shortened figure when the cell’s number format shows fewer decimal places than the value carries, or when the column is too narrow for the whole number.
The exceptions are a formula that rounds on purpose, one workbook option covered below, and entries longer than Excel’s 15-digit limit, also covered below. If you came here wanting to round deliberately, that is the other page: the ROUND function guide covers ROUND, ROUNDUP, ROUNDDOWN, and their relatives.
Why Excel rounds numbers you never asked it to round
Microsoft’s support page on the problem opens with the distinction that explains nearly every case: “In Excel, the number that appears in a cell is separate from the number that is stored in the cell.” Microsoft’s example is a value with seven decimal places in a cell formatted to show two: the cell displays a rounded figure, either because of that format or because the column cannot fit the digits, while the stored number is untouched and is what Excel calculates with.
| A | B | |
|---|---|---|
| 1 | Item | Rate |
| 2 | Fee | 5.85 |
Three display mechanisms account for nearly all of it:
- Number format. The format shows fewer decimals than the value has: per Microsoft, applying any of the five fixed formats (Number, Currency, Accounting, Percentage, Scientific) starts you at a default of two decimal places. That shared default is why currency and accounting cells appear to round everything to two decimal places out of the box: a cell holding 4.257 shows 4.26 the moment the format lands on it.
- Column width. General, the default format of every cell on a new worksheet, fits the number to the cell: Excel shows fewer decimal places so the value fits within the column’s width. Narrow the column further and more decimals disappear from the display; none of them leaves the cell.
- Twelve or more digits. Under General, very large numbers switch to scientific notation. That one has its own section below, because one version of it is not cosmetic.
In every display case the formula bar is the tell. It shows the stored value, the one a formula in Excel reading that cell would receive, no matter what the grid is rendering.
How to stop Excel from rounding: the display fixes
Microsoft’s rounding overview files the decimal-place fixes below under display changes: the decimal places you see move, the number underneath does not. These are display repairs for a display problem.
1. Check the formula bar
Select the cell. If the formula bar shows the full value, proceed with the fixes below. If the formula bar shows the rounded value too, the stored number really was rounded: look for a ROUND family function in the formula, covered in the ROUND function guide, or the workbook option in the last section.
2. Widen the column
Double-click the boundary to the right of the column heading and the column resizes to fit its contents; drag the boundary instead for a custom width. Excel for Mac also documents a menu route: Format > Column > AutoFit Selection. Widening helps because General shows as many decimals as fit. If the cell shows ######## instead of digits, the column cannot fit the number at all at its current width, and Microsoft’s fix is the same double-click on the column header’s right border.
3. Click Increase Decimal
Select the cells, then click Home > Number > Increase Decimal, once per decimal place, until the places you need are visible. On a General formatted cell, Microsoft notes the format switches from General to Number as you click, with the column width adjusting on its own, so this one step handles both the format cause and the width cause.
4. Set the decimal places in Format Cells
For a lot of numbers at once, set the format explicitly. In Excel for Windows: on the Home tab, click the dialog box launcher next to Number, pick a category that has decimal places (Number, Currency, Accounting, Percentage, or Scientific), enter the count in the Decimal places box, and click OK. In Excel for the web the same dialog is behind Number Format > More Number Formats; in Excel for Mac it is Format > Cells, on the Number tab.
Long numbers: scientific notation and the 15-digit limit
A 12-digit account number in a General cell shows up in 1.23E+11 style. That is still display rounding: apply a different number format, such as Number, and the digits return, unchanged. The destructive case is longer entries: Excel keeps 15 significant digits, so a 16-digit code loses its trailing digits at entry, and no format change brings them back. Both stories, including why such codes belong in Text formatted cells before you type them, are covered in full in numbers stored as text in Excel.
Why the cells do not add up to the displayed total
The classic complaint: a column shows 8.12, 9.23, and 4.35, the total underneath shows 21.71, and anyone with a calculator makes it 21.70. The total is off by a penny, someone rechecks the SUM, and the SUM is right.
| A | B | |
|---|---|---|
| 1 | Line item | Amount |
| 2 | Materials | 8.12 |
| 3 | Labor | 9.23 |
| 4 | Freight | 4.35 |
| 5 | Total | 21.71 |
Each cell stores more decimals than it shows, SUM adds the stored values, and the rounded renderings do not have to add up to the rounded total. Nothing is miscalculated; the display is reconciling a different sum than the reader is.
There are two honest ways out, and they are different decisions:
- Show the precision that exists. Increase the decimal places on the column and the total, as above, and the figures reconcile because nothing is hidden anymore.
- Store the precision you show. Apply ROUND to the data so each cell genuinely holds 8.12, and the displayed arithmetic becomes the real arithmetic. When and how to do that is the ROUND guide’s subject.
Do not ship a sheet that fails this test. Readers notice, and they assume the workbook is wrong, not that the rounding is cosmetic.
Set precision as displayed: the one setting that really rounds
Everything above changes rendering. One workbook option changes the numbers. In Excel for Windows, under File > Options > Advanced, in the When calculating this workbook section, sits the Set precision as displayed check box. Microsoft’s description: “This option forces the value of each number in the worksheet to be at the precision that is displayed on the worksheet.”
Microsoft documents it as a way that can often keep floating point rounding artifacts out of results when applied before formatting the data. Its note on the option warns that the effect can compound and leave data increasingly inaccurate over time, and it advises turning the option on only when you are certain the displayed precision preserves your data’s accuracy. Treat that warning literally. Once the option has run, the trimmed decimals are no longer stored anywhere in the workbook, so unticking the box or adding decimal places back to the format does not restore them.
If the goal is the penny problem above, the same page’s own tip points at a safer tool: ROUND, applied to the specific cells that need it, instead of a switch that rewrites every number in the worksheet to whatever its format happens to show.
The short version
- Excel’s unasked-for rounding is almost always display rounding: what a cell shows and what it stores are two different things, and calculations use the stored one. The formula bar shows the truth.
- Fix the display with room: widen the column, click Home > Number > Increase Decimal, or set the Decimal places in Format Cells. None of these changes a value.
- Currency, Accounting, and the other fixed formats show two decimal places by default; General fits decimals to the column width; 12-plus-digit numbers go scientific under General. All cosmetic; the 15-digit limit is the one that destroys data.
- Columns that do not add up to their own total are displaying rounded figures over unrounded values: show more decimals, or ROUND the data, per the ROUND guide.
- Set precision as displayed (File > Options > Advanced, Windows) is the exception that really rounds stored values; Microsoft warns its effects accumulate, and the trimmed decimals do not come back.
- Display rounding also hides edits. Change a stored 5.004 to 5.001 under a two-decimal format and the cell shows 5.00 before and after, so reading the sheet reveals nothing while every downstream total moves. When a workbook comes back edited, compare the two versions: the comparison reads stored values, not rendered figures, and flags a change the display masks as its own finding.
