Do you want to add a total row to your Excel table?

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.

1. Select the table to which you want to add the total row.
2. Go to the Table Design tab.
3. Check the Total Row option found in the Table Style Options section.

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.

1. Select any cell inside your table.
2. Press the Ctrl + Shift + T to toggle on or off the total row in 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.

1. Right-click anywhere in the table.
2. Select the Table option from the menu.
3. Select the Totals Row option from the submenu.

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.

= BYCOL ( Orders, LAMBDA ( col, SUBTOTAL ( 9, col ) ) )

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.

Sub AddTotalRow()

Dim ws As Worksheet
Dim tbl As ListObject

For Each ws In ActiveWorkbook.Worksheets
    For Each tbl In ws.ListObjects
        tbl.ShowTotals = True
    Next tbl
Next ws

End Sub

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.

1. Go to the Developer tab.
2. Click on the Visual Basic option in the Code section of the ribbon. This will open the visual basic editor in another window.

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.

3. Go to the Insert menu of the visual basic editor.
4. Select the Module option. This will create a new module where you can place the code.
5. Paste the VBA code in the new module.

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.

function main(workbook: ExcelScript.Workbook) {
  //get all tables in the workbook
  let tbls = workbook.getTables();
  //loop through each table in tbls and
  //add a total row
  for (let i = 0; i < tbls.length; i++) {
    tbls[i].setShowTotals(true);
  };
};

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.

1. Go to the Automate tab.
2. Select the New Script command. This will open the Code Editor on the right side of the workbook.

3. Copy and paste the above Office Script code into the Code Editor and replace all the default code.
4. Press the Save script button and give the script a name such as Add Total Rows.

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.

5. Go to the Automate tab.
6. Click on the More button to show your script gallery.
7. Select the Add Total Rows script.

8. Press the Run button in the Code Editor window pane.

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!