Working with excel tables can significantly improve your spreadsheet solutions. A total row is one very beneficial table feature that will help you see quick summaries of the columns in your table such as counts, sums, and averages. The totals in the total row are generated with SUBTOTAL formulas and will automatically update when any information in the table is added or updated. When you create a new Excel table the total row is disabled and you will need to enable this useful feature for each table. This post will show you how to enable the total row on your Excel tables.
Add a Total Row to a Table with the Design Tab
This tutorial will assume you already have your dataset in an Excel table object. When you select a table object a new Table Design tab will appear in the Excel ribbon. This is where you will find all the options available for your table, including the option to add a total row to the table. Here’s how you can enable the total row from the Table Design tab. This will add a new row to the end of the table. This will have a distinctive style compared to the rest of the table, depending on what table style is selected. This new row will contain the word Total on the left-most column when your table contains two or more columns. The rightmost column will contain a SUBTOTAL formula that either results in a count or sum type of aggregation depending on if the column is text or numbers. You can easily add a total to any of the columns by selecting the cell in that total row to reveal a dropdown selection. Click on the dropdown and select the type of total to create. This inserts the required SUBTOTAL formula.
Add a Total Row to a Table with a Keyboard Shortcut
The table total row is a popular feature that you will likely want to enable on all your tables. Thankfully, there is an easy keyboard shortcut you can use to quickly add the totals to your table. The total row will be added. There’s another handy keyboard shortcut worth knowing to select different aggregation types in your table. Select any cell in the total row and press the Alt + Down keys to activate the dropdown menu to create a new total. Then use the Up or Down keys to navigate through the dropdown selections and Enter to make a selection.
Add a Total Row to a Table from the Right Click Menu
The table row option is also available from the right-click menu. This will add the total row to your table.
Add a Total Row to a Table with the BYCOL Function
One negative aspect of the total row is that it will only display at the bottom of the table. This means you need to scroll down to see the row if you have a larger table. So you might want to skip the total row feature in your table and create your own at the top using a formula. The above formula creates an array of SUBTOTAL functions for each column in the Orders table. The BYCOL function allows you to reference the full table and create a calculation for each column. The LAMBDA function then is used to define what calculation is performed for each column. In this case, a SUBTOTAL that gives the sum of the column is the calculation. The formula is dynamic and will expand or contract the array based on the columns in the table. If you add or remove a column in the table, the total array will adjust accordingly. This formula can also be placed above your table so it’s always in view. In fact, you can even place this on another sheet if you want.
Add a Total Row to All Tables with VBA
Unfortunately, there is no option to enable the total rows for all tables in your workbook. There is also no option to set the total row by default when you create a table. But you can use VBA for this to turn on or off the total rows in all the tables in your workbook. The above code will loop through all the sheets in the workbook and then all the tables in the sheet. It then sets the ShowTotals property to True which turns the total row on for the table. This code will also work to turn off the total row, you just need to change the tbl.ShowTotals = True line of code to tbl.ShowTotals = False. Follow these steps to use this VBA code. If you don’t see this tab, you can enable the Developer tab from the Excel options menu. Alternatively, you can press the Alt + F11 keyboard shortcut to open the visual basic editor. Now you can run this VBA macro to toggle on or off the total row for all the tables in your workbook.
Add a Total Row to All Tables with Office Scripts
Another way to turn on the total row across all your tables at once is with Office Scripts. Unlike VBA, this will also work in Excel online. The above Office Script code will loop through all the tables in the workbook and set the setShowTotals() property to true. This can also turn all the total rows off with the line tbls[i].setShowTotals(false);. Here’s how to use this Office Script code to toggle the total row in all your tables. The Office Script is now saved to your OneDrive storage and can be used from any Excel workbook saved in SharePoint or OneDrive. You can then follow these steps to run the script. The code will execute and add total rows in all the tables!
Conclusions
Total rows are a great table feature to quickly see basic statistics, but there is no way to make enable them by default. Turning on the total row is an quite easy step and you can even customize the summarization type within the row. You can use a VBA or Office Script solution to quickly toggle on or off the total rows across all your tables in a workbook. Do you use the total row in your tables? Let me know in the comments!
