Overview
This lecture explains how to lock table references in Excel formulas, ensuring accurate results when copying across columns or rows, and introduces shortcuts to simplify the process.
Problems with Copying Excel Table Formulas
- Excel table structured references shift when copying formulas by dragging, causing incorrect results.
- Copying and pasting a formula with structured references treats them as absolute by default.
Locking Table References (Absolute and Relative)
- To lock (absolute) a column reference, wrap the column name in double square brackets with a colon:
[[Column]:[Column]].
- Locked references ensure the formula always uses the correct column when copied across.
- To keep a reference relative (so it moves as you drag), do not use the double square brackets.
Shortcut for Creating Absolute References
- Select two columns in the table to have Excel build the correct reference with double brackets and colon.
- Copy and paste the column name to easily create a locked reference without typing the syntax manually.
Working with Multiple Columns
- The double square brackets trick works for single and multiple columns in functions like XLOOKUP.
- To lock a multi-column reference but keep it relative, include the table name after the colon (e.g.,
[Table[Col1]:[Table[Col2]]]).
Locking Row References with the At Sign (@)
- The
@ symbol before a column in a formula refers to the current row's value.
- Locking a row reference works similarly using double brackets to keep the formula stable across rows and columns.
Key Terms & Definitions
- Structured reference — A way to refer to table columns and rows in Excel formulas using column names.
- Absolute reference — A reference that doesn’t change when the formula is copied across cells.
- Relative reference — A reference that adjusts when the formula is copied.
- @ sign — In Excel tables, refers to the value from the current row of the specified column.
Action Items / Next Steps
- Download the cheat sheet with reference types and syntax from the link in the video description.
- Practice using double square brackets to lock references in your Excel table formulas.