Work with Excel tables
Give a growing dataset a consistent structure, useful names and built-in controls.
By the end, you’ll be able to:
- Create a real Excel table with headers
- Recognize structured references and calculated columns
- Use the Total Row without confusing visible and complete data
A table is more than a colored range
An Excel table is a structured object created from a rectangular range. It has a name, column headings, filter controls and behavior that helps a dataset grow. Applying a fill color to cells does not create those features.
Tables are useful for order logs, expense lists and product records. Keep one heading row, one row per record and one field per column. Avoid merged cells and internal blank rows. Place report titles and narrative outside the table.
Worked example: turn the order list into a table
- Select the dataset, including headings, A1:D6.
- Choose Insert → Table, or Home → Format as Table.
- Confirm the range and select My table has headers.
- Use the table-design controls to give it a descriptive name, such as SalesData.
- Try the filter arrows and add a record immediately below the table.
Typing directly below a table normally expands it to include the new record. Verify the table boundary after adding data, especially when pasting large blocks. Table names cannot contain spaces; use a clear name such as SalesData rather than Table1.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Region | Category | Sales |
| 2 | A101 | East | Stationery | 120 |
| 3 | A102 | West | Home | 80 |
| 4 | A103 | East | Home | 200 |
| 5 | A104 | West | Stationery | 150 |
| 6 | A105 | East | Stationery | 60 |
Read references that describe the data
=SUM(SalesData[Sales]) refers to the Sales column in the SalesData table. A table-column reference expands with the table. It is easier to interpret than a long address when you return to the workbook later.
Inside a calculated column, =[@Units]*[@Price] multiplies Units and Price from the current row. The @ means “this row.” Excel normally fills a calculated-column formula through the table, but check for exceptions if existing rows contain different formulas.
These are called structured references. The browser formula checker currently teaches A1 references instead. Try the native table tasks in the downloadable extension workbook to practice table features in Excel.
Choose the right total
Enable the table’s Total Row and choose Sum from a numeric column’s dropdown. Excel commonly creates a SUBTOTAL formula that excludes filtered-out rows. This can answer “what are the visible sales?”
=SUM(SalesData[Sales]) instead totals the whole Sales column, even when some records are filtered out. Neither result is inherently better: choose the measure that matches your question, then label it clearly.
Use unique headings, consistent field types and formulas that refer to the intended columns. Add records inside the table boundary and test totals after an extension.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 4 exercises complete
Take this with you
Create a real table, name it clearly and check its boundary. Structured references describe columns; the Total Row can summarize visible records.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Reviewed 2026-10-16.