Add Totals Row to Numeric Columns

What it does

You finish a small data table. Now you want a totals row at the bottom. Excel has a "Total Row" feature in formal Tables (the Ctrl+T kind), but most workbooks use plain ranges, not Tables, and the Tables feature only works after you convert. The macro skips the conversion step. Select your data, run the tool, and 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 how many SUM formulas it added.

Real-world example

Picture a sales operations analyst at a B2B SaaS company building the weekly pipeline review. Her job is to pull the latest opportunities export from the CRM, paste it into a clean tab in the master pipeline workbook, and generate a totals row at the bottom showing pipeline value, weighted value, count of deals, average deal size, you get the picture. Twelve columns, eight of which are numeric.

Her current routine: select column G, click into the cell below the last row, type =SUM(G2:G487), hit Enter. Repeat for columns H, I, J, K, L, M, N. Then go back and bold them all. Then add a top border. Then type "Total" in column A. About 4 minutes per week, including the inevitable typo where she accidentally writes =SUM(G2:G478) because her finger slipped on the row count.

With this macro she selects the data block (rows 2 to 488, columns A to L), runs the tool, and the macro writes 8 SUM formulas with correct ranges, drops "Total" in column A, applies bold and top borders to row 489, all in about 1 second. The popup tells her "Added totals row at row 489 with 8 SUM formula(s)." She glances at the totals, the numbers look right, she sends the workbook to her VP.

On a 487-row weekly pipeline export with 8 numeric columns, expect about 3 to 4 minutes saved per pass. Across 50 weeks that's about 3 hours back, and zero =SUM(G2:G478)-style typos.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the data range without including any existing totals row. The macro writes the totals to the row immediately below your selection.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick AddTotalsRow, click Run.
3. The macro writes a SUM formula in every numeric column and drops a "Total" label in the first non-numeric column. The popup confirms how many SUM formulas were added.

FAQ

How does it decide which columns are numeric?

It scans each column in your selection. If at least one cell in the column has a numeric value (and is not a string-formatted number like "$1,234"), the column is treated as numeric and gets a SUM. Otherwise it's skipped. So a column with mixed numbers and "N/A" entries still gets a SUM (which silently ignores the N/A); a column with only text gets the "Total" label or stays blank.

What if my numbers are stored as text from a CSV import?

The macro skips them, because VarType returns vbString for text-stored numbers and the SUM would return zero anyway. Run Convert Text to Numbers on the column first to turn the text into real numbers, then run AddTotalsRow.

Does it handle filtered or hidden rows?

The SUM formula sums every cell in the range, including hidden rows. If you want a total that respects filters, replace SUM with SUBTOTAL(9, range) after the macro writes the formulas. We may add a "filter-aware" option that uses SUBTOTAL by default in a future version.

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's row. Always undo before re-running, or extend your selection to the original data range only. If you've added new data and want to refresh the totals, undo the previous totals row first, then select the expanded data range and run again.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes the new row, the formulas, the bolding, the borders, and the label in one step. Always work on a copy if your file is critical. The macro runs entirely offline. Free, hosted on GitHub, source visible.