Beginner level · Module 1: Reliable Formulas and Excel Tables · Lesson 2 of 3
Excel Tables and Structured References
Store data in Excel tables so formulas, formatting and summaries grow with the data.
In this lesson you will
- Convert ranges to Excel tables
- Write formulas with structured references
Key concepts
Ctrl+Ttable namecalculated columntotal row
The lesson
Select a data range and press Ctrl+T to make it an Excel table. Give it a meaningful name, such as Sales. Tables expand automatically when you add rows or columns.
Formulas inside a table use structured references: =[@Quantity]*[@Price] creates a calculated column that fills the whole table. Outside the table, =SUM(Sales[Amount]) always includes every row.
Tables also provide filters, banded rows and a total row, and they are the best source for PivotTables, charts and Power Query.
Step by step
- Convert the sales range into a table named Sales.
- Add a calculated Amount column.
- Write a summary formula with a structured reference.
- Add rows and check that everything updates.
Practice activity
Rebuild the practical activity's sales sheet on a named Excel table.
Expected result: New rows are included automatically in calculations and summaries.
Check your understanding
-
Which shortcut converts a range to a table?
Show answer
Ctrl+T.
-
Why is =SUM(Sales[Amount]) safer than =SUM(D2:D200)?
Show answer
It always includes every row of the table, however many are added.
Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.