Working with tables in Excel: how it works

Turn your list into an Excel table with Ctrl + T. The table grows with new rows, gets filter buttons and a total row, and your formulas use column names: =SUM(Sales[Total]).

Try the first exercises for freeIn the lesson Excel tables

What is an Excel table?

An Excel table is a list that Excel recognises as one whole. It gets formatting with alternating colours, filter buttons in the header row and a name of its own, such as Sales. If you type a new row under the table, it belongs to it straight away: the formatting, the filters and the formulas come along automatically.

In formulas, you refer to columns by their name instead of by cell addresses. =SUM(Sales[Total]) adds up the whole Total column, however many rows are added. A reference like that is called a structured reference.

When do you turn a list into a table?

Your list grows every week, and your totals have to include the new rows without you changing the formulas.
You want a quick total, average or count under your list: turn on the total row.
You want a PivotTable or a chart that grows with new data.
You want formulas that say what they do: =[@Price]*[@Quantity] instead of =B2*C2.

Creating a table, step by step

This is how to go about it:

Make sure you have a list with one header row, with no empty rows or columns.
Click one cell in your list and choose Insert > Table, or press Ctrl + T.
Check the range Excel suggests, leave My table has headers ticked and click OK.
Click in the table. On the Table Design tab, give the table a name, such as Sales, and tick Total Row for a total at the bottom.
In the first empty column next to the table, type a formula. If you click the cells, Excel writes the column names for you, and the formula appears in all the rows straight away.=[@Price]*[@Quantity]

Formulas with column names

=SUM(Sales[Total]) adds up the whole Total column of the Sales table, even when rows are added. Inside the table, [@Price] means the price from the same row: =[@Price]*[@Quantity].

Where it often goes wrong

Spilling formulas such as UNIQUE and FILTER don't work inside a table. Put them next to it.
A table can't contain merged cells.

Want to try it yourself?
Practise this in the lesson Excel tables. The first exercises are free.

Try the first exercises for free

Frequently asked questions

How do I give my table a name?

Click in the table and type a name in the Table Name box on the Table Design tab, such as Sales. Don't use spaces.

How do I turn it back into a normal range?

Choose Convert to Range on the Table Design tab. The formatting stays, but the table no longer grows with new rows.