To add numbers in Excel, select the cell where you want the total, type a SUM formula, and press Enter. For example, with numbers in B2 through B6, use =SUM(B2:B6). SUM adds the values in the selected cells and updates the result when those values change. You can also use AutoSum to have Excel suggest a range. The steps below are for Microsoft Excel for Microsoft 365 on Windows.
What You’ll Need
- An Excel workbook with numbers you want to add. The values can be in a continuous range, separate cells, a row, or a column.
- A blank cell for the total. Placing the total outside the range you are adding helps avoid including the result in its own calculation.
- Basic familiarity with cell addresses: B2 means column B, row 2. The address bar above the worksheet shows the selected cell’s address.
Step-by-Step
- Arrange and check the values. Put the numbers you want to total in a worksheet, ideally in one uninterrupted column or row. For the main example, suppose B2 contains 12, B3 contains 8, B4 contains 15, B5 contains 5, and B6 contains 10. Make sure the cells contain numeric values rather than numbers stored as text. Labels such as Product or Total are fine in a neighboring column, but should not be included in the range you intend to add.
- Choose where the total should appear. Click a blank cell below or beside the values, such as B7 for the example above. Check that this cell is not part of the range you plan to sum. A total cell can be elsewhere on the sheet too; the formula can refer to cells in another part of the worksheet. Leaving a label such as Grand total in the adjacent cell can make a report easier to read.
- Start the formula. Type an equals sign in the selected total cell. Excel uses the equals sign to recognize that what follows is a calculation, rather than ordinary text. Then type SUM and an opening parenthesis. You can enter the cell references yourself or select cells with the mouse while building the formula. Do not add spaces or punctuation inside a cell address.
- Enter the range to add. With the example values in B2:B6 and the total in B7, the formula is
=SUM(B2:B6). The colon means include every cell from B2 through B6, including both endpoints. The result is 50: 12 + 8 + 15 + 5 + 10 = 50. Type the closing parenthesis after the range, then press Enter. Excel displays the result in B7 while retaining the formula in that cell. - Confirm the result and the formula. Click the total cell and look at the formula bar above the grid. It should show the formula, while the cell itself normally shows the calculated number. If the formula bar is hidden, use the View tab and turn on Formula Bar. Check that the range starts and ends at the intended cells, and compare the result with a quick manual check if the list is short.
- Use AutoSum for a quick total. Click the blank cell directly below a column of numbers, or directly to the right of a row of numbers. On the ribbon, choose Home > Editing > AutoSum. Excel proposes a range and displays the suggested formula in the total cell. Inspect the outlined cells before accepting it; adjust the selection if it leaves out a value or includes a heading. Press Enter to accept the suggestion. AutoSum is convenient, but checking the proposed range is still important.
- Add selected, nonadjacent cells if needed. A SUM formula can include several separate cells or ranges. With 12 in B2, 8 in B3, 15 in B4, 5 in D2, 10 in D3, and 20 in D4, use
=SUM(B2:B4,D2:D4)in a blank cell. Excel returns 70. The comma separates the two ranges; it does not mean that the cells between columns B and D are included. Select each reference carefully if you build this formula by clicking cells. - Sum a row or an entire column when appropriate. To total a row, identify its first and last numeric cells. With 4 in B2, 6 in C2, 10 in D2, 3 in E2, and 7 in F2, entering
=SUM(B2:F2)in another cell returns 30. To total a whole column, with 12 in B2, 8 in B3, and 15 in B4 and no other numbers in column B,=SUM(B:B)returns 35. A whole-column formula also includes future numeric entries in that column, so avoid putting the total itself in column B or you could create a circular reference. - Update the source data and verify the total. Change a value within the range and confirm that the result changes. SUM normally recalculates automatically. If you add new values immediately below a range, check whether the formula includes those new rows; a formula that refers only to B2:B6 does not automatically expand to include B7. Edit the ending reference, or use an Excel table if the list will grow regularly.
Common Mistakes
Leaving out the equals sign: Excel treats an entry without the equals sign as text in many cases, so it will not calculate it. If the cell displays the characters you typed instead of a result, select the cell, enter the formula correctly, and press Enter.
Selecting the wrong range: A formula can calculate without an error and still give the wrong total if the first or last cell is off by one row. AutoSum can also guess an incomplete range when a blank cell interrupts the data. Review the highlighted range before accepting it and inspect the cell references in the formula bar afterward.
Including the total cell in the range: A total cannot add itself. If the formula in B7 includes B7, Excel may report a circular reference or fail to calculate as expected. Keep the result outside its input range; for a column total, putting it in a different column or above the data can prevent accidental self-reference.
Numbers stored as text: A value that looks like a number may have been imported or entered as text. SUM generally ignores text in referenced cells, which can make a total too low. Look for warning indicators, inconsistent alignment, or a green triangle in the cell. Convert the affected cells to numbers, then check the result again.
Assuming a blank is the same as a missing value: Blank cells do not add anything, but an accidentally empty cell can still represent data that has not been entered. Check the source list rather than assuming a plausible-looking total proves every value is present.
Tips
- Use a range reference for a continuous block. It is quicker to enter and easier to review than listing every cell separately.
- Keep labels outside numeric ranges. For example, place a heading above the data rather than between the values, so a blank or text label does not interrupt the range AutoSum detects.
- For data that will grow, format the list as a table by selecting it and choosing Insert > Table. A formula that refers to a table column can include rows added to that table. Confirm that the table includes the intended data and headers before relying on it.
- Use the formula bar to edit a formula without retyping the entire entry. You can also double-click the formula cell to inspect its references directly on the worksheet.
- Format the result separately from calculating it. Select the total cell and use the Home tab’s number-format controls to show a suitable number of decimal places or a currency format. Formatting changes how the result appears, not which cells SUM adds.
- If a workbook uses several totals, add clear labels and keep each formula close to the values it summarizes. This makes later checks much easier, especially when someone else needs to maintain the worksheet.
Troubleshooting
The cell shows the formula instead of its result: First, check whether Show Formulas is turned on. Press Ctrl+` on Windows to toggle it off; the backtick key is usually above Tab. If only one cell is affected, it may be formatted as Text. Change its number format to General on the Home tab, edit the cell, and press Enter so Excel interprets the entry as a formula. On Mac, the shortcut can vary by keyboard layout; use the Formulas tab’s Show Formulas control if the key combination does not work.
The answer is zero or too small: Check that the range points to the cells containing the values, then investigate any numbers stored as text. Select a suspect cell and look at the formula bar and alignment. You can often convert imported text by selecting the affected cells and choosing the warning icon’s Convert to Number option when Excel offers it. If no warning appears, re-enter a value as a number or use the Data tab’s Text to Columns tool to convert a suitable imported column.
Excel displays a circular-reference warning: Inspect the formula’s range and make sure it does not include the cell containing the formula. Move the total outside the range or correct the ending row. Do not enable iterative calculation just to hide the warning; that setting is intended for specific models that deliberately use circular calculations.
The total does not change after editing a value: Confirm that the changed cell is included in the formula. If it is, check the calculation setting through Formulas > Calculation Options and select Automatic. If the workbook is set to Manual, Excel may wait for a recalculation; press F9 on Windows to recalculate. On Mac, use the Formulas tab’s calculation controls or the recalculation shortcut for your version.
AutoSum selects an unexpected range: Cancel or revise the suggestion before pressing Enter. Click and drag over the correct cells, or type the intended references into the formula. AutoSum looks for nearby numbers and can be confused by blank rows, neighboring totals, or other data blocks. For irregular layouts, entering the range directly is often more reliable.
Frequently Asked Questions
Can I use SUM for numbers in separate cells?
Yes. Enter each cell or range as an argument, separated by commas in standard English-language Excel settings. Check that every reference points to a value you intend to include. Separate ranges are useful when the relevant figures are in different parts of a worksheet, but a continuous range is usually simpler when the data is together.
Does SUM include blank cells?
Blank cells contribute zero to the calculation, so they do not change the numeric total. However, a blank in the middle of a list may lead AutoSum to choose only part of the data. Check the source list for missing entries and verify the formula’s boundaries.
Can SUM add numbers from another worksheet?
Yes. You can select cells on another sheet while editing a formula, and Excel inserts the sheet reference. The sheet name appears before the cell reference, followed by an exclamation mark. If you type the reference yourself, use the exact worksheet name; names containing spaces need single quotation marks around the name. Confirm the selected sheet and cells before accepting the formula.
Why is my total different from a calculator?
Look for omitted rows, extra cells, numbers stored as text, or a value included twice through overlapping ranges. Also check whether the displayed values are rounded: a cell can show two decimal places while storing more digits, and SUM uses the stored values. Use the formula bar to inspect inputs and increase displayed decimal places when needed.
Should I type SUM or use AutoSum?
Either method can produce a working total. Typing SUM gives you direct control over the references and is useful for distant or separate ranges. AutoSum is faster for a straightforward adjacent row or column, but inspect its suggested selection before you accept it.
Will the total update when I add more data?
It updates when values change inside the referenced range, provided calculation is set to Automatic. New values outside that range are not included automatically in a basic fixed-range formula. Extend the range, use a whole-column reference where suitable, or convert a growing dataset to a table and reference its column.
Related
- How to Autofill in an Excel Table: Steps That Work
- Power Query Data Validation in Excel: What It Can Do
- Excel Data Validation: Create Rules and Drop-Down Lists
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Excel: How to Reference Cells in CriteriaTo make COUNTIF use a cell as its criterion, put the cell reference in the second argument: with…
- Text Formula Examples in Excel: Clean, Extract, CombineExcel text formulas let you combine words, extract part of a value, standardize capitalization, and clean imported text.…
- Excel SUMPRODUCT Shortcut: Formula, Uses, and ExamplesExcel has no dedicated keyboard shortcut for SUMPRODUCT: enter the formula in a cell and press Enter. For…
- COUNTIF for Text: Exact Matches, Wildcards, and TipsTo count cells that exactly match text in Excel, use =COUNTIF(A2:A7,"East"). It counts matching entries in A2:A7 without…
- Excel Formula for Difference Between Two Numbers ExplainedTo find the difference between two numbers in Excel, subtract one cell from the other: if the first…
- Freeze a Formula in Excel: Keep Cell References FixedTo freeze a cell reference while copying a formula in Excel, edit the formula, select the reference, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.