In Excel a list of data can stay a plain range of cells or become a table, with the Insert, Table command or the Ctrl+T shortcut. At first sight the difference looks cosmetic: banded rows and filter arrows in the headers. In reality the table changes how formulas, charts, pivots and validation behave as the data grows. For any list that is going to get longer over time, it is one of the most useful habits you can pick up.
Creating a table
- Click any cell in the list.
- Press Ctrl+T or choose Insert, Table.
- Confirm the proposed range and check that "My table has headers" is ticked.
Straight afterwards, on the Table Design tab, give it a meaningful name: Sales, Customers, PriceList. The default name, "Table1", tells nothing to someone reading the formulas.
The list must have unique, non-empty headers and must not contain merged cells. These are the same requirements as a good source for pivots.
Automatic expansion
When you type a new value in the row immediately below the table, or in the column immediately to its right, the table grows to include it. Everything that refers to the table updates accordingly:
- formulas using its columns include the new rows;
- charts based on the table show the new data;
- pivots built on the table pick it up at the next refresh;
- drop-down lists fed by a table column, through a name, include the new items.
With a normal range, by contrast, a formula like =SUM(D2:D500) silently ignores row 501. It is one of the hardest mistakes to spot.
Structured references
Inside and outside the table, formulas can refer to columns by name instead of by coordinates:
| Reference | Meaning |
|---|---|
Sales[Amount] |
all values in the Amount column |
[@Amount] |
the value of the Amount column in the current row |
Sales[#Headers] |
the header row |
Sales[#Totals] |
the total row |
The comparison speaks for itself:
=SUMIFS($D$2:$D$5000, $B$2:$B$5000, G2)
=SUMIFS(Sales[Amount], Sales[Agent], G2)
The second formula reads like a sentence, needs no dollar signs and never has to be updated when the table grows. What is more, if you rename a column header, Excel automatically updates every formula that uses it.
You do not type structured references by hand: when, while composing a formula, you select a table column with the mouse, Excel inserts them for you.
Calculated columns
If you write a formula in the first row of an empty column in the table, Excel applies it to the whole column, and will also apply it to rows added in the future. For example, in a "Total incl. VAT" column:
=[@Amount]*(1+[@Rate])
You no longer have to copy formulas down or remember to extend them when you add data. If you overwrite the formula with a value in a single cell, Excel flags the inconsistency with a small marker: a good check against accidental manual edits.
Total row
From the table tab you can switch on the total row. In each column a drop-down lets you choose sum, average, count, minimum, maximum and other functions. Excel uses the SUBTOTAL function, which has an important feature: it only considers visible rows. If you filter the table to show a single area, the total refers to that area.
Filters, sorting and slicers
The arrows in the headers are always available for filtering and sorting. With Insert Slicer you can add graphic buttons that filter the table with one click: they are handy in sheets consulted by people who are not used to filters.
Limits worth knowing
- Dynamic array formulas cannot spill inside a table. Put them outside, referring to the table's columns.
- Merged cells are not allowed inside the table.
- Sheets shared across different versions or programs may turn structured references into ordinary coordinates. LibreOffice Calc and Google Sheets have similar but not identical tools: Google Sheets has introduced its own tables with references by name, while Calc handles database ranges differently. If the file travels, check how it behaves.
- One table per list. Do not place two tables side by side on the same rows: inserting rows in one can shift the other.
In my work I turn every list that receives new data into a table, even when it is small: it takes a few seconds and saves a whole category of mistakes.
In short
- Ctrl+T turns a list into a table; give it a meaningful name straight away.
- The table expands by itself and carries formulas, charts, pivots and validation with it.
- Structured references such as
Sales[Amount]are readable and need no dollar signs. - Calculated columns apply a formula to every row, present and future.
- The total row uses SUBTOTAL and takes active filters into account.
