How to Add a Total Row in Excel Table​ (5 Simple Ways)

In Excel, you get a built-in option to add a Total row with functions like Average, Sum, Count, Max, Min, etc. This way, you get to calculate data within the table without manually typing separate formulas.

We use the Table Design tab to add a Total row at the bottom of a table. However, the functions of this built-in feature only use the visible cells while calculating. You still need a manual approach to include hidden cells in your calculations.

In our sample dataset, we have columns for Product names and their sales revenue for January, February, and March. We’ll select a cell in the range and press  Ctrl  +  T  to turn it into a table.

To quickly add a Total row at the bottom of your table, use a keyboard shortcut in the following way:

➤ Select any cell inside the table and press  Ctrl  +  Shift  +  T   on your keyboard.
➤ Excel will now add a Total row with each cell containing a drop-down. Also, the sum for the final number column is calculated automatically.

➤ To activate a drop-down, select the Total row cell, press and hold the  Alt  key, and click the downward arrow button (ALT + ↓) on your keyboard.

➤ You can use the downward or upward arrow ( ↑ or ↓) keys to go up and down and choose any of the following functions from the drop-down:

• Sum: Adds up all the numbers in the column.

• Average: Finds the mean (sum of values ÷ number of values).

• Count: Counts the number of entries (can be all items or only numeric values).

• Count Numbers: Returns the number of cells in that column that contain numeric values (ignoring text, blanks, and errors).

• Max: Shows the largest value in the column.

• Min: Shows the smallest value in the column.

• Standard Deviation (StdDev): Measures how spread out the values are from the average.

• Variance (Var): Shows how much the values differ from the average (the square of standard deviation).

Follow the steps given below to readily add a total row using the Table Design tab:

➤ Click on any cell in your table and go to the Table Design tab on the ribbon.
➤ In the Table Style Options group, check the Total Row box.

➤ A new row appears at the bottom with drop-down menus in each column. In our dataset, the last cell contains the sum of the column.

➤ To fill the remaining cells, click on the drop-down in each cell and choose a function as necessary.
➤ Here’s the sum for each number column in our dataset:

➤ To remove the Total row, go to the Table Design tab >> Table Style Options group >> uncheck the Total Row box.

When you right-click on a cell, the Context menu appears with several options. Below is the process of using it to add a Total row:

➤ Right-click anywhere inside the Excel table. From the Context menu, click on the arrow sign beside the Table option.

➤ Choose Totals Row and the Total row will appear instantly. Use the drop-downs to select your preferred function.

The Quick Analysis tool is a small button that appears at the bottom-right corner of a selected range of data. When you click the button, it opens a dialog box with the Totals tab. Here’s how to use it to add a Total row:

➤ Click and drag to highlight your table range (A1:D10). Look for the Quick Analysis button at the bottom-right corner of the selection. If it doesn’t appear, press  Ctrl  +  Q  .

➤ Once you find the button, click on it to reveal multiple tabs like Formatting, Charts, Totals, etc.
➤ Press the Totals tab and it will expand more icons for calculations like Sum, Average, Count, Running Total, % Total, etc. Click on the Sum icon (or any other as needed).

➤ Excel will now add a Sum row at the bottom with the sum of the numbers in each column.
➤ You can edit the cell containing the text Sum, type Total, and press Enter.

While the above-mentioned methods are quick and easy, they don’t include filtered or hidden rows in the calculations. Therefore, if you want to include the hidden rows, you need to add a Total row manually. Here’s how:

➤ Go to the first empty row below your table and type Total. You can bold the row to make it stand out.

➤ Now, follow any of the methods given below:

Create Total Row with AutoSum

➤ Select the Total row cell(s) where you want the calculated values and to the Home tab. In the Editing group, click on the AutoSum (Σ) drop-down.
➤ Select any of the given functions such as Sum, Average, Count Numbers, Min, Max, etc. We selected Sum.

➤ It will sum up the numbers in the columns (without any drop-downs) as shown below:

Create Total Row with SUM

➤ In the first empty row under a numeric column, insert the following formula:

➤ Here, B2:B10 is the range containing the numbers we want to sum. Change the range according to your dataset. You can also replace SUM with other functions like AVERAGE, MAX, MIN, etc., to perform different calculations.
➤ Press Enter.

➤ Use the fill handle  (+ sign on the bottom corner of the cell) to drag the formula across to autofill the remaining cells of the row.

Frequently Asked Questions

How do I add new rows and push the Total row down in Excel?

When you insert a new row above the Total row, Excel automatically pushes the Total row down to stay at the bottom. To add a new row, select the last row before the Total row and press the TAB button on your keyboard.

How do I get the Total row at the top of a table in Excel?

Excel only allows the Total row at the bottom by default. To show it at the top, first, add the Total row at the bottom using the Table Design tab. After that, Copy or Cut the Total row and paste it above your data.

Can I remove the table Total row drop-down in Excel?

No, you can’t fully remove the drop-down menus. However, when you use a manual formula

(e.g., =SUM(B2:B10)) or use the AutoSum button to create the Total row, the drop-downs aren’t added.

Concluding Words

A Total row recalculates every time you add a new row or remove one. If you want to change the row function from Sum to Average (or any other function), click on the drop-down of that cell and choose Average. Finally, you can switch the Total Row on or off without losing any data in your original table.