Totals look wrong but Excel shows no error: stale calculation, text numbers and displayed versus stored values

Three quiet causes of a wrong total

A total can be wrong without any message because the workbook is not recalculating, because some entries are text that looks like numbers, or because what is displayed is not what is stored. Each has a quick test, and doing them in this order costs a few minutes.

• Calculation set to manual, so formulas show old results.
• Numbers stored as text, so sums skip them and sorting is odd.
• Display formatting that hides more decimals than the sum uses.

Check the calculation mode first

Excel recalculates automatically by default. In Manual mode, Microsoft's page explains, formulas update only when you recalculate, for example with F9, and Ctrl+Alt+F9 recalculates every formula in all open workbooks whether or not it changed. Write down a total, force a full recalculation, and look again. If the number moves, the workbook was stale and you have found at least one cause.

Check this before anything else, because every later check is meaningless on stale results.

Numbers stored as text

Microsoft describes numbers stored as text: they align left, often carry a small green triangle and cause calculation problems and confusing sort orders. They often arrive from imports, copies from other systems or cells that were formatted as text before a number was typed. A count of the column that is lower than the number of filled cells is a quick test.

The page lists the fixes: Convert to Number from the error button, or Paste Special with Multiply by 1 for cells without a marker. Ignore Error only hides the marker and leaves the data unchanged. Extra spaces and nonprintable characters need TRIM and CLEAN. Some systems export a negative number with the minus sign after the value, which also arrives as text.

Codes are different from amounts. Excel removes leading zeros from numbers and keeps 15 significant digits, so a postcode, account code or long reference number must be text from the start. Setting the column to Text, or the data type to Text when importing, keeps it.

Displayed is not stored

A number format changes what you see, not the value underneath. Three amounts that each display as 1.30 can hold more decimals, and their sum can differ from the sum of the displayed figures. Excel also follows the IEEE 754 standard, so some decimals such as 0.1 cannot be stored exactly. Microsoft's own example, =(43.1-43.2)+1, displays 0.899999999999999 when shown to fifteen decimal places.

The sensible remedy is the ROUND function, applied where the business rule says rounding happens. The workbook-wide option "Set precision as displayed" is risky: Microsoft says it permanently trims stored values, cannot be undone and can have cumulative effects that make data increasingly inaccurate. A penny difference between two quotes is usually a rounding-rule question, and the accompanying worked example shows one.

When it is a repair job, and when it is not

If a workbook's formulas, text numbers or calculation setting are making outputs wrong, the fixed £195 formula repair covers one workbook of up to five sheets and eight outputs, accepted by check cases you calculated independently and a full recalculation that moves nothing. The price is an untested proposal and payment follows your sign-off.

It does not decide your rounding policy, correct figures typed in by people, or give accounting advice. Say in the enquiry how many outputs you do not trust and whether macros are involved, and send no workbook at first.