Excel Tables: Create, Format and Use Structured References

Last updated: November 2026 · Works in Excel for Microsoft 365, Excel 2021, Excel 2019 and Excel 2016

An Excel Table is not just a range with borders. It is a special object that knows where your data starts and ends, keeps formatting consistent, copies formulas down automatically and expands as you add rows. Charts, pivot tables, drop-down lists and formulas that use a table all pick up new data without being edited. If you keep any kind of list in Excel, such as sales, stock, expenses or contacts, converting it to a table is one of the best habits you can build.

Prepare your list

Tables work best with a clean list:

Our guide to entering and managing data covers these principles in more detail.

Create a table

Click any single cell inside your list and press Ctrl + T (or go to Insert > Table). Excel guesses the range and shows it in the dialog. Make sure My table has headers is ticked, then click OK.

Straight away you get banded rows, filter buttons on every header and a new Table Design tab on the ribbon whenever a cell in the table is selected. When you scroll down a long table, the column letters at the top of the sheet are replaced by your header names, so you always know which column you are looking at.

Give the table a meaningful name

Excel names tables Table1, Table2 and so on. Rename it on the Table Design tab, in the Table Name box at the far left. Use a short name without spaces, such as tblSales or Sales. A good name makes formulas much easier to read and lets you jump to the table from the Name Box drop-down.

Styles and banding

Pick a look from the Table Styles gallery on the Table Design tab. The options beside it control Header Row, Banded Rows, First Column and Last Column emphasis. Because the style belongs to the table, new rows are formatted automatically and banding stays correct after sorting or filtering. If you previously applied fill colours by hand, clear them first with Home > Clear > Clear Formats, otherwise they will sit on top of the table style.

Calculated columns: formulas that fill themselves

Type a formula in the first empty column next to the table and press Enter. Excel adds the column to the table and copies the formula into every row. For example, in a column headed VAT, type:

=[@Amount]*5%

The [@Amount] part means "the Amount value in this row". Add a new sale at the bottom and the VAT formula appears in that row automatically. If you change the formula in any cell of the column, Excel offers to update the whole column. You no longer need to drag formulas down or worry that the last few rows were missed.

The Total Row

Tick Total Row on the Table Design tab, or press Ctrl + Shift + T. A row appears at the bottom of the table. Click any cell in it and use the drop-down to choose Sum, Average, Count, Max, Min and more.

The Total Row uses the SUBTOTAL function, which means it only calculates the rows that are visible. Filter the table to show one branch and the total updates to that branch alone, which is a quick way to answer questions without writing extra formulas.

Structured references in formulas

When a formula outside the table refers to it, Excel uses the table and column names instead of cell addresses:

You do not have to type these. Select the column with the mouse while writing the formula and Excel inserts the reference. Structured references are self-explaining, so anyone opening the file can understand Sales[Amount] far more easily than Sheet1!$E$2:$E$873. They also never need updating when the table grows.

One thing to watch: when you copy a structured reference across columns, it moves like a relative reference. To lock a column, write it as a range of itself: Sales[[Amount]:[Amount]].

Filters and slicers

Every table header has a filter button for sorting and filtering; see Data Analysis for the options. For something more visual, choose Table Design > Insert Slicer and tick one or more columns. A slicer is a panel of buttons, one for each value, that filters the table with a single click. Hold Ctrl to select several buttons, and use the small clear-filter icon in the corner to show everything again. Slicers are especially helpful in workbooks used by colleagues who are not comfortable with filter menus.

Limits, and converting back to a range

Tables have a few restrictions. You cannot merge cells inside them, dynamic array formulas such as FILTER and UNIQUE cannot spill inside them, and in older Excel versions a workbook containing tables cannot be shared using the legacy Shared Workbook feature. If you need a plain range again, choose Table Design > Convert to Range. The formatting stays but the table features are removed, and structured references in formulas turn back into normal cell addresses.

Common mistakes to avoid

Summary

Back to: all Excel tutorials.