How to Add a Total Row in Excel

When using Excel tables, you sometimes need to calculate the summarized data of all its rows and columns. Whether you need the average, the total sum, the smallest and largest value in the column or something else, you can use the Excel total row to calculate all of those things in just a few seconds. Why bother calculating them one by one when you can take the shortcut? In today’s tutorial, I will show you how to add a total row in Excel, but first off

Wait, what is an Excel Table?

Tables in Excel are a great way to visualize all your data. Putting all the important statistics and values in a tidy, pretty table can help make it easy to read and work with. Plus it is much more manageable than trying to figure out some unstructured data spread across the sheet. Now, you might be asking yourself how to create those Excel tables.

How to convert your data into an Excel Table

First off, let’s start with a dataset similar to this one. Make sure each value is in its respective column and row.

creating a dataset to create a table for the total row

Select the cells you want to format as a table and go to the Home tab. There, from the Styles tab, click the Format as Table button.

how to create the table to add the total row

From the drop-down menu, select the design you think most fits your table.

table designs

Confirm that you’ve selected the correct cells, and your data will be formatted in a table.

confirm the cells for the table you are creating to add total row

Add a Total Row Using Table Design

Now that you have your table, you may be starting to wonder how to show that total row we talked about earlier. To start, select your whole table.

adding total row using table design

Right-click the excel table and go to the Table drop-down menu. From there, you can click the Excel total row button and a new row will appear underneath your table.

how to add a total row in excel

Without additional settings, it will automatically calculate the total sum of the salaries in my table. To make it calculate something else, select the cell you want to work in and click on the arrow that appears next to it.

dropdown menu of the excel total row

You can select what you want the Totals row to calculate in your table from the dropdown menu. I will use the Average on the Salary increase column to find the average percentage raise, the Min on the Salary one, to see who receives the least in my company, and the Max on the Job experience, to find out who my most loyal employees are (Note that you have to remove the years in the Job Experience otherwise Excel will treat it as a string and won’t be able to calculate which value is the largest).

The completed table should look something like this.

finished table with a excel total row added

Note, that in the Formula bar we now have the =SUBOTAL formula, instead of the expected AVERAGE, SUM, MIN, MAX etc. Excel uses the SUBTOTAL formula to make those calculations with just one formula, and it even ignores a row if you hide it, as opposed to the other ones that calculate even the hidden rows. The SUBTOTAL formula is also really helpful because you can change what calculation you want it to perform in just two clicks.

Add a Total Row Using a Keyboard Shortcut

After doing this a few times, you might want to speed up the process. The keyboard shortcut for that is Ctrl+Shift+T and I will also show you the codes for the different functions in the formula. When typing in the SUBTOTAL, the first argument you have for that formula is the code for the function you use. The codes as follows are:

101 – AVERAGE – Calculates the average value of the rows from that column

102 – COUNT – Counts all the cells with a number or a date

103 – COUNTA – Stands for count all; Counts all the cells that are not blank

104 – MAX – Finds the largest values from that column

105 – MIN – Finds the lowest value from that column

106 – PRODUCT – Multiplies all the values from that column

107 – STDEV – Calculates the standard deviation

108 – STDEVP – Calculates the standard deviation for the whole population

109 – SUM – Calculates the total sum of that column

110 – VAR – Estimates the variance of a sample of data

111 – VARP – Calculates the variance of an entire population of data

How to Insert a Total Row in Excel by Adding a Row and Formula

The easiest way how to insert a total row in excel is actually by manually adding it and using a formula. To do that, after formatting your cells as a table, you can then expand the table by a row down. In that row, you can use any of the above formulas to find the sum, average, count, min and max, and so on, just by using their respective formulas. It is way quicker than formatting the whole formula and then using the Total Row and all its respective functions and arguments for the SUBTOTAL function.


The Excel Total Row is an extremely useful and easy to use function in Excel, that can save you a lot of hassle instead of manually inserting the formula in each column, you can do so in a click of a button. It has a great amount of formulas built within it, which are easily added by using the dropdown menu. Nevertheless, most people find that typing the formulas by hand is a quicker and more reliable way than using the Total Row functions.

Leave a Reply

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