Excel Not Adding Numbers? Fix Text, Formulas, and Totals

Errors & fixes · Updated November 18, 2026

If Excel is not adding numbers, first check whether the entries are stored as text; the SUM function ignores text in a referenced range. Select the cells and look for a warning icon or the number format, then convert text values to numbers. If the values are genuine numbers, check that calculation is set to Automatic and that the formula includes the cells you expect.

Why Excel may not add numbers

A worksheet can display something that looks like a number without storing it as a numeric value. This often happens when data is copied from a website, imported from a text or CSV file, or entered with an apostrophe at the beginning. A leading apostrophe is not displayed in the cell, but it tells Excel to treat the entry as text. Spaces or nonbreaking spaces copied with a value can also prevent Excel from recognizing it as a number.

Other causes are less obvious. A formula may point to the wrong range, calculation may be set to Manual, or the result may be displayed with rounding that hides a small difference. Sometimes the arithmetic is correct but the chosen operation does not match the goal: SUM, for example, adds the values in its specified range, while a filtered list or a set of cells outside that range may require a different selection.

Start by distinguishing two situations: does the formula return an unexpected value, or does it not update when you change an input? The first often points to text, a range, or formatting; the second often points to calculation settings or a formula error.

Run a quick check before changing the data

Click a cell that should contain a number and inspect the formula bar and the Home tab. A green triangle in the cell’s upper-left corner can indicate a number stored as text, though Excel uses that indicator for other potential errors too. A number aligned to the left can be a clue, but alignment alone is not proof because cell formatting can change alignment.

Next, try a simple addition in an empty cell using two of the cells you believe contain numbers. If the result is an error, or if a SUM formula returns less than expected, investigate the input values. If a direct addition works but the total does not, check the SUM range. If a formula remains unchanged after you edit a referenced cell, check calculation mode.

It can help to test one suspected value at a time rather than convert an entire column immediately. This is especially important in mixed data, such as product codes that contain digits but are meant to remain text. A code like 00127 may need its leading zeros preserved; turning it into a number changes its meaning and appearance.

Convert numbers stored as text

When Excel flags a text-formatted number, select the affected cells, click the warning icon, and choose Convert to Number. The warning menu appears only when Excel identifies a relevant issue. If there is no warning, use one of the other conversion methods below. Menu wording or availability can vary slightly by Excel build and by the type of imported data.

For a column of consistently structured values, Text to Columns is a useful conversion tool even when the data is already in one column:

  1. Select the cells that contain the values, not the whole worksheet.
  2. On the Data tab, select Text to Columns.
  3. Choose Delimited and select Next, then leave delimiters unchecked unless the data actually needs splitting.
  4. Select Next, confirm the column data format is General, and select Finish.

Check the result afterward, especially if the source contains dates, leading zeros, or identifiers. General format can interpret some text as dates or numbers, so this method is not suitable for every column. On Mac, Text to Columns is also available from the Data tab; the dialog layout may differ from Windows.

For a small number of cells, you can re-enter a value: edit the cell, remove any leading apostrophe or unwanted spaces, and press Enter. Changing the Number Format from Text to General by itself does not convert existing text values; it changes how Excel will treat future entries and may change the display of numeric values already stored as numbers.

Check for spaces, separators, and imported data

Data copied from a web page or another program may contain characters that are difficult to see. A regular space before or after a value can interfere with conversion. A nonbreaking space, often used to keep words together in web pages, may look identical to an ordinary space but is a different character. If only a few entries are affected, edit them directly. For a large import, inspect a sample and clean the source or use Power Query’s transformation tools before loading the data.

Decimal and thousands separators are another common source of confusion. Excel interprets separators according to regional settings and the file’s import options. For example, a value written as 1.234 may mean one point two three four in one convention and one thousand two hundred thirty-four in another. Do not replace punctuation across a whole column until you know which convention the source uses. Otherwise, Excel may convert a correct value into a different amount.

Also check whether the imported column mixes values and labels, such as 12, 18, and the text entry N/A. SUM ignores text entries in a referenced range; that may be intended, but it can also conceal missing data. Make sure the cells that were skipped are truly labels and not numbers stored as text.

Verify the formula and its range

For a basic total, check that the formula uses the correct start and end cells and that the range includes every value. With numbers in B2:B5—4, 7, 9, and 10—the formula =SUM(B2:B5) returns 30. If the formula instead refers to B2:B4, it returns 20, even though the omitted value is visible nearby.

Click the formula cell and inspect the colored outlines Excel places around referenced cells. You can also select the formula and press F2 to examine its references; press Esc if you do not want to edit it. Inserting rows or copying formulas can alter references, so verify that a copied total still points to the intended data. Absolute references, marked with dollar signs, stay fixed when a formula is copied; relative references adjust.

