Excel Column Addition Formula: Sum a Column

Formulas & functions · Updated November 18, 2026

To add every number in an Excel column, use =SUM(B:B), replacing B with the column you want to total. If your values are in B2:B20, use =SUM(B2:B20) instead; put the formula outside the range being added so it does not include its own result.

The basic formula for adding a column

Excel’s SUM function adds numbers and returns their total. A full-column reference such as B:B tells Excel to consider all of column B, while a range such as B2:B20 limits the calculation to those specified cells. For example, if B2 through B5 contain 12, 8, 15, and 5, the range formula returns 40.

With values anywhere in column B and the result placed in another column, such as D1, use =SUM(B:B). For a defined list in B2:B20, use =SUM(B2:B20). These formulas update when you change a number in the referenced cells. The full-column version also includes new numbers entered farther down column B, whereas the bounded version does not include entries outside rows 2 through 20.

To enter a formula, select the cell where the answer should appear, type an equals sign and the formula, then press Enter. In Excel for Microsoft 365 on Windows, you can also select a cell below a list and choose Home > AutoSum, then press Enter to accept the suggested range. Excel for Mac and Excel for the web use the same SUM formulas; ribbon placement can vary slightly by platform.

Choose a full column or a specific range

A full-column reference is convenient when the list grows and you want to avoid adjusting the formula as new rows are added. If column B contains a running list of expenses, for example, =SUM(B:B) includes future entries in that column. Put the result in another column, not in B itself: a formula in B that sums B:B would include itself and produce a circular reference.

A specific range is more controlled. With amounts in B2:B20, =SUM(B2:B20) excludes a heading in B1 and avoids unrelated values lower down the sheet. Use a bounded range when only a known block belongs in the total, or when the same column contains other numbers that should not be included.

For a table that is expected to expand, consider converting the list to an Excel table with Insert > Table. A table’s structured references can expand with added rows, and they make formulas easier to interpret. For example, if a table is named Sales and its numeric column is named Amount, the total-row calculation can refer to that column as =SUM(Sales[Amount]). This assumes the table is named Sales and the column header is Amount. You can see or change table names on the Table Design tab after selecting a table cell.

Add two columns row by row

Sometimes “add columns” means combining corresponding values from two columns, rather than calculating one grand total. If B2 and C2 contain amounts for the same item, enter a row formula in D2 and fill it down. With the two inputs in B2 and C2, use =B2+C2. If B2 is 12 and C2 is 8, the result in D2 is 20. Drag the fill handle from D2 down to copy the formula for later rows; Excel adjusts the row references automatically.

For a row containing several values, SUM can add the cells across the row. With values to combine in B2 through E2, enter =SUM(B2:E2) in F2. If those cells contain 3, 4, 5, and 6, the result is 18. This is often easier to extend than typing a long chain of plus signs.

Do not confuse a row-by-row calculation with a total of both entire columns. To total two separate lists together, use SUM with both ranges. With the first list in B2:B20 and the second in C2:C20, enter =SUM(B2:B20,C2:C20) in a cell outside those ranges. This returns one combined total, not a separate result on each row. If the row positions correspond and you need a per-row combined amount, use the row formula instead.

SUM, plus signs, and AutoSum

For a column total, SUM is generally clearer and easier to maintain than manually adding every cell with plus signs. With values in B2:B4, =B2+B3+B4 adds those three cells directly, but you must edit the formula if you add another row. In contrast, =SUM(B2:B4) expresses the same range more compactly. Both return the same total for ordinary numeric inputs.

Use plus signs when the calculation is specifically between a small number of cells or when you are building a longer arithmetic expression. Use SUM when adding a range, several ranges, or a complete column. AutoSum is a shortcut for creating a SUM formula: select the cell immediately below a list and choose Home > AutoSum. Check the highlighted range before accepting it, especially if the list has blank rows or nearby numbers that should not be part of the result.

On Windows, Alt+= is the keyboard shortcut for AutoSum. On current Excel for Mac, Command+Shift+T invokes AutoSum; if that shortcut is unavailable in your setup, use the ribbon command. The shortcut inserts a formula rather than permanently calculating a value, so the total changes when the source numbers change.

Make totals update as data changes

SUM formulas recalculate when referenced values change, and a bounded formula includes new entries only if they fall inside its range. Suppose the list is currently B2:B20 and you add a new amount in B21. A formula using =SUM(B2:B20) will not include B21, while =SUM(B:B) will. You can also expand the bounded reference, or use an Excel table if new records are routinely appended.

Full-column references are easy to maintain, but they include every cell in that column, including numbers far below the visible list. That can create an incorrect total if unrelated figures are stored in the same column. A defined range or table column makes the intended data boundary clearer. For a one-off worksheet with a dedicated numeric column, a full-column formula is often perfectly practical; choose based on how the sheet is organized rather than assuming one form is always best.

When copying formulas, relative references such as B2:B20 can shift to C2:C20 if copied one column to the right. A full-column reference B:B also shifts when copied horizontally. If a formula must keep pointing at a particular column while being copied, an absolute reference can help. With values in B2:B20, the formula =SUM($B$2:$B$20) keeps that range fixed when copied elsewhere. Dollar signs lock the column, row, or both in a cell reference.

Filtered lists and visible-cell totals

A common surprise is that SUM includes rows hidden by a filter. If you filter a list to show only one category, a formula such as =SUM(B2:B100) still adds numeric values in the filtered-out rows. That behavior is useful when you want the overall total to remain constant, but it is not the right result when you want a total for only the visible records.

For a filtered list where values are in B2:B100, use =SUBTOTAL(9,B2:B100) to sum visible rows. The first argument, 9, specifies the SUM operation; SUBTOTAL excludes rows hidden by a filter. If you also want to exclude rows hidden manually, use function number 109 instead: with the same range, enter =SUBTOTAL(109,B2:B100). This distinction matters when a worksheet uses both filtering and manual row hiding. SUBTOTAL can also perform other calculations using different function numbers, but its behavior should be selected deliberately.

For a straightforward total of all records, keep using SUM. Before replacing it with SUBTOTAL, decide whether hidden values should count. Filtering does not delete data, and the choice of function changes the meaning of the displayed answer.

Common problems and how to fix them

  • The result is zero or too small: Check that the formula references the right column and rows. Numbers stored as text may not be treated like ordinary numeric values. Select a suspicious cell and inspect its alignment or warning indicator, then convert the text value to a number if appropriate.
  • The total does not change: Confirm that the edited cell lies inside the referenced range. A formula limited to B2:B20 will not include a new value in B21. Also check that calculation is set to Automatic under Formulas > Calculation Options.
  • The formula shows in the cell instead of a result: The cell may be formatted as Text, or Show Formulas may be turned on. Change the cell format to General, re-enter the formula, and check Formulas > Show Formulas.
  • Excel reports a circular reference: The formula may be inside the range it sums. Move the result to another column or exclude the result cell from the range.
  • The total is unexpectedly large: A full-column formula may be adding unrelated numbers elsewhere in that column. Use a specific range or a table column that contains only the intended records.
  • An error appears: SUM generally passes through an error value in its referenced cells, so inspect the range for errors such as #VALUE! or #N/A. Correct the source error if it should not be present; do not hide it without understanding what it means.

SUM ignores blank cells and text in referenced cells, but it does not ignore numeric values merely because they look unrelated to the list. It also does not automatically exclude filtered-out rows. These are separate issues: data type determines whether a cell is counted as a number, while the function and range determine which cells are considered.

Frequently Asked Questions

What is the formula to add an entire Excel column?
With the numbers in column B and the answer outside column B, enter =SUM(B:B). Replace B with the column letter you need.

How do I add only some cells in a column?
With the desired values in B2:B20, enter =SUM(B2:B20). Change the first and last row numbers to match the cells you want included.

How do I add two columns together in Excel?
For a row-by-row result with inputs in B2 and C2, enter =B2+C2 in D2 and fill it down. For one grand total across both lists, with data in B2:B20 and C2:C20, use =SUM(B2:B20,C2:C20).

Does SUM include hidden or filtered rows?
SUM includes values in rows hidden by a filter. With the list in B2:B100, use =SUBTOTAL(9,B2:B100) to total visible filtered rows. Use function number 109 instead if manually hidden rows should also be excluded.

Can I put the total formula in the same column?
Yes, if the formula’s range does not include its own cell. For example, a total below values in B2:B20 can be placed in B21 using =SUM(B2:B20). Do not put =SUM(B:B) in column B, because that full-column reference includes the formula cell and creates a circular reference.

Why does Excel show a formula instead of its answer?
Check whether the formula cell is formatted as Text or whether Formulas > Show Formulas is enabled. Set the cell to General and re-enter the formula if it was stored as text.

Related

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.