Video Guide: Locking Excel Table References

Introduction

• Excel tables do not easily support locking column references, often leading to issues when formulas are copied across columns.

Objective

• Demonstrate how to lock table references and provide a shortcut to apply them.

Example Problem

• Scenario: Calculating the total stock for the "bread" group using SUMIFS.
• Issue: Copying the formula leads to incorrect results due to reference shifts.

Solution Overview

1. Absolute references for single columns.
2. Utilize double square brackets for structured references.

Step-by-Step Instructions

Absolute Single Column

• Use F4 to lock reference to a cell (e.g., D4).
• Limitation: F4 does not work on structured references.

Structured References

• References with structured tables are treated absolute when copied via Ctrl + C and Ctrl + V.
• Left-click and drag treats references as relative.

Locking Single Column Reference

• Wrap the reference with additional square brackets and a colon: [column]:[column].

Shortcut

• Copy column name and paste it within the double square brackets Excel provides.

Multi-Column References

• For XLOOKUP, lock reference to multiple columns by selecting them, Excel inserts brackets.
• Use TRANSPOSE if horizontal array needs to be vertical.

Make Multicolumn Reference Relative

• Remove double square brackets.
• Adjust by repeating the table name with a colon.

Locking Row References

• Use the @ sign to lock current row values across a table.

Example

• For calculations like allocation across product departments:
  • Make reference to allocation column fixed for dragging formulas.

Tips and Tricks

• Allow Excel to generate needed syntax by selecting ranges.
• Cheat Sheet: Available for download to reference different syntax types.

Conclusion

• Locking references in Excel tables can improve formula stability.
• Consider watching the next video on practical Excel tricks for additional solutions to common issues.