Excel tables and structured references
For office, finance and operations staff who keep growing lists in Excel (orders, invoices, tickets, stock) and build formulas and reports on them. Prerequisite: 'Excel beginner: entering data, ranges, sorting and filtering' (you can already turn a range into a table and filter it). You'll create and name tables that follow Microsoft's naming rules, write calculated columns with this-row references such as [@[Unit Price]], write fully qualified references from other sheets (including column ranges and #Totals or #Headers), set up a total row and understand how SUBTOTAL treats filtered and hidden rows, and keep formulas working as rows, columns and names change. Applies to Excel for Microsoft 365 and recent desktop versions; Excel for the web differs in places. To use table references in SUMIFS-based reports, continue with 'SUMIFS and COUNTIFS for operational reports'. Your organisation's spreadsheet standards apply on top.
- Level
- Intermediate
- Length
- About 85 minutes
- Contents
- 5 lessons · final exam
- Status
- Published · updated 10 Oct 2026
Skills you'll practise
- Create and name an Excel table that follows Microsoft's table naming rules
- Write calculated columns using this-row structured references such as [@[Unit Price]]
- Write fully qualified structured references from outside a table, including column ranges and special items such as #Totals and #Headers
- Add a total row and choose its functions, knowing how SUBTOTAL treats filtered and hidden rows
- Keep table formulas reliable when rows, columns and names change, including resizing, copying versus filling, and converting to a range
Course outline
- 1.Creating and naming an Excel table that follows the naming rulesLesson · 15 min
- 2.Writing calculated columns using this-row structured references such as [@[Unit Price]]Lesson · 17 min
- 3.Writing fully qualified structured references from outside a tableLesson · 18 min
- 4.Adding a total row and knowing how SUBTOTAL treats filtered and hidden rowsLesson · 14 min
- 5.Keeping table formulas reliable when rows, columns and names changeLesson · 15 min
- 6.Excel tables and structured references: knowledge checkKnowledge check · 13 questions
- 7.Excel tables and structured references: practical exerciseKnowledge check · 1 question
- 8.Final exam10 questions · passing it completes the course, so people who already know the material can test out
Sources it draws on
The lessons and questions are written from these references, so learners can go back to the original.
- Microsoft Support: Using structured references with Excel tables
- Microsoft Support: Overview of Excel tables
- Microsoft Support: Create and format tables
- Microsoft Support: Rename an Excel table
- Microsoft Support: Use calculated columns in an Excel table
- Microsoft Support: Total the data in an Excel table
- Microsoft Support: SUBTOTAL function
- Microsoft Support: Resize a table by adding or removing rows and columns
- Microsoft Support: SUMIFS function
See it with your own jobs and topics
Tell us about your team and we'll walk you through setup, from choosing jobs to your first skills check.