If you only learn one Excel habit this year, make it this: turn your lists into Tables. One shortcut, Ctrl + T, gives you formatting, filters, formulas that fill themselves, and ranges that grow automatically when you add data.
How to create a Table
- Click any cell inside your list (it needs one header row and no completely blank rows or columns).
- Press Ctrl + T (Mac: Cmd + T), or go to Insert → Table.
- Make sure “My table has headers” is ticked, then click OK.
- Go to Table Design and give it a clear name, e.g.
Sales.
7 benefits you get instantly
| Benefit | What it means |
|---|---|
| Grows automatically | Type in the row below the table and it expands. Formulas, charts and pivot tables include the new rows. |
| Formulas fill themselves | Type a formula once in a column and it copies down the whole column (a “calculated column”). |
| Readable formulas | =SUM(Sales[Amount]) instead of =SUM(D2:D847) |
| Filters and sorting | Filter buttons are added to every header |
| Banded rows | Easier to read, with styles in one click |
| Headers stay visible | When you scroll, column letters show your headers |
| Total row | Sum, average or count any column with a drop-down |
Structured references (table formulas)
Inside a table named Sales with columns Qty, Price and Region:
| Formula | Meaning |
|---|---|
=[@Qty]*[@Price] |
Qty × Price in the same row |
=SUM(Sales[Amount]) |
Total of the Amount column |
=SUMIFS(Sales[Amount],Sales[Region],"North") |
North total. See SUMIFS. |
=XLOOKUP(F2,Sales[ID],Sales[Amount]) |
Lookup that grows with the table. See XLOOKUP. |
You don’t have to type these. Click the cells and Excel writes the table names for you.
Add a total row
Click inside the table → Table Design → Total Row. Then click a cell in the total row and choose Sum, Average, Count, Max and more from the drop-down. Totals ignore filtered-out rows.
Tables + pivot tables, charts and drop-downs
- Pivot tables built from a table include new rows after you click Refresh. See how to make a pivot table.
- Charts based on a table update automatically. See making good charts.
- Slicers: Table Design → Insert Slicer for clickable filters. See sort vs filter vs slicers.
- Drop-down lists that grow: base the list on a table column. See drop-down lists.
Things to know
- Dynamic array formulas like UNIQUE and FILTER can’t spill inside a table. Put them outside.
- To go back to a normal range: Table Design → Convert to Range.
- Keep one table per list, with no blank rows in the middle and no merged cells.
Google Sheets
Google Sheets also has tables: select your data and use Format → Convert to table. They bring similar headers, filters and column types.
Quick summary
- Press Ctrl + T on any list and name the table.
- Tables grow, fill formulas, add filters and a total row.
- Formulas like
=SUM(Sales[Amount])are easier to read and never miss new rows.