AutoSum can save time, but it guesses which nearby cells form the range. Select the cell where you want the total, choose Home > AutoSum or Formulas > AutoSum, inspect the highlighted range, and press Enter only if it is correct. On Windows, Alt+= inserts AutoSum. On Mac, use the AutoSum button on the Home or Formulas tab if the keyboard shortcut differs on your keyboard or version.

Remember that SUM adds the cells you name, not every visible number in a worksheet. If you have a filtered list and want a total that changes to include only visible rows, ordinary SUM may not match that goal. SUBTOTAL can be appropriate for filtered lists, but choose its function number based on whether you want to include manually hidden rows as well as filtered-out rows.

Make sure formulas recalculate

Excel normally recalculates formulas automatically when their inputs change. If a total stays stale, go to Formulas > Calculation Options and select Automatic. A workbook saved in Manual calculation mode can retain old results until recalculation occurs. The setting may affect other open workbooks in the same Excel session, so check the workbook again after changing it.

In Excel for Microsoft 365 on Windows, press F9 to recalculate formulas in open workbooks. Ctrl+Alt+F9 forces a full recalculation of all formulas in open workbooks. Use a full recalculation when ordinary recalculation does not refresh dependent results, but first check for a slow workbook or formulas that depend on external data. On Mac, use the calculation commands on the Formulas tab; keyboard shortcuts can depend on the keyboard and Excel version.

Manual calculation is sometimes used for very large or complex workbooks to avoid recalculating after every edit. It is not a fix for incorrect numbers, though, and it can make results look outdated. If you deliberately use Manual mode, recalculate before relying on totals or sharing the workbook.

Separate a wrong total from a display issue

A cell can contain a precise number but display fewer decimal places. For instance, a value of 2.45 may appear as 2 when the cell is formatted to show zero decimal places. The underlying value can still contribute 2.45 to a total. Select the cell and increase the displayed decimal places using Home > Increase Decimal, or inspect the formula bar to see the stored value.

Formatting is different from rounding the value with a formula. A displayed total of 100 may actually be 99.6 rounded for presentation, while the formula uses the underlying value. If the requirement is to calculate using rounded amounts, decide the rounding rule explicitly and apply it consistently; simply changing the number format does not round the underlying data.

Another visual clue is when a cell displays the formula itself, such as =SUM(B2:B5), rather than its result. Check whether the cell is formatted as Text, or whether Show Formulas is enabled. Change the cell format to General, then re-enter the formula by pressing F2 and Enter. If Show Formulas is on, turn it off from the Formulas tab. A column that is too narrow usually shows number signs for many numeric formats, not the formula text.

Look for formula errors and unusual values

If a formula returns an error such as #VALUE!, inspect the cells it uses. A calculation that expects numbers may fail when one of its inputs is text, or when a formula returns text instead of a numeric result. An error in a referenced cell can also flow into the total. Correct the source problem rather than hiding every error with an error-handling formula, which can make missing or invalid data harder to notice.

Check for accidental circular references too. A formula that includes its own cell—for example, a total in B10 that sums B2:B10—depends on its own result. Excel may warn about a circular reference, and iterative calculation can produce results only when deliberately configured. Usually the right repair is to change the range so it excludes the total cell.

Finally, do not assume that every number-looking item should be added. Dates are stored as serial numbers, and product IDs, telephone numbers, and postal codes are often identifiers, not quantities. Converting or summing these values may produce a mathematical result that is technically valid but meaningless. Preserve the data type that matches the information’s purpose.

Frequently Asked Questions

Why does SUM return zero when the cells show numbers?
Those entries may be stored as text, or the formula may point at empty or different cells. Check for a text warning, apostrophes, spaces, and the formula’s highlighted range. Convert confirmed numeric text to numbers and retest.

Does changing a cell from Text to General fix the number?
Not necessarily. Changing the format does not reliably convert a value already stored as text. Use Convert to Number, Text to Columns, or re-enter the value, then verify the result.

Why does my total not update after I change a value?
Calculation may be set to Manual. Select Formulas > Calculation Options > Automatic, then recalculate. Also confirm that the changed cell is actually included in the total’s references.

Why is AutoSum leaving out a number?
AutoSum chooses a nearby range based on its layout and may stop at a blank cell or select the wrong block. Inspect the highlighted range before accepting it, and edit the references if necessary.

Why does the total look wrong when the individual numbers look right?
Some values may be hidden text, the formula may omit rows, or the cells may display rounded values while storing more precise amounts. Check the data types, references, and decimal display before changing the formula.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.