Sum All Columns in The Total Row of an Excel Table + Video
6

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.

Sum Each Column of the Total Row in Excel Table

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

How do I automatically add the sum to all the columns in the Total Row of the Table?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.

Excel Total Row Copy Paste Incorrect Results

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).

Excel Total Row Drag Formula Across Total Row Relative Reference

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.

Excel AutoSum to Create SUBTOTAL Formulas in Total Row

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.

Total Row Keyboard Shortcut Excel Hover Screen Tip

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.

Excel Tables eBook - Zack Barresse - Kevin Jones
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.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 6 comments
Shafaq - April 12, 2017

Hi,
The total Row option in excel tables provides the sum of columns (total of each column appears at the bottom of the table. what if someone needs the sum of each row in a table at the right side of the table

Reply
    Jon Acampora - April 18, 2017

    Hi Shafaq,
    we can use a SUM formula and the AutoSum feature for this. First, select the cell directly to the right of the Table. This will be the first cell in the data row, not the header row. Then press Alt+= on the keyboard. Hold the Alt key, then press =. A SUM formula will be created. Press Enter to fill the formula down all rows of the Table.

    Reply
Rajkumar - March 24, 2017

How many cells in computer

Reply
S.GUNAA - January 3, 2017

how to change total row,%,
how to add rounded off + or –

Reply
Don - September 30, 2014

Jon-
this is a great tip! I could not find anyone with a solution to adding totals across multiple columns in a table so these are great solutions. keep up the great work!

Reply
    Jon Acampora - October 4, 2014

    Thank you Don! I’m glad to hear it helped, and that you are using Tables. I just added a link to a book my friends wrote about Excel Tables. It’s a great resource that includes a lot of VBA code for Tables.

    Reply

Leave a Reply:

Grab My Free Cheat Sheet!

VLOOKUP Essentials Guide​

Vlookup Essentials Guide 317x407
Plus: Free Video Training Series on the Lookup Formulas!
x