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.
