How to Add a Total Row to a Google Sheets Data Table

Most of you might have started using Google Sheets data tables by now. How do you add a total row to one?

Adding a total row below a data table in Google Sheets is quite easy. You need to use structured references in the formula for that row.

Example of a total row added below a data table in Sheets

Steps to Add a Total Row to a Data Table

Project NameQ1 Billing
Project Alpha10500
Project Beta22000
Project Gamma6000
Project Delta12250
  • Step 2: Select the range A1:B5 and click Format > Convert to Table.
Creating a data table in Google Sheets
  • Step 3: Navigate to cell B6 where you want to apply your formula. Here, we will use the SUM function.
  • Step 4: In cell B6, enter =SUM(TABLE and Google Sheets will list available structured table references.
  • Step 5: Select the reference that matches the table name and field label. Based on the sample data, it will be =SUM(Table1[Q1 Billing]).
Totaling a column using structured table references
  • Step 6: Select it and hit enter. This will immediately format the last row as a total row that stands out from the rest of the table with borders.

You can make it more appealing by choosing a different font and making the text bold. Additionally, enter the label “Total” in cell A6.

This is how we can add a total row to a data table in Google Sheets.

Things to Know:

Once you have completed the above steps, adding data below the table will not be included in the table. How do you include them?

  1. Remove the formula from the total row of the data table. Alternatively, you can delete the row as well.
  2. Navigate to any cell within the table.
  3. Click Format > Alternating Colors.
  4. Uncheck the Footer in the sidebar panel and click Done.
  5. Click the drop-down next to the table name at the top left corner of the table and select Adjust Table Range.
  6. Enter the range that includes the new rows, such as A1:B8, in the small window that opens, and click OK.

This will ensure that new data added below the table is included in the table.

Can You Add a Subtotal Row to a Data Table in Google Sheets?

Unfortunately, you cannot directly use structured table references to sum a specific range of rows within a table in Google Sheets.

As a side note, structured references allow you to refer to data within a table using column names instead of cell addresses.

To add subtotal rows within a data table, you should use traditional cell references, but be aware of the following implications:

  • The added subtotal rows will move when you apply sorting or column grouping.
  • Use the SUBTOTAL function instead of functions like AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, and VARP in the subtotal rows. Otherwise, when you add a total row to the table at the bottom, these subtotal rows will be included. Also, remember to use the SUBTOTAL function in the total row as well.

Example:

In the following table, assume you want to add a subtotal row below the categories “Apple” and “Orange,” and insert a total row at the end.

Sample table with subtotals added below each category
  1. Right-click on row #3 (the last row in the first category) and select “Insert 1 row below”.
  2. Right-click on row #7 (the last row in the second category after inserting the above row) and select “Insert 1 row below”.
  3. In cell D4, enter =SUBTOTAL(109, D2:D3) to total the first category. The function #109 is used for summing a range.
  4. To get the subtotal for the second category, enter =SUBTOTAL(109, D5:D7) in cell D8.
  5. Enter =SUBTOTAL(109, Table1[Amount]) in cell D9.
  6. Enter the labels “Subtotal” in cells A4 and A8, and “Total” in cell A9.
  7. Bold the total and subtotal rows.
Example of a subtotal row added below a data table in Sheets

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.