To total a column in Excel, use =SUM(A2:A100) for a specific range, or =SUM(A:A) for the entire column. Put the result in a different column or outside the summed range; placing a whole-column SUM inside that same column can create a circular reference.
The basic SUM formula
SUM is Excel’s standard function for adding numbers. With amounts in A2:A5 and the total displayed in B1, enter =SUM(A2:A5) in B1. If the cells contain 120, 85, 95, and 40, the result is 340. The colon means “through,” so A2:A5 includes every cell from A2 to A5.
You can type the formula directly into a cell or into the formula bar, then press Enter. Excel displays the result in the cell and keeps the formula available for recalculation. If one of the source amounts changes, the total updates automatically.
A range is usually the clearest choice when you know where the data starts and ends. It makes the formula’s scope easy to see and avoids including unrelated values elsewhere in the column. Common variations include:
- With quantities in C2:C20 and the total outside that range, use
=SUM(C2:C20)to add those 19 cells. - With weekly figures in D5:D12 and the result in E5, use
=SUM(D5:D12). - With a single value in A2 and the result elsewhere, use
=SUM(A2:A2), although simply referencing the cell is also possible.
Choosing between a fixed range and a whole column
A fixed range such as A2:A100 is useful when the data has a known boundary. It also makes it less likely that a note, another calculation, or an unrelated number farther down the sheet will become part of the total. The trade-off is that new entries below row 100 will not be included unless you expand the range.
A whole-column reference can be convenient for a list that may grow. With a heading in A1, amounts of 120, 85, and 95 in A2:A4, and the total in C1, enter =SUM(A:A) in C1; it returns 300. SUM ignores the text heading, so it does not need to be excluded manually. Keep the result outside column A: if the formula is in A5, it tries to include its own result and Excel reports a circular reference.
Whole-column references are common in simple worksheets, but they are not always the best layout. A formula that totals A:A can also include later numbers added to that column for a different purpose. For a small, clearly defined list, a bounded range is more explicit. For a growing dataset, an Excel Table often provides a more reliable way to include new rows.
Adding a column with AutoSum
AutoSum inserts a SUM formula for you. In Excel for Microsoft 365 on Windows, select the empty cell directly below a vertical list of numbers and choose Home > AutoSum, or use the AutoSum button on the Formulas tab. Excel proposes a range based on nearby data. Check the highlighted cells before accepting the suggestion, then press Enter.
For example, with values 120, 85, 95, and 40 in B2:B5 and the total cell B6 selected, AutoSum normally proposes =SUM(B2:B5). The result is 340. If the proposed outline omits a value or includes a cell that should not be counted, select the correct range in the formula before pressing Enter.
The Windows keyboard shortcut for AutoSum is Alt+=. On Mac, use the AutoSum control on the ribbon if that shortcut is unavailable or differs with the keyboard layout; the Formulas tab provides the command. Excel for the web also has an AutoSum command on the ribbon, though the exact available shortcuts can vary by browser and keyboard setup.
Use an Excel Table for data that grows
If you regularly add records, converting the list to an Excel Table can reduce range-maintenance work. Select a cell in the data and choose Insert > Table, confirm whether the table has headers, and select OK. You can also use Ctrl+T on Windows. On Mac, use Insert > Table or the corresponding table command available in the ribbon.
Give the table and its amount column meaningful names when useful. With a table named Table1, a Sales column containing 120, 85, and 95, and the formula entered outside the table, =SUM(Table1[Sales]) returns 300. This structured reference identifies the Sales column by name, and the table’s column reference expands as rows are added to the table.
Tables can also show a built-in total row. Select a cell in the table, open the Table Design tab, and turn on Total Row. In the total row, choose Sum from the column’s dropdown. Excel uses a subtotal calculation for that row so the displayed total can respond to filtering. This is useful for reports where readers need to filter records and see a total for the visible results.
Totals that respond to filters and hidden rows
SUM adds the numbers in its referenced cells whether or not some rows are hidden by a filter. That behavior is appropriate when you want the grand total of all records, but it can be surprising when you expect the result to change as you filter a list.
For a filtered list, SUBTOTAL can return a sum of visible records. With amounts 120, 85, and 95 in A2:A4, a filter hiding the row containing 85, and the formula placed outside the filtered range, =SUBTOTAL(109,A2:A4) returns 215. The function number 109 tells SUBTOTAL to sum while excluding filtered-out rows and manually hidden rows. Function number 9 also sums while respecting filters, but it includes manually hidden rows.
That distinction matters when rows are hidden manually rather than by a filter. Choose the behavior that matches the report: a regular SUM for every value in the range, or SUBTOTAL when the displayed result should reflect which rows are visible. If you use a Table Total Row, Excel’s built-in calculation is designed to work with the table’s filtered view.
What SUM includes—and what it does not
SUM adds numeric values in the cells or ranges you reference. Blank cells and ordinary text in referenced cells are ignored, which is why a column heading does not prevent a whole-column total. A cell containing a number stored as text is not reliably treated as a numeric amount by SUM, even if it looks like one on screen. Convert such entries to real numbers if they should be included.
Formatting is separate from the underlying value. Applying Currency or Number formatting changes how a value appears, not what SUM calculates. Conversely, a value that looks like a number may still be text. A small green triangle or an error-checking notice can indicate that Excel has stored a number as text; use the warning options or convert the cell contents appropriately.
Errors in a referenced range can affect the result. If a cell included in the range contains an error such as #N/A or #VALUE!, the SUM result may show an error too. Check the source cells before assuming the total is wrong. Also check for negative values, which reduce the total, and for numbers outside the selected range, which are not counted at all.
Common problems and quick checks
If a total appears incorrect, start by confirming the range shown in the formula bar. A formula that stops at row 99 will not include a value in row 100. If the list is likely to grow, extend the range or convert it to a Table. Then check whether the cells that should count are stored as numbers rather than text.
- The formula displays instead of a result: The cell may be formatted as Text, or Show Formulas may be enabled. Change the cell format to General, re-enter the formula, and check the Formulas tab for the Show Formulas setting.
- The result is zero or too small: Check for numbers stored as text and verify that the referenced range covers every intended row.
- Excel reports a circular reference: Move the total outside the cells being summed. A whole-column formula cannot safely sit in the column it references.
- The total does not change with a filter: SUM includes filtered-out records. Use SUBTOTAL or a Table Total Row if the visible rows should determine the displayed total.
- The result is an error: Inspect the source range for error values and correct or handle those source errors as appropriate.
Excel usually recalculates formulas automatically. If a workbook is set to manual calculation, a changed input may not immediately update the displayed total. Check Formulas > Calculation Options and select Automatic if that is the intended workbook behavior. Calculation settings can affect the workbook more broadly, so consider whether manual calculation was deliberately chosen.
SUM compared with other ways to total
For most ordinary column totals, SUM is the simplest option. AutoSum is not a different kind of arithmetic; it is a convenient command that writes a SUM formula after guessing the range. A Table Total Row is useful when you want a visible summary that can adapt to filtering. SUBTOTAL is preferable when the answer should depend on which rows are currently visible.
There is no need to use a separate addition operator for every cell in a long list. SUM is easier to review and maintain than a chain such as A2+A3+A4, especially when the range changes. It also makes the intended operation clear to someone reviewing the workbook later. Keep the data in one consistent column and put labels, notes, and totals in clearly separate places to make formulas easier to audit.
Frequently Asked Questions
What is the formula to add an entire column in Excel?
With the values in column A and the result placed outside column A, use =SUM(A:A). It includes numeric values throughout that column, so check that there are no unrelated numbers in it.
How do I total a column without including the header?
With a heading in A1 and numbers beginning in A2, use a range such as A2:A100 when the last relevant row is 100. SUM ignores a text heading in a whole-column reference too, but a bounded range makes the intended data area clearer.
Why is my Excel column total not updating?
Check that calculation is set to Automatic, that the changed cells are inside the formula’s range, and that the formula is not stored as text. If the workbook uses manual calculation, recalculate it or change the calculation setting if appropriate.
How can I total only visible rows?
Use SUBTOTAL with function number 109 when filtered-out and manually hidden rows should be excluded. A Table Total Row is another practical option for a filtered table.
Can I put the total at the bottom of the same column?
Yes, when the formula sums only the data cells above it. For example, a total in A101 can sum A2:A100. Do not use a whole-column reference in A101, because that reference also includes the formula cell.
Related
- Power Query Replace Text: Steps, Options, and Examples
- Conditional Formatting: Use More Than One Rule in Excel
- table() in RStudio: How It Counts and Displays Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Extract Text Between Quotes with FormulasIf the text is in A2, use =IFERROR(TEXTBEFORE(TEXTAFTER(A2,""""),""""),"") in Microsoft 365 or Excel 2024 to return the text…
- Power Query XLOOKUP From Another Table: How It WorksPower Query does not run the Excel XLOOKUP function inside a query. To return columns from another table,…
- Bing Chat Excel Formula Not Calculating? Causes & FixesIf a formula from Bing Chat is not calculating, first paste it into an Excel cell and confirm…
- Excel IFS List: Syntax, Examples, and Common FixesExcel’s IFS function checks conditions in order and returns the result paired with the first condition that is…
- Excel Text Functions List: Names, Uses, and ExamplesExcel’s text functions help you extract, combine, clean, compare, format, and convert text or values. The main functions…
- Access Database vs Excel: How to Choose the Right ToolChoose Microsoft Access when you need a structured database with related tables, forms, and repeatable queries; choose Excel…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.