How To Sum All Columns in the Total Row of an Excel Table

In this post and video I explain how to add the sum (subtotal) to all the cells in the Total Row of an Excel Table.

This article is based on a great question submitted by Don.

The Problem

Unfortunately, there is no built-in way to do this in Excel.  When you click the Total Row checkbox in the Table Tools ribbon, it only adds the SUBTOTAL formula to the bottom right corner of the Excel Table.

To compound that problem, when you copy/paste this formula to the other cells in the Total Row, the results are incorrect.  This happens because the structured column reference (column names) do NOT change within the formula.  All the pasted formulas will still reference the column of the copied cell.

In the video below I explain a few solutions to this problem.  You can see read the explanations below the video.

Video – How to Create Subtotal Formulas in All Cells in the Total Row

Solution #1 – Drag Formulas Across

Instead of copy/paste, you can drag the formulas across the cells to get the correct results.  Dragging the formulas will change the column reference in the formula.  This behaves more like the relative references you are used to when working with formulas that contain cell references (B2:B5).

This requirement to drag a formula across instead of copy/paste, is just a weird characteristic of a Table.  It's good to understand how this works because it will help you in a lot of situations when you are using structured references (formulas that reference column names instead of cell addresses).

The general rules for structure reference formulas:

• Dragging the formulas across makes the references behave as though they were relative references (B2:B5).
• Copying & pasting the formulas across makes the reference behave like absolute references ($B$2:$B$5).

Checkout my other article on Absolute Structured References in Excel Table Formulas for more details on this behavior.  I also have a free add-in that will help you convert your formulas between absolute and relative references.

Solution #2 – Use AutoSum to Quickly Create Subtotal Formulas

The AutoSum feature can be used to create the SUBTOTAL formulas in the Total Row.

1. Simply select the cells in the Total Row that you want to create sum formulas for.
2. Then press the AutoSum button in the Home tab of the ribbon.

This is a very fast and easy solution because it does NOT require the mouse.  Instead, you can use the keyboard shortcut Alt+= (hold down Alt, then press the equal sign key).

If you have a table with lots of columns, this will be much faster than dragging the formula across with the mouse.  This is especially true if you are using a laptop trackpad for the mouse.

The one drawback to this method is that AutoSum does not use structured referencing.  That means it creates a formula that contains the cell references instead of column references.  For example, AutoSum will create a formula that looks like this =SUBTOTAL(109,B2:B6).  It contains the cell addresses instead of the column name.

This might actually be an advantage because you could then use copy/paste to paste this formula to other cells in the Total Row.

Bonus Tip

The keyboard shortcut to turn the Total Row on/off is Ctrl+Shift+T.  You must have a cell selected in any part of the Table for this to work.

Additional Reading

If you are new to Tables, checkout my full video tutorial on Excel Tables that will explain all the benefits and features of Tables.  Even though there a few quirks and limitations, Excel Tables are awesome and will save you a lot of time!

Learn how create Absolute Structured References in Excel Table Formulas.

A whole book on Tables! My friends Zack Barresse and Kevin Jones have written THE GUIDE to everything Tables.  This is a great read if you are looking to master Excel Tables. It also includes tons of information and resources for writing VBA for Tables.
eBook Version – Excel Tables Book
Paperback version – Excel Tables Book

Submit Your Question

You can submit your Excel or VBA question here and I will do my best to get it answered.  Don't forget to subscribe to my free email newsletter below to stay updated.

Do you have another way to create the SUBTOTAL formulas in a Table?  Please leave a comment below with any questions or suggestions.