How to Add Total Row in Excel: Best 2 Methods (2024)

Do you work with large datasets in Excel?

Adding a total row can be a lifesaver for calculating important statistics in your table.

In this article, we’ll explore the two best methods for adding a total row in Excel. These are sure to save you time and boost your productivity 😉

Get ready to streamline your data analysis and impress your colleagues with your Excel skills!

You can also download the sample workbook here to practice adding total rows in real-time.

Table of Contents

Add a total row to a table

Adding a total row to a table is pretty straightforward. And adding it will take you less than 5 seconds.

Suppose we have the following table. It contains information about the monthly sales of different companies.

Sample data for how to add a total row in excel.

We want a total row at the bottom showing the net amount acquired in each month 📅

To add a total row at the bottom,

  1. Select the table.
  2. Go to the table Design tab under Table Tools.
  3. Under the Table Style Options section, mark the Total Row checkbox.
Select Totals row option to create an entire row at the bottom.
  1. A total row will be inserted at the bottom of your table.
Total row inserted - includes hidden table rows

As evident, the middle columns of the table appear empty. We have to insert functions in these cells manually.

To sum up each month, we will apply the SUM formula. You can choose different functions from the drop-down menu.

  1. Click on the small arrow in column B in the Total row. The drop-down list will appear. Select SUM.
Select the subtotal function from the drop down menu
  1. Excel will apply the function to column B as:
Sum of values in Column B

We will do the same with column C:

The structured reference formula applied to all the cells in the last row of the table.

The SUM function is applied, and Excel shows the total sales made in each month.

It’s that easy. Try it yourself using the sample workbook 😀

Add a total table row with a shortcut

You can add a total row at the end of your table using this shortcut too.

Say, we have the following table. It contains information about the strength of different school classes each year.

Sample data for adding row using a shortcut

We want to find the average class strength each year. We will add a total row at the bottom of this table to average the values using a keyboard shortcut.

To do that,

  1. Select the table.
  2. Press CTRL + SHIFT + T
  3. The Total row appears.
Using keyboard shortcut to add the Total row

Note that we want to find the average here. But the total row appears with a SUM of column C. The format is also set to Currency here instead of General.

This setting is by default – to change it, simply select the desired function from the drop-down. and change the format from the options.

Kasper Langmann, Microsoft Office Specialist

Upon selecting the Average function from the drop-down menu, Excel shows the result as:

Average of Column C

Apply the same function to the remaining cells as:

Average of all columns in the Total row

Pretty cool, no? 🤩

Add a total row with formulas

Another easy way to add a total row at the bottom of Excel tables is by using Excel formulas.

This might take slightly more time than the methods mentioned above, but it works just fine. Let’s see it below.

Suppose we have the following table. It shows the increase in prices of certain products over the years.

Sample data for adding total row using a formula

We want to find the average unit price of the products mentioned. We can quickly do that by creating a Total row at the bottom of the table.

To do that,

  1. Select the row next to the last row of the table.
  2. Type in Total.
Border line under the last row of table

Note that as soon as you press Enter after typing Total, the table adds a border to the total row. The border is extended to the cells in front of the Total row as well.

Also, note that the SUM of column C did not appear automatically, as seen earlier. That’s because we are adding the values manually this time, so we need to apply all the formulas ourselves.

Kasper Langmann, Microsoft Office Specialist
  1. In column B, add the AVG formula as

=AVERAGE(B2:B10)

  1. Press Enter.
Average of the prices

The AVERAGE of the column appears in the last cell.

To copy the formula to the remaining rows, click the Fill Handle and drag it to the other cells.

Average of all cells along the Total row

The formula appears in all cells. You can now format the new table row as you like.

This is yet another easy and quick method to add a total row. It is most useful when you can’t add a total row using the shortcut or Design tab for some reason.

Make sure to give it a try using the sample workbook.

That’s it – Now what?

In this article, we saw how to add a total row at the end of a table using Table tools. We also saw how to do the same using formulas and some easy shortcuts.

Adding a total row in an Excel table is very easy, and you should be able to master it once you practice it a couple of times.

Apart from these cool shortcuts and tools, Excel has many other awesome features. The best of these is the Functions feature.

You can try practicing some core Excel functions like VLOOKUP, IF and SUMIF, and some others.

You can learn them in my 30-minute free email course only at the cost of your email address. So join now to learn this and much more.

Other resources

Did you enjoy reading this article? If yes, then we’re sure you’d love to read more.

Try similar topics: Delete Blank Rows, Insert Multiple Rows, Unhide Rows in Excel, and more.

Frequently asked questions

To add a total row to a table in Excel and display the average, follow these simple steps:

  1. Select the entire table, including the column headers.
  2. On the Table Tools tab, click the Design tab.
  3. In the Table Style Options group, check the Total Row box.
  4. A new row will appear at the bottom of the table, labeled Total.
  5. Click on the cell in the Total row where you want to display the average.
  6. In the function drop-down list, select average.
  7. Excel will calculate the average of the column. And display it in the selected cell in the total row.