An Excel Table turns a plain range of data into a smart, self-managing list. It grows when you add rows, copies formulas down by itself, adds filter buttons and a total row, and makes formulas easier to read. If you work with lists in Excel, Tables are the single most useful habit you can build.
Table of Contents
How to create an Excel Table
- Click any cell in your data. Make sure the data has one header row and no completely blank rows or columns.
- Press Ctrl + T (or Ctrl + L), or go to Insert → Table.
- Check that My table has headers is ticked, and click OK.
More options, including Format as Table and the Quick Analysis button, are in different ways to create an Excel Table.
Give the Table a name
New tables are called Table1, Table2 and so on. A clear name such as Sales makes formulas easier to read. Click inside the table, go to Table Design, and type a new name in the Table Name box.
Names can’t contain spaces. Use an underscore instead, as in Sales_2026.
1. Tables grow automatically
Type in the row just below a table, or the column just to its right, and the table expands to include it. Every formula, chart and PivotTable that uses the table picks up the new data without editing a single reference.
You can also drag the small handle at the bottom-right corner of the table to resize it.
This makes Tables the best source for drop-down lists and PivotTables. Point a drop-down at a table column and new items appear in the list automatically:
2. Formulas you can read: structured references
Inside a table, formulas refer to column names instead of cell addresses. Click a cell while writing a formula and Excel inserts the column name:
=[@Units]*[@Price]
[@Units] means “the Units value in this row”. Compare that with =C3*D3: the table version tells you what it calculates. Outside the table, use the table name and column:
=SUM(Sales[Total])
This totals the whole Total column of the Sales table, 7,850, and it keeps including new rows as the table grows. Your existing examples show the same idea with measurements: =[@Length]*[@Breadth] for area, =2*[@Length]+2*[@Breadth] for perimeter, and =[@Height]*[@Area] for volume.
| Reference | Means |
|---|---|
[@Price] |
Price in the current row |
Sales[Price] |
The whole Price column, data only |
Sales[#Headers] |
The header row |
Sales[#Totals] |
The Total Row |
Sales |
All the data in the table |
3. Formulas fill down by themselves
Type a formula in one cell of a table column and Excel copies it to every row. This is called a calculated column. Change the formula in any cell and the whole column updates.
New rows added later get the formula too, so there’s no dragging the fill handle and no gaps.
4. A Total Row in one click
Tick Total Row on the Table Design tab, or press Ctrl + Shift + T. Click any cell in the Total Row to choose Sum, Average, Count, Max, Min and more:
The Total Row uses SUBTOTAL with 109, so when you filter the table, the total shows only the visible rows.
5. Filters and Slicers built in
Every table gets filter buttons in its header row. For an easier, clickable filter, add a Slicer: click inside the table, go to Table Design → Insert Slicer, tick the columns, and click OK.
6. Headers stay visible as you scroll
Scroll down a long table and the column letters A, B, C are replaced by the table’s own headers, as long as a cell inside the table is selected. For most lists, you don’t need Freeze Panes.
7. Formatting in one click
Tables come with banded rows and dozens of styles. Click inside the table, go to Table Design → Table Styles, and hover over a style to preview it. Banded rows stay correct even after you sort, filter or insert rows.
8. Jump to any table
Every table in the workbook is listed in the Name Box, to the left of the formula bar. Pick one from the drop-down to jump straight to it, even on another sheet.
9. Alt Text for accessibility
To describe a table for people using screen readers, right-click it and choose Table → Alternative Text, then add a title and description.
Excel Table shortcuts
| Shortcut | Does |
|---|---|
| Ctrl + T or Ctrl + L | Create a table |
| Ctrl + Shift + T | Show or hide the Total Row |
| Ctrl + Shift + L | Show or hide the filter buttons |
| Ctrl + A | Select the table data; press again to include headers |
| Ctrl + Space | Select the table column |
| Shift + Space | Select the table row |
| Tab in the last cell | Add a new row |
Things Tables can’t do
- No merged cells. Unmerge them before creating the table.
- No spilling formulas inside a table. Functions like FILTER and UNIQUE show
#SPILL!inside a table. Put them outside it, referring to the table. - Workbooks with tables can’t use the old Shared Workbook feature. Use co-authoring in OneDrive or SharePoint instead.
To turn a table back into a normal range, click inside it and choose Table Design → Convert to Range. The formatting stays, and the structured references become normal cell references.
Frequently asked questions
What’s the difference between a Table and a range?
A range is just cells. A Table is a named object that knows where its data starts and ends, so it can expand, fill formulas, filter, total and be referred to by name.
Why should I use a Table as the source of a PivotTable?
Because the table grows with your data. Refresh the PivotTable and new rows are included, with no need to change its source range.
How do I remove the table formatting but keep the table?
Go to Table Design → Table Styles and choose Clear at the bottom of the list.
Can I have two tables on the same sheet?
Yes. Leave at least one empty row or column between them, and give each a clear name.