How to Add a Totals Row to a Table in Excel

The bottom-of-the-table problem

Every sales report ends the same way: a totals row at the bottom. Sum the amounts. Sum the quantities. Maybe sum a couple of other columns. Type Total in the first column. Bold it. Add a top border so it visually separates from the data.

Doing this by hand is 90 seconds, three of which are inevitably a typo in the SUM range. Here are three faster ways.

TL;DR — Key takeaways

• For data in a formal Excel Table (Ctrl+T), turning on the Total Row option auto-adds a totals row.
• For plain ranges (most workbooks), you write SUM formulas one column at a time.
• A free VBA macro detects numeric columns automatically and writes SUM formulas to all of them in one click.
• All methods can produce wrong totals if your data has numbers stored as text. Check for that first.

Method 1: Convert your range to a Table (Ctrl+T)

If your data isn't already in a formal Excel Table, this is a one-time conversion:

1. Select your data block.
2. Press Ctrl + T. Confirm the data has headers if applicable.
3. Click anywhere in the table. Table Design tab appears.
4. Check Total Row.

A totals row appears at the bottom. Each column has a dropdown letting you pick SUM, AVERAGE, COUNT, etc.

Pros: Auto-updates as you add rows. Lets you switch between sum/average/count per column. Looks polished.

Cons: Converting to a Table changes how the range behaves: structured references replace cell addresses in formulas, and some old-style features (like merged cells, certain pivot setups) work differently. Many established workbooks intentionally avoid Tables.

Method 2: Manual SUM formulas (plain range)

For a range that isn't a Table:

1. Click into the cell directly below your last data row, in the first numeric column.
2. Type =SUM(.
3. Drag to select the column above (Excel auto-fills the range).
4. Press Enter.
5. Click the cell with the new SUM, copy it (Ctrl+C).
6. Click the totals row in the next numeric column, paste.
7. Repeat for each numeric column.
8. (Optional) Type Total in the first non-numeric column.
9. (Optional) Bold the totals row, add a top border.

About 60 seconds for 6 numeric columns, plus formatting. Plenty of room for off-by-one errors in the SUM range.

Method 3: The free VBA macro (auto-detects numeric columns)

Download Add Totals Row to Numeric Columns. Free .xlsm with one macro.

Steps:

1. Select the data range without including any existing totals row.
2. Press Alt + F8, pick AddTotalsRow, click Run.
3. The macro inspects each column, writes a SUM formula in the row immediately below for every numeric column, drops a Total label in the first non-numeric column, applies bold and a top border, and reports the count.

For a 12-column block with 8 numeric columns, this writes 8 SUM formulas with correct ranges in about 1 second.

A common scenario: weekly pipeline review

Pipeline export from the CRM, 487 rows of opportunities, 12 columns, 8 of them numeric (deal size, weighted value, count of activities, etc.). Your VP wants column totals at the bottom.

With the macro:

1. Select rows 2 through 488, columns A through L.
2. Run the macro.
3. Macro writes 8 SUM formulas, drops Total in column A, applies bold and top border in row 489.

The popup confirms Added totals row at row 489 with 8 SUM formula(s). That's faster than typing one SUM, plus zero risk of =SUM(G2:G478) typos.

What if my numbers are stored as text?

The macro skips columns where the values are stored as text strings. Run Convert Text to Numbers on the affected columns first, then re-run the totals macro.

Sign your numbers are text:

• They're left-aligned by default (numbers are right-aligned).
• A small green triangle appears in the top-left of each cell.
• =SUM(...) returns 0 even though the cells visibly contain numbers.

What if I want SUBTOTAL instead of SUM (filter-aware)?

SUM(range) ignores filters and totals everything in the range. SUBTOTAL(9, range) respects filters and totals only visible rows. For filter-aware reports, edit the macro source: change =SUM(...) to =SUBTOTAL(9, ...). It's a one-line edit in the .bas file. We may add a SUBTOTAL option in a future version of the macro.

What if I want averages or counts instead of sums?

Same answer: edit the source. The macro is opinionated for SUM because that's what 90% of "totals row" use cases want. For mixed total types (some SUMs, some AVERAGEs), the formal Table approach (Method 1) is more flexible.

Frequently asked questions

What if I run it twice on the same data?

You'll get two totals rows, one below the other, with the second SUM range including the first totals row. Always undo before re-running, or extend your selection to the original data range only.

Does the macro handle filtered or hidden rows?

SUM includes hidden rows in the calculation. So if you filter your data and run the macro, the totals reflect the unfiltered total, not what you see on screen. For filter-aware totals, edit the source to use SUBTOTAL(9, ...) (see above).

What about my "Total" label in the first non-numeric column?

The macro writes Total in the first non-numeric column it encounters. Usually that's column A (a name or category column). If you have a different label preference (TOTAL, Sum, Grand Total), edit the .bas source: change ws.Cells(totalsRow, sourceCol).Value = "Total".

Will it preserve my existing formatting?

The macro applies bold and a top border to the totals row. Existing formatting in your data rows is untouched. If you have specific column widths or number formats elsewhere, those stay as they were.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes the new row, the formulas, the bolding, and the borders in one step.

What to do next

After adding the totals row, if you're sharing the file and want to lock the formulas to their current values, run Convert Formulas to Values on the totals row. If your source data has dirty cells (text-stored numbers), Convert Text to Numbers is a useful prep step.