One of the more complicated examples of this syntax occurs when locking column references. Although it may not look like it, a reference to a table column is relative and will change as a formula is copied. For example this formula entered in G5: will change to: when the formula is copied to H5. How can we look a column reference to stop it from changing? Unlike a standard absolute or mixed reference, you can’t use the $ sign to lock a table column reference. Instead, you must duplicate the column name in another pair of square brackets ([]). For example, in the worksheet shown, the formula in G5 is: This effectively locks the reference to Table1[Total] so that it will not change as the formula is copied. This allows the formula in G5 to be copied to H5 and I5 and keep working correctly. Note: because this example uses a dynamic array formula, it is not necessary to lock the row 5, which contains the percentages.
Video: Introduction to structured references Video: How to copy and lock structured references
Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
