Intermediate Excel · Interactive lesson 1 of 12
Clean data & Excel Tables
Every skill in this track assumes the data underneath it is table-ready: one header row, no blank rows or merged cells, and the same kind of value all the way down each column. Get that right first and sorting, PivotTables, and formulas all behave the way you expect.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialTurn Messy Data into a Clean Excel TableSkill check
Try these first:
- Explain what makes a range "table-ready" instead of just data sitting in cells.
- Convert a range into a Table and give it a real name.
- Add a calculated column that fills itself down automatically.
- Turn on a Total Row and change what it summarizes.
Comfortable with all four? Skip ahead to I2 — References.
Targeted teach
What you're learning
- Table-ready data
- One header row, no blank rows or columns inside the data, no merged cells, and one consistent kind of value all the way down each column.
- Format as Table / Ctrl+T
- Turns a plain range into a structured Table with its own name. The common mistake: converting a range with a stray blank row buried in it, which splits the table in two.
- Table naming
- Excel calls it "Table1" by default — rename it to something like
SalesDataso later formulas read like sentences instead of cell soup. - Structured references
- Inside a Table, a formula can say
[@Sales]*[@Commission Rate]instead ofC2*E2— it reads the same on every row and never needs re-copying. - Total Row & calculated columns
- A calculated column fills itself down the instant you type it once; a Total Row adds a summary strip you can switch between Sum, Average, and more.
Where to find it in Excel
Home › Format as Table›Table Design
| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | OrderID▾ | Region▾ | Qty▾ | Price▾ | Amount▾ | |||
| 2 | 1001 | North | 4 | 12.50 | 50.00 | |||
| 3 | 1002 | South | 2 | 18.00 | 36.00 | |||
| 4 | 1003 | West | 6 | 9.75 | 58.50 | |||
| 5 | 1004 | North | 3 | 22.00 | 66.00 | |||
| 6 | 1005 | East | 5 | 11.00 | 55.00 | |||
| 7 | Total | 20 | 265.50 | |||||
| 8 |
Swipe sideways to see the whole window.
- 1Make the range a table with Home › Format as Table or Ctrl+T; Table Design then appears.
- 2Rename the table (here Orders) so formulas read =SUM(Orders[Amount]).
- 3A calculated column: one formula with [@Column] references fills the whole column.
- 4Total Row adds totals that ignore filtered-out rows.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided practice
A plain sales range below. Convert it to a Table, rename it, add a calculated Commission column, then turn on the Total Row — in that order.
Independent variation
Turn a messy export into a Table
Take a real range you have — a sales list, a sign-up sheet, an export from another tool — and convert it into a proper Excel Table. Give it a name that describes what it holds, add one calculated column, and turn on the Total Row set to whichever summary actually answers a question you have about the data.
Spot a mistake or have an idea for this lesson? Send feedback