📎 This article includes 1 downloadable practice file ↓
In this article
In Lessons 5 and 7 you met two annoyances: ranges like B2:B11 that miss new rows, and sorting that breaks when there’s a blank row. Excel Tables solve both. A Table is your list with superpowers, and making one takes one keystroke.
Create a table
- Click any cell in your list (one header row, no blank rows).
- Press Ctrl + T, check that My table has headers is ticked, press Enter.
- On the Table Design tab, type a name in Table Name, for example
Sales.
You get banded rows, filter arrows and a new tab of table tools straight away.
What changes
| Before (plain range) | After (Table) |
|---|---|
| New rows outside formulas and charts | Type under the table and it grows; everything linked includes the new row |
| Formulas dragged down by hand | Type a formula once in a column, it fills the whole column |
| Headers scroll away | Header names replace A, B, C at the top while you scroll |
| Totals typed below, often wrong | Tick Total Row and pick Sum/Average/Count per column |
Formulas like =SUM(F2:F61) |
Formulas like =SUM(Sales[Amount]) |
Calculated columns
Add a header “Amount with GST” in the first empty column to the right. The table expands. In the first row type =[@Amount]*1.18 and press Enter: the whole column fills. [@Amount] means “the Amount in this row”. You can also just click the cell instead of typing; Excel writes the name for you.
Structured references
Outside the table, refer to whole columns by name:
=SUM(Sales[Amount])
=SUMIFS(Sales[Amount], Sales[Region], "North")
=ROWS(Sales) ' how many records
=MAX(Sales[Date]) ' latest order dateAdd 10 new orders and every one of these updates. No ranges to fix.
Total Row and slicers
- Table Design › Total Row adds a totals line; each cell has a drop-down (Sum, Average, Count, Max…). It respects filters, so filtering to “North” shows North’s total.
- Table Design › Insert Slicer adds clickable buttons, e.g. one per Region, a friendlier filter for people who don’t like drop-downs.
Styles and removing a table
Pick any look from Table Styles; light styles print best. To go back to a plain range but keep the formatting: Table Design › Convert to Range.
Where beginners go wrong
| Mistake | Fix |
|---|---|
| Leaving the name as Table1 | Rename it; Sales[Amount] reads far better than Table1[Amount] |
| Totals typed in the row under the table | The table grows into them; use Total Row |
| Two header rows or merged headers | One header row only; put titles above the table |
| A blank row in the middle before Ctrl+T | The table stops at the blank; delete blank rows first |
More reasons to use them: 10 reasons to use Excel Tables.
Practice
Download this lesson’s workbook below. The Sales sheet is already a Table named Sales; answer each question with table formulas, then add a row and watch the answers update. Type your answers in the yellow column; the Check column turns green when you’re right, and the Answers sheet shows a working formula for every task.
📎 Practice files for this article
- 📗Lesson 8 practice workbookA 60-order Sales Table: answer 10 questions with table formulas, then add a row and watch everything update.⬇ XLSX · 16 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.