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