How to create and use a table in Excel
Last updated
An Excel table turns a plain list into one named block of data. Click any cell of the list, press Ctrl + T (or go to Insert > Table), check the range, keep My table has headers ticked and click OK. The table gets banded rows, a filter arrow in every header and a name such as Table1. Formulas can then use structured references such as =SUM(Sales[Total]), which follow the table as it grows.
Where to find it in Excel
Insert > Table (Ctrl + T) > Create Table > My table has headers
- Where is the data for your table?
- The range of the table, header row included, such as
=$A$1:$E$9. Excel proposes the whole block of filled cells around the active cell, so there is usually nothing to select first. - My table has headers
- Keep it ticked when the first row holds the column names. Unticked, Excel adds its own header row named Column1, Column2, …
- Table Name
- On the Table Design tab, replace
Table1with a name formulas will read well, such asSales. A name is one word without spaces and starts with a letter or an underscore. - Calculated column
- Type a formula in one cell of an empty column of the table and Excel fills it into every row. Rows added later get the formula too.
- Structured references
Sales[Total]is the whole Total column without its header,[@Qty]the Qty cell of the formula’s own row, andSalesalone the whole table without headers.- Filter arrows
- Each header has an arrow to sort the table or filter it on one or more values. Rows that don’t match are hidden, not deleted.
Practise Excel tables here
Click a cell of the data and press Ctrl + T, or click Table above the formula bar, as on the Insert tab in Excel. Type structured references straight into the cells, and use the arrows in the headers to filter.
Turn the orders into a table
Click any cell of the orders, press Ctrl + T (or click Table above the formula bar), check that the range is A1:E9, keep My table has headers ticked and click OK.
| Region | Product | Qty | Price | Total |
| North | Laptop | 2 | 900.00 | 1,800.00 |
| South | Monitor | 3 | 250.00 | 750.00 |
| East | Keyboard | 10 | 45.00 | 450.00 |
| North | Monitor | 4 | 250.00 | 1,000.00 |
| West | Laptop | 1 | 900.00 | 900.00 |
| South | Keyboard | 6 | 45.00 | 270.00 |
| East | Laptop | 3 | 900.00 | 2,700.00 |
| South | Monitor | 2 | 250.00 | 500.00 |
How to create a table, step by step
- Make sure the list has one header row and no completely blank row or column inside it, then click any cell of it.
- Press Ctrl + T, or click Insert > Table. To pick a colour style at the same time, use Home > Format as Table instead.
- In Create Table, check the range, keep My table has headers ticked and click OK.
- On the Table Design tab, type a short name in Table Name, such as
Sales, and press Enter. Formulas can now refer toSales[Total].
Structured reference examples
Total of a column
=SUM(Sales[Total])
Adds up the Total column of the Sales table. New rows at the bottom of the table are included without changing the formula.
A calculation on each row
=[@Qty]*[@Price]
Typed in an empty column, it becomes a calculated column: each row multiplies its own quantity by its own price.
A conditional total
=SUMIF(Sales[Region],"North",Sales[Total])
Adds up the Total of the North rows. COUNTIF and AVERAGEIF take table columns the same way.
Number of rows
=ROWS(Sales)
The table name alone is its data, without the header row, so ROWS returns the number of records.
Look up in a table
=VLOOKUP(G2,Products,3,FALSE)
The table name replaces a range such as $A$2:$C$50: no $ needed, and the lookup still covers rows added later.
Common problems and how to fix them
| Problem | Cause | Fix |
|---|---|---|
| Headers called Column1, Column2… | My table has headers was unticked, so Excel added a header row above the data. | Undo with Ctrl + Z and create the table again with the box ticked, or type the real names over Column1, Column2. |
| The formula doesn’t fill the column | The column already held other values, or the AutoCorrect option that creates calculated columns is turned off. | Start from an empty column, or tick Fill formulas in tables to create calculated columns under File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type. |
| “There’s a problem with this formula.” | A table or column name in the structured reference is misspelled, or a column name with a space is used after @ without its own brackets. | Check the names against the header row. Write [@[Unit Price]] for a column whose name has a space. |
| New rows aren’t part of the table | They were typed after a blank row, or further down than the row just below the table. | Type directly in the first row under the table, or use Table Design > Resize Table. |
| The total doesn’t change when you filter | SUM adds hidden rows too: a filter only hides them. | Tick Total Row on the Table Design tab. Its totals use SUBTOTAL, which only counts the visible rows. |
Table or plain range?
A plain range is fine for a quick list, but every formula, chart or drop-down that uses it has to be widened by hand when rows are added. A table grows on its own when you type under it, keeps its formatting and filters, and lets formulas say Sales[Total] instead of $E$2:$E$9. Colours applied with cell styles look the same but give none of that. If you need a plain range again, Table Design > Convert to Range keeps the formatting and turns structured references into normal cell references.
Frequently asked questions
What is the shortcut to create a table?
Ctrl + T opens Create Table; Ctrl + L does the same. Click a cell of your data before you press it.
How do I rename a table?
Click a cell of the table, go to the Table Design tab and type the new name in Table Name, at the far left. Formulas that use the old name update by themselves.
How do I add a total row?
Tick Total Row on the Table Design tab. A row appears under the table, and each of its cells has a list to pick Sum, Average, Count and more.
Why does my formula show [@Qty] instead of C2?
When you click a cell of a table while typing a formula, Excel writes a structured reference. To get cell addresses instead, untick Use table names in formulas under File > Options > Formulas.
How do I turn a table back into a normal range?
Click inside it and choose Table Design > Convert to Range. The colours stay, the filter arrows go, and formulas switch to cell references.
Keep practising
All 5 Excel table exercises, with explanations and progress tracking.
Open the Excel table exercises