Add Data to Excel Table with Total Row

Add Data to Excel Table with Total Row

With a formatted Excel table, you can turn the Total Row on or off easily, and it shows at the bottom of the table. Someone asked me how to add data to Excel Table with Total Row showing – they were hiding the totals every time they wanted to add data. You don’t need to do that!

Add Data to Excel Table

If you like to show the Total row in an Excel table, you don’t need to hide that row, when it’s time to add data at the bottom of the table.

Use one of the following methods to add data to Excel Table with Total Row showing. With both methods, the Total row moves down automatically.

Add One New Row

To add a single row of new data:

  • Select the last cell in the last row of data
  • Press the Tab key, to start a new row

This animated screen shot show the Tab method.

tableaddrows01

Add Multiple Rows of New Data

To paste new data at the end of the table

  • Copy the data that you want to add (Ctrl+C)
  • Select the first cell in the Title row, even if there’s text or a formula there
  • Then, press Enter, or Ctrl+V, to paste the copied data

This animated screen shot show the Copy and Paste method.

paste rows at end of table

Excel Table Total Row

After you create a formatted Excel table, it’s easy to turn the Total Row on or off.

  • Select a cell in the Excel table
  • On the Excel Ribbon, under Table Tools, click the Design tab
  • In the Table Style Options group, add or remove the check mark for Total Row

tabletotalrow01

Total Row Calculations

When you first add the Total Row, Excel automatically puts a Subtotal formula into the last column of the total row, to show a sum or count of the items in that column.

  • If the entries are all numbers, the first argument is 109 – SUM.
  • If there are non-numeric entries, the first argument is 103 – COUNTA

total row subtotal formula

Totals for Filtered Data

Excel uses the SUBTOTAL function in the total row, because that function only calculates the total for items that are visible after applying a filter.

You can replace the automatic formula in the Total Row, if you prefer, and you can add Total formulas in other columns.

For example, add an AGGREGATE formula in the Total row — it has more functions and options than SUBTOTAL.

choose location for formula

Totals at Bottom or Top?

When you have a list or table on a worksheet, do you like to show the totals above the table, or at the bottom of the table?

  • NOTE: With the built-in Total Row for Excel tables, there isn’t an option to show those totals at the top, within the table. You can show that Total Row at the bottom of the table only.

In my Excel table, the Total row is usually hidden. If totals need to be shown, I do that with formulas above the table. Or, I build a pivot table on a different sheet, and show the totals there.

More Excel Table Tips

See more Excel Table tips on my Contextures site. There is also a sample workbook to download, so you can test these Table techniques.

Video: Create an Excel Named Table

When you work with lists in Excel, use the built-in Table feature, to make it easy to sort and filter your data. This short video shows the simple steps

___________________________

Add Data to Excel Table with Total Row

Add Data to Excel Table with Total Row

___________________________

One thought on “Add Data to Excel Table with Total Row”

  1. Hallo,
    can you pls helm me?
    I like this macro but i need Excel Drop Down List – Add New Items (without sorting if i add intems)
    thanks and i love the site greetings

Leave a Reply to Jeffrey Cancel reply

Your email address will not be published.

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