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

 

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

19 Comments

  1. Asad Akhtar on July 3, 2015 at 4:44 am

    How many Total Rows for Microsoft Excel 2007?

  2. Ricky on September 24, 2015 at 11:49 am

    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.

    • jefflenning on October 8, 2015 at 12:16 pm

      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. Siu on January 25, 2016 at 8:23 pm

    Hello,

    Do you know that if there is a way I can insert multiple total rows? Wow, I really like your tutorial. Are those pictures GIFs?

    Thanks,
    Siu

  4. Kat on May 12, 2016 at 10:25 am

    Hi Jeff,

    How come the validation rules don’t seem to copy to the new rows (when you add a new row)? Using your example, if your column D restricts entries to a predefined list (ex. Postage, Meals, Internet) and then you tab to a new row after entering an amount in column H, the new row’s column D no longer has any restrictions. How do I enforce my validation rules to newly created rows?

    Thanks,
    Kat

  5. Carrie on June 7, 2016 at 12:40 pm

    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.

    • Kurt LeBlanc on July 22, 2016 at 2:01 pm

      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

      • Bill on December 7, 2016 at 1:46 am

        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.

  6. Didier on January 31, 2017 at 3:36 pm

    I am having trouble enabling the total row option. What can be done to resolve this?

  7. Christine Kmiec on March 13, 2017 at 6:32 am

    How do I add a column that is not in the rightmost position? I have a spreadsheet with months of the year and the 2nd rightmost is “Change from previous month” and the rightmost is “Change from previous year” so the 3rd column from the right should be the current month.

  8. Lauren DaSilva on April 2, 2017 at 11:41 am

    I am trying to add in a Total Row feature but Excel is sending message saying there is not room, remove rows or any data that will be able to fit it the row in but I am required to have exactly 25 rows in my project. I made the font smaller, not working. Help?

  9. Ahmad on June 28, 2017 at 4:50 am

    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.

    • Jeff Lenning on June 29, 2017 at 5:13 am

      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

  10. Michelle on September 28, 2017 at 4:04 pm

    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?

    • Jeff Lenning on September 28, 2017 at 6:36 pm

      Alt+Down Arrow 🙂

  11. adnan on December 19, 2018 at 11:54 pm

    Is there a way we can sum entire total row for all columns in the table at once? Instead of selecting sum from the drop-down for every column?

  12. RAJESH R BHATE on September 30, 2020 at 1:53 am

    I am facing a problem with Excel table. The Total Row is added but the drop down doesn’t appear. How to add the drop down for Total Row ?

  13. Dylan on October 21, 2020 at 9:55 pm

    same man… I need to figure this out too for a schooln project. ):

  14. Md. Razib Hossain on November 27, 2022 at 12:07 pm

    Its really interesting for me to know various formola about excel. Thanks.

Leave a Comment