# How to sum a column in Excel

Select the cell below or beside the numbers and use `=SUM(B2:B7)`, or press Alt+= (AutoSum) to have Excel write it for you.

**Excel:** `=SUM(B2:B7)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Month | Electric bill |  | Total |
| Jan | 412.36 |  |  |
| Feb | 398.12 |  |  |
| Mar | 356.8 |  |  |
| Apr | 301.45 |  |  |
| May | 287.9 |  |  |
| Jun | 344.17 |  |  |

Formula in E1: `=SUM(B2:B7)`

Result: $2,100.80

## How it works

SUM adds every number in the range `B2:B7`, the first cell to the last, and skips empty cells and text. The colon means "everything from B2 to B7", so the total updates as soon as any bill changes.

## Other ways

- `=SUM(B:B)`: A whole-column reference keeps working when you add rows. Put the total outside column B, or it becomes a circular reference.
- `=B2+B3+B4+B5+B6+B7`: Fine for two or three cells, but it breaks with #VALUE! if a cell holds text, and it does not grow when you insert a row.

## Common mistakes

- A total inside the range it adds (`=SUM(B2:B8)` typed in B8) is a circular reference. Excel warns and shows 0.
- Numbers stored as text are skipped without a warning. If the total looks too small, look for numbers aligned to the left of the cell.
- Inserting a row just below the last number is outside `B2:B7`. Insert rows inside the range, or use an Excel Table, so the reference grows.

## Questions

**What is the shortcut to sum a column?**

Click the cell below the numbers and press Alt+= (Cmd+Shift+T on a Mac). Excel writes the SUM and selects the range for you to check.

**How do I sum only the visible rows after filtering?**

Use `=SUBTOTAL(109,B2:B7)`. SUM always adds hidden rows too.

**How do I make the total include rows I add later?**

Use the whole column, `=SUM(B:B)`, with the total outside column B, or convert the data to a Table (Ctrl+T) and use `=SUM(Table1[Electric bill])`, which grows with the table.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-a-column