If an Excel sum is wrong, first check that the formula covers the intended cells, the entries are real numbers rather than text, and calculation is set to Automatic. In Microsoft Excel for Microsoft 365 on Windows, select the total cell and inspect its formula; these checks resolve most cases where a total looks incorrect or does not update.
Start by checking the formula and the expected total
Click the cell containing the total and read the formula in the formula bar. A formula such as =SUM(B2:B6) adds the values in cells B2 through B6, including both endpoints. Compare that range with the data you intend to include: a newly added row, a heading inserted within the data, or a total row outside the selected range can change what should be summed.
For example, with amounts in B2:B6 of 12, 18, 7, 23, and 10, the formula =SUM(B2:B6) returns 70. If a sixth amount of 5 is entered in B7, the existing formula still returns 70 because B7 is not in its range. Expand the formula to include B7 if that row belongs in the total.
Also check whether the cell contains a formula at all. A number typed into a cell, a copied value, or a formula that points to a different range can look like a sum while behaving differently. If you are unsure which cells the formula references, select the formula cell and look for the colored outlines Excel displays around referenced cells.
Check whether numbers are stored as text
One of the most common causes is that entries look like numbers but are stored as text. Excel’s SUM function adds numeric values in referenced cells; text entries are generally ignored. This often happens after importing data from a website, a CSV file, or another system, especially when values contain leading apostrophes, extra spaces, or separators that do not match the computer’s regional settings.
Look for clues such as a green triangle in a cell’s corner, an error-checking icon, or numbers aligned differently from nearby numeric entries. Alignment alone is not conclusive because formatting can change it. To check a range, select it and look at the status bar at the bottom of Excel: its numerical summary can help reveal whether Excel recognizes the entries as numbers. You can also count numeric cells. With the suspected values in B2:B6, enter =COUNT(B2:B6) in another cell. If all five entries are genuine numbers, the result is 5; a smaller count suggests that some cells are blank or not numeric.
To convert text numbers, select the affected cells and use the error-checking icon’s Convert to Number option when Excel offers it. Another option is to select the column and choose Data > Text to Columns, then select Finish without changing the wizard’s settings. Check the result before relying on it, especially if the data uses commas or periods as decimal and thousands separators. Simply applying a Number format does not convert text into numeric values.
- Inspect imported values for leading or trailing spaces and apostrophes.
- Check that decimal and thousands separators match the source data.
- After conversion, verify that the total and the numeric count make sense.
Make sure the sum range includes every intended cell
A correct SUM formula can still produce an unexpected result when its range is incomplete or too broad. Check the first and last cell references, then compare them with the actual data. A formula such as =SUM(B2:B20) will not include values in B21 and below. Conversely, a range that includes a subtotal or an unrelated value may count more than you expect.
Pay attention to inserted rows and copied formulas. Excel often adjusts references when rows are inserted, but the result can depend on where the row is inserted and how the formula was created. If the data is a regularly expanding list, converting it to an Excel table can make formulas easier to maintain: select the data and choose Insert > Table. When you add rows directly to the table, its range expands with the data. A table’s Total Row can also calculate a column total; select the total cell and choose Sum from its drop-down list.
When a formula is copied, dollar signs can lock a reference. For example, $B$2:$B$20 is an absolute range and will not shift when the formula is copied elsewhere. That is useful when intentional, but it can be a source of confusion if you expected the range to move. Review the formula bar rather than assuming a copied formula now points to the adjacent data.
Check Excel’s calculation setting
If the inputs or formula have changed but the displayed total has not, Excel may be using manual calculation. On Windows, choose Formulas > Calculation Options > Automatic. In Automatic mode, Excel recalculates formulas when their dependencies change. In Manual mode, a displayed result can remain stale until a recalculation is requested.
On Windows, press F9 to recalculate open workbooks; on some keyboards, you may need to hold Fn as well. Press Shift+F9 to calculate the active worksheet. On Mac, the function-key behavior can depend on keyboard settings, so use the Formulas tab’s calculation controls if the shortcut does not work. In Excel for Mac, calculation preferences are available under Excel > Settings or Excel > Preferences, depending on the interface version.
Manual calculation can be useful in large workbooks with lengthy formulas, but it can make numbers appear wrong if you forget to recalculate. If a workbook was saved while set to manual calculation, check the setting when opening it. After switching to Automatic, give Excel time to finish calculating a large workbook before comparing results.
Separate display formatting from the stored value
Number formatting changes how a value appears, not the underlying value used in a sum. A cell might display 12 because it is formatted to show no decimal places while actually containing 12.49. A total based on the stored value can therefore differ from the sum of the rounded figures you see on screen. Select a cell and increase the displayed decimal places using the Home tab’s number-format controls to inspect the underlying precision.
The reverse can happen too: a total may be mathematically correct but appear wrong because the total cell is formatted as a date, percentage, or text, or because it shows too few decimal places. Set an appropriate number format from Home > Number, and compare the displayed result with the formula bar. Formatting does not repair text numbers or alter a calculation; it only changes presentation.
Excel also stores numbers using binary floating-point arithmetic. Some decimal fractions cannot be represented exactly in binary, so calculations involving decimals can contain tiny precision differences. This is usually noticeable only far beyond the displayed decimal places. If a business rule requires rounding each line before adding, apply rounding deliberately and consistently; do not round just to hide a discrepancy without understanding which values should be rounded.
Understand filtered and hidden rows
The SUM function includes numeric values in its range even when some of those rows are hidden by a filter. That can surprise people who expect a total to change when they filter a list. For a filtered list, SUBTOTAL is often a better choice because it can exclude filtered-out rows.
With values in B2:B100, =SUBTOTAL(9,B2:B100) adds the visible and filtered-out numeric values but excludes rows removed by a filter. Use this when you want the ordinary total regardless of the filter. With the same layout, =SUBTOTAL(109,B2:B100) excludes both filtered-out rows and rows hidden manually. The function number matters: 9 and 109 have different behavior for manually hidden rows. SUBTOTAL also ignores other SUBTOTAL formulas within its reference, which helps prevent double-counting in some layouts.
If the total should always include every row, keep SUM. If it should reflect only what remains visible after filtering, choose the appropriate SUBTOTAL option and test it by filtering a small, known example. Hidden rows and filtered rows are not the same thing, so decide which behavior the report needs before changing the formula.
Look for errors, text results, and circular references
A sum can fail for reasons other than the values in its range. If the formula cell displays the formula itself instead of a result, the cell may be formatted as Text, or Excel’s Show Formulas view may be enabled. Change the cell format to General or Number, then edit and confirm the formula again; changing the format alone may not make an already-entered formula recalculate. Check the Formulas tab for the Show Formulas control if formulas appear throughout the worksheet.
If the result displays an error such as #VALUE! or #REF!, inspect the formula and its referenced cells. A broken reference may result from deleting or moving a referenced cell. An error within the range can also affect the result. A circular reference occurs when a formula depends on its own result, directly or through other formulas; Excel may warn you or show a result that depends on iterative calculation settings. Resolve the reference chain rather than replacing the total with a typed number.
Also confirm that the total cell is not included in its own range. If a total in B10 uses =SUM(B2:B10), the formula includes itself. The intended range may be B2:B9 instead. A circular-reference warning or an unexpected calculation is a reason to inspect the range boundaries carefully.
A practical troubleshooting sequence
Use a consistent order so you can isolate the cause instead of changing several things at once:
- Select the total and read its formula. Confirm that it references the intended cells and does not include itself.
- Check whether the source entries are numeric, especially if the data was imported or pasted.
- Compare the displayed values with their stored precision and verify the total cell’s number format.
- Check whether filters or manually hidden rows should affect the result; choose SUM or SUBTOTAL accordingly.
- Confirm that calculation is Automatic, or recalculate the workbook.
- Look for formula errors, text-formatted formulas, and unexpected references before replacing any result.
For a quick sanity check, add a small known set of values in a separate area and compare the result. If a simple sum works but the original does not, the issue is probably in the original data, range, calculation setting, or formatting rather than in the SUM function itself. Preserve a copy of important data before performing bulk conversions, so you can recover the original values if separators or imported formats were interpreted incorrectly.
Frequently Asked Questions
Why does Excel SUM ignore some numbers?
Those entries may be stored as text, blank, or otherwise not recognized as numeric values. Check imported data, spaces, apostrophes, and regional separators, then convert genuine text numbers and verify the result.
Why does my Excel total not update when I change a value?
The workbook may be set to Manual calculation. On Windows, check Formulas > Calculation Options > Automatic, or recalculate with F9. A formula that references a different range can also make a change appear to have no effect.
Does SUM include filtered-out rows?
Yes. SUM includes numeric values in its range even when a filter hides their rows. Use SUBTOTAL with function number 9 to exclude filtered-out rows, or 109 to exclude filtered-out and manually hidden rows.
Why is my total different from the numbers shown in the cells?
The cells may display rounded values while storing more decimal places. Increase the displayed decimal places and inspect the formula bar. Number formatting affects appearance, not the stored value.
Why does Excel show the formula instead of the sum?
The cell may be formatted as Text, or Show Formulas may be on. Set the cell to General or Number, re-enter the formula, and check the Show Formulas control on the Formulas tab.
Related
- Excel Row Limit: How Many Rows Can a Sheet Hold?
- Excel Date Shortcut: Insert Today’s Date with Ctrl+;
- Data Excel Skills: What They Are and How to Build Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Worksheet Not Scrolling? Fix the Most Likely CausesIf an Excel worksheet will not scroll, first check whether Scroll Lock is on, then look for frozen…
- PMT Formula Not Working? Fix Excel Payment ErrorsIf your Excel PMT formula is not working, first make the interest rate and number of periods match…
- How to Normalize Dates in Excel and Fix Inconsistent FormatsTo normalize dates in Excel, first convert any text dates into real Excel date values, then apply one…
- TRIM Excel Not Working? Fix Spaces That TRIM MissesIf TRIM appears not to work in Excel, the text may contain a character that TRIM does not…
- Excel Formula Not Calculating? Fix Zero ResultsIf an Excel formula shows zero, first check Formulas > Calculation Options > Automatic, then confirm the formula…
- Excel Protect Sheet Not Working? Causes and FixesIf Excel sheet protection seems not to work, first check that the sheet is actually protected and that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.