Exceljet

Quick, clean, and to the point

Locked table reference

Example of locked table reference in formula

Excel Tables provide a special kind of formula syntax called a structured reference. In a nutshell, structured references makes it possible to refer to parts of an Excel Table by name, so you can write formulas like this:

=SUM(Table1[Total]) // sum total column
=MAX(Table1[Total]) // max of total column

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:

=SUM(Table1[Qty])

will change to:

=SUM(Table1[Price])

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:

=Table1[[Total]:[Total]]*G4 // locked column reference

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.