Table Total Row

The Table feature of Excel remains one of my favorites. This post explores one very specific aspect of the feature, the total row.

Overview

One of Microsoft’s greatest gifts of Excel 2007 was the introduction of the table feature. It allows us to convert an ordinary range into a table (Insert > Table, or, Ctrl+T). Once the data is stored in a table, we can use the table’s name in our formulas. Since tables auto-expand to include new rows, new transactions are automatically included in our formulas. This auto-expansion property helps us build more reliable workbooks.

Another special property is the total row. The total row can be toggled on, or off, at any time to show, or hide, table totals. There are a few interesting points to note about the total row, so, let’s dig into the details.

Total Row

It is easy to turn the total row on and off. We just use the Total Row checkbox in the TableTools > Design ribbon tab, as shown below:

20140605-a

Customize Totals

When you turn on the total row, Excel does its best to determine which column you want to add. If Excel displays a total for a column that doesn’t need one, simply select the drop-down control in the total row and select none.

The drop-down also contains other math functions, such as Average and Count. So, it is pretty easy to change the math used by the total row for any given column, as shown below:

20140605-b

Filters

Tables come with filter controls on the header row. The nice thing is that when you apply one of the filters, the total row updates. Specifically, it will include visible rows and exclude hidden ones. This makes it easy to quickly analyze the data, as shown below:

20140605-c

Adding Rows

Even with the total row on, it is easy to add new rows to the table. If you are going to manually key the new rows, just use the Tab key and when you get to the last column, Excel will automatically insert a new blank row for your entry, as shown below:

20140605-d

If you are going to add a bunch of rows, you can resize the table by dragging the sizer control in the lower-right corner of the total row, as shown below:

20140605-e

If you are going to paste rows, just be sure you paste them into the total row. Excel will push the total row down and insert the new rows accordingly, as shown below:

20140605-f

The table feature is incredible, and I use it all the time. It improves efficiency and reduces errors…thanks Microsoft for this gift!

If you have any other fun table tricks…please post a comment below!

Additional Resources

Sample File: TotalRow

 

This article was written by Jeff Lenning

10 comments:

  1. Asad Akhtar
    Reply

    How many Total Rows for Microsoft Excel 2007?

  2. Ricky
    Reply

    how do I place the total for my table in another cell? for example, I want a diagnostic worksheet that shows the total amount of a column and compare that to my individual pieces to ensure that all my totals agree. if I use =table[amount], I either get the #VALUE or excel just totals the very first item ignoring the rest of my table. I had it working using the above formula, but all of a sudden it stopped working properly. Thanks.

    1. jefflenning Post author
      Reply

      Ricky,

      You have a couple options here. One is to write a formula in any cell that will sum the column, such as =SUM(table[amount]). Another option would be to use the Total Row feature of the table, and as long as the total row is displayed, you can retrieve its value into any other cell with its structured table reference, such as =table[[#Totals],[amount]].

      Hope it helps!

      Thanks
      Jeff

  3. Carrie
    Reply

    I have a table with many columns and have the total row displayed. Is there a quick way to select the sum function in the total row for multiple columns? Right now I am going into each cell’s total row drop down menu to select the sum function.

    1. Kurt LeBlanc
      Reply

      Hey Carrie

      I couldn’t find an easy way without a macro. To find a basis for your code, I suggest recording a macro of you changing 1 total and then you can fine-tune it.

      Let me know if I can be of more help!
      Kurt LeBlanc

      1. Bill
        Reply

        You can use the fill handle (little black square on the bottom right of the cell) to copy across totals. Just select sum on the first one and fill it across to the right.

  4. Ahmad
    Reply

    I need to add multiple total rows from these…because I am referring to the entire table through a VBA to be shown or hidden based on a checkbox condition. So, I need to add multiple total rows that belong to a “format as a table” table.

    Thank you.

    1. Jeff Lenning Post author
      Reply

      Hi! Although I’m not aware of a way to add multiple total rows into the table, as part of the table object, hopefully someone can post a comment to help out.
      Thanks
      Jeff

  5. Michelle
    Reply

    What is the keyboard shortcut for accessing the drop down menu functions in the total row, so I don’t have to stop and grab the mouse?

    1. Jeff Lenning Post author
      Reply

      Alt+Down Arrow 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *