How to Create a Table in Excel (and Why You Should)

Most spreadsheets are just lists: customers, orders, stock, expenses. Excel has a feature built specifically for lists, called Tables. Most people never use it. Once you do, you'll turn every list into one.

1. Click any cell inside your data. It should have one heading row and no completely blank rows or columns.
2. Press Ctrl + T (on a Mac, Command + T), or go to Insert > Table.
3. Check the range is right and that My table has headers is ticked.
4. Click OK.

Your list now has coloured banded rows and filter arrows in the headings, plus a new Table Design tab on the ribbon whenever you click inside it.

Filters and sorting. Every heading gets a drop-down arrow straight away.

Headings that stay visible. Scroll down a long table and the column letters (A, B, C) are replaced by your headings, so you always know which column is which.

Formulas that fill themselves. Type a formula in the first row of a new column and Excel copies it down the whole table automatically.

A table that grows. Type in the row directly below the table, or the column to the right, and the table expands to include it. Formatting, formulas, charts and PivotTables that use the table all pick up the new data. No more updating ranges by hand.

1. Click inside the table.
2. On the Table Design tab, tick Total Row.
3. A new row appears at the bottom. Click any cell in it and use the drop-down to choose Sum, Average, Count, Max, Min and more.

Total Row uses SUBTOTAL, so it only counts the rows visible after filtering.

Excel calls your first table Table1. Give it something meaningful:

1. Click inside the table.
2. On Table Design, type a new name in the Table Name box, for example Sales (no spaces).
3. Press Enter.

When you click on table cells while writing a formula, Excel uses the column names instead of cell addresses. These are called structured references:

=SUM(Sales[Amount])

instead of =SUM(D2:D847). Inside the table, a formula like =[@Quantity]*[@Price] means "this row's Quantity times this row's Price".

They look strange at first, but they're easier to read and they never go out of date when the table grows.

On Table Design, pick a new style from the Table Styles gallery. You can also switch Banded Rows, First Column and Header Row on or off.

If you want the formatting but not the table behaviour:

1. Click inside the table.
2. Go to Table Design > Convert to Range.
3. Click Yes.

To remove the table colours too, choose Table Design > Table Styles > Clear first.

The table didn't include all my data. There's a blank row or column in it. Go to Table Design > Resize Table and select the correct range.

Merge & Center is greyed out. You can't merge cells inside a table. Use Center Across Selection instead, or leave the headings unmerged.

I can't use Group or Subtotal. These don't work inside tables. Convert to a range first, or use a PivotTable.

Next time you start a list, press Ctrl + T before you do anything else. You get filters, totals and formatting in one step, and a range that looks after itself as your data grows.