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.
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.
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:
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:
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:
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:
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:
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:
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!
Sample File: TotalRow
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?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.
How many Total Rows for Microsoft Excel 2007?
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.
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!
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?
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?
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.
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!
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.
I am having trouble enabling the total row option. What can be done to resolve this?
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.
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?
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.
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.
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?
Alt+Down Arrow 🙂
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?
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 ?
same man… I need to figure this out too for a schooln project. ):
Its really interesting for me to know various formola about excel. Thanks.