Add a total row using the Table feature

The fastest way to add a total row in Excel is to convert your data into a Table, then turn on the total row option. Excel will automatically sum numeric columns and count text columns, and you can change what calculation each column uses.

Select any cell in your data range, then go to the Home tab and click Format as Table. Choose a table style from the gallery. Excel will detect your data boundaries — if it guesses wrong, you can adjust the range in the dialog that appears. Click OK.

With the table selected, go to the Table Design tab (this appears only when a table is active). Check the box next to Total Row. Excel adds a row at the bottom with automatic calculations: SUM for numbers, COUNT for text, and SUBTOTAL for filtered data.

Key Takeaways

  • Converting data to a Table and enabling Total Row is the quickest method and automatically chooses the right calculation for each column type.
  • You can change what calculation a total cell uses by clicking the cell and selecting a different function from the dropdown menu.
  • The SUBTOTAL function used in total rows automatically ignores hidden or filtered rows, so your totals stay accurate when you filter data.
  • If you do not want to use a Table, you can manually enter SUM, AVERAGE, COUNT, or other formulas in a row below your data.

Change the calculation for a specific column

When you add a total row to a table, Excel picks a default calculation for each column. Numbers get SUM, text gets COUNT. You can change this for any column by clicking the total cell and selecting a different function.

Click the cell in the total row under the column you want to change. A small dropdown arrow appears on the right side of the cell. Click the arrow to see the available functions: Sum, Average, Count, Count Numbers, Max, Min, and others. Select the one you need. The cell updates immediately.

If you need a calculation that is not in the dropdown list, click the cell and type a formula directly. For example, you could enter =AVERAGE(B2:B100) to average a range, or =MAX(C2:C100) to find the highest value. This overrides the automatic total.

Add a total row without converting to a Table

If you prefer not to use the Table feature, you can add a total row manually by inserting a blank row at the bottom of your data and entering formulas yourself.

Click the row number of the first empty row below your data. Right-click and select Insert to add a new row. In the first cell of this row, type a label like "Total" if you want one. Then move to the next cell and enter a formula. For a sum, type =SUM(B2:B100), replacing B2:B100 with your actual data range. Press Enter and the formula calculates.

Copy the formula across to other columns by clicking the cell with the formula, then dragging the small square in the bottom-right corner of the cell to the right. Excel adjusts the column references automatically. You can also use other functions like AVERAGE, COUNT, MAX, or MIN instead of SUM.

Total row behavior when you filter or sort data

When you use a Table with a total row, the total row stays at the bottom even when you sort or filter the data. The calculations update automatically to reflect only the visible rows.

This works because Excel uses the SUBTOTAL function behind the scenes, which ignores hidden rows. If you manually entered formulas using SUM, those formulas would still include hidden rows in their calculation, giving you an incorrect total. This is one reason the Table feature is useful for data you plan to filter.

If you sort your table by clicking a column header, the total row remains at the bottom. If you filter by clicking the filter button in a column header, only the rows that match your filter show, and the total updates to match. To clear a filter and see all rows again, click the filter button and select Clear Filter.

Delete or hide a total row

To remove a total row from a Table, go to the Table Design tab and uncheck Total Row. The row disappears immediately and your data returns to its original state.

If you added a total row manually by inserting a row and entering formulas, right-click the row number and select Delete to remove it. If you want to keep the row but hide it temporarily, right-click the row number and select Hide. The row is still there but does not display. Right-click and select Unhide to show it again.

Common total row functions and when to use them

Excel offers several calculation options for total rows. Sum adds all values in a column and is the default for numeric data. Average calculates the mean of all values. Count counts how many cells contain any data, while Count Numbers counts only cells with numbers, ignoring text and blanks.

Max finds the highest value in a column, and Min finds the lowest. StdDev calculates standard deviation for statistical analysis. Most of the time you will use Sum or Average, but Max and Min are useful when you need to see the range of your data at a glance.

If you are working with filtered data and using manual formulas instead of a Table, use SUBTOTAL instead of SUM. The syntax is =SUBTOTAL(9, B2:B100) where 9 means sum. The number 109 means sum and ignore hidden rows. This ensures your total stays correct when data is filtered.

Frequently Asked Questions

Does the total row update automatically when I add new data?

If you are using a Table, yes — the total row expands automatically when you type in the row below it. If you manually entered formulas, you need to edit the formula range to include the new rows. For example, change =SUM(B2:B100) to =SUM(B2:B150) if you added 50 new rows.

Can I have multiple total rows in one table?

The Table feature allows only one total row, and it always appears at the bottom. If you need subtotals for groups within your data, use the Data menu's Subtotals feature instead, which inserts subtotal rows between groups and a grand total at the end.

Why does my total row show the wrong number?

If you are using a Table, check that the correct function is selected for that column. Click the total cell and verify the dropdown shows the function you want. If you used a manual formula, make sure the range includes all your data rows. Hidden rows are included in SUM but ignored in SUBTOTAL, so check whether rows are hidden.

Can I format the total row differently from the rest of the table?

Yes. Click any cell in the total row and use the formatting tools on the Home tab to change the font, background color, borders, or number format. You can also right-click and select Format Cells for more detailed options. The Table style will still apply, but your custom formatting takes priority.