When Excel’s total doesn’t match the numbers you can see
Your calculator and Excel disagree on a total, even though every entry shows two decimal places. Can you make the report add up without losing the original values?
A calculator can disagree with Excel because the spreadsheet is still adding decimal digits that the report does not show. If each displayed entry is a finalized amount, round each row and then add those results. If the report requires the total of the original values, add them first and round once at the end. The two totals can differ; changing the total cell’s number format does not change that order.
This is a practical problem beyond currency tables. One Microsoft Q&A user reported a total that was a penny out; another asked why displayed scores added to 242 while the total showed 241. Neither symptom alone identifies the cause in your workbook. Start with its actual values and formulas. Currency total question, Rounded scores question
Reveal the decimals behind the display
Enter 1.125 and format the cell to two decimal places: it displays 1.13. With the default precision settings, a formula referencing that cell still uses 1.125. Formatting controls appearance; Excel normally calculates with stored values. Excel’s calculation precision
Work in a copy of the file. Select the entries and their total, then use Home → Increase Decimal to reveal additional digits. You can also specify more decimal places in the Number format. Widen a column if the result becomes a row of hash marks. Compare the entries and total at the same displayed precision. Showing more decimal places
The formula bar helps with directly entered numbers. For a formula cell, however, it shows the formula rather than simply revealing the calculated result. Increase that cell’s displayed decimals and inspect the formula separately. Check whether it already contains ROUND, and whether the total includes the intended first and last rows. Rounding cannot repair an omitted entry.
How the same entries produce 6.38 and 6.39
For a small worked example, put 1.125, 2.125 and 3.125 in A2:A4. Displayed to two decimal places, they become 1.13, 2.13 and 3.13. Adding those displayed numbers gives 6.39. The original entries sum to 6.375, which rounds to 6.38.
| What is being added? | Calculation | Result |
|---|---|---|
| Original entries | 1.125 + 2.125 + 3.125 | 6.375 |
| Entries rounded individually | 1.13 + 2.13 + 3.13 | 6.39 |
| Original total, rounded once | Round 6.375 to two decimal places | 6.38 |
In =ROUND(SUM(A2:A4),2), SUM adds the original entries before ROUND handles the result. By contrast, =ROUND(A2,2) applied to each row creates individually rounded numbers that can then be added. The second argument, 2, specifies two decimal places. ROUND syntax and behavior
The discrepancy here follows from the order of operations. It does not require a floating-point storage error to explain it. Each row rounds upward before addition in one calculation; only the combined amount is rounded in the other.
Nor will every workbook differ by exactly 0.01. The number of entries and their fractional parts determine the difference. Subtracting a penny from the last row might make today’s display agree, but it changes that entry and provides no valid rule for next month.
Build a total without replacing the source numbers
If each reported entry must be finalized to two decimal places, keep column A as the source and calculate the rounded entries in B. This makes the transformation visible and leaves the original numbers available for other calculations.
- Label A1 “Original value” and B1 “Rounded entry.” Keep the three original numbers in A2:A4.
- Enter
=ROUND(A2,2)in B2 and fill down to B4. Check that B3 references A3 and B4 references A4. - Enter
=SUM(B2:B4)in B5. Format B2:B5 to two decimal places. The entries display 1.13, 2.13 and 3.13; the total displays 6.39. - In D2, enter
=ROUND(SUM(A2:A4),2)and label it “Original total, rounded.” Its result is 6.38. - In D3, enter
=ROUND(B5-D2,2)and label it “Difference due to rounding order.” The result in this example is 0.01.
D3 uses two places because this example compares two results reported at that precision. Do not copy that choice into measurements requiring four decimal places: it could conceal a difference you need to retain.
For a larger table, fill the helper formula through the last data row and connect the report total to the helper column. Creating column B changes nothing if the published total still sums A. A label such as “Sum of individually rounded entries” also explains why it may differ from the source total.
Use A for further analysis that requires the original precision. Use B when the analysis specifically concerns the finalized entries. Replacing A with B’s results removes that choice and makes later reconciliation harder.
Choose the reporting unit and rounding stage
For a report requiring whole units, use 0 as ROUND’s second argument; use 2 when the reporting currency requires two decimal places. If the report is expressed in thousands, convert the amounts to that unit before rounding. Its calculation rules also determine whether to finalize each entry or round only the combined total.
Consider two hypothetical amounts of 12,400, reported in thousands with no decimal places. Individually, each becomes 12, so the displayed entries add to 24. Their original total is 24,800; converted to thousands and rounded once, it becomes 25.
| Required result | Calculation from original amounts in A | Example |
|---|---|---|
| Sum of finalized entries in thousands | Apply ROUND(A2/1000,0) to each row, then sum the results | 12 + 12 = 24 |
| Original total expressed in thousands | ROUND(SUM(A2:A3)/1000,0) | 25 |
Choose 24 only when the report calls for the sum of those finalized entries. Choose 25 when it calls for the original total expressed at the reporting precision. In the latter case, a rounding note can explain why displayed components do not add exactly to the total. Showing more decimals is another option if the report format allows it.
The same issue occurs before multiplication. A unit value of 1.125 multiplied by a quantity of eight gives 9. Round that unit value to 1.13 first, then multiply, and the result is 9.04. Establish whether rounding applies to the unit value or the extended line amount before rewriting the formula.
What Set precision as displayed changes
In Windows desktop Excel, File → Options → Advanced → When calculating this workbook contains Set precision as displayed. It changes calculation precision for the workbook; it is not a ROUND formula attached only to the total. Microsoft cautions that repeated calculations under this setting can become less accurate. Displayed precision settings
Stored digits lost through this option do not return merely because you turn it off. Rates or unit values on other sheets can also change if their formats display fewer digits. Leave this option off, keep the original values in A, and calculate the rounded report amounts in B. Those source numbers will remain available for later calculations.
If the setting has already been used, preserve the current file and compare it with an earlier version or the original input data. Adding decimal places to the display cannot reconstruct discarded digits. Trailing zeros are not proof that the original input had no fractional part.
If the total still doesn’t reconcile
Check whether a formula such as =TEXT(A2,"0.00") created the apparent numbers. TEXT returns text, whereas ROUND produces a numeric result suitable for the helper column. Keep TEXT for labels or formatted text output. TEXT’s return type
SUM ignores text in referenced cells. A column of TEXT results therefore will not behave like the numeric helper column above. Also check that newly added rows fall inside the sum range: additions outside the referenced range are not guaranteed to be included automatically. SUM and referenced ranges
Extremely small residuals are a separate possibility. Excel uses binary floating-point arithmetic, which cannot represent every decimal fraction exactly. That limitation should not be used as a blanket explanation for mismatches caused by different rounding stages. Floating-point calculation limits
Before submitting, compare the original total, the helper-column total and the report’s linked total side by side. Check that every row uses the same agreed rule and that units are labeled. A report of finalized entries should match their sum. A report of the original total should match that total after the required unit conversion and final rounding.