You can use dollar signs to lock cell references in normal Excel formulas.
For example, you could type $A$1 to lock the reference to cell A1 in a formula so that when you drag the formula to another cell, A1 is still used.
However, if you’d like to lock a reference to a column name in an Excel table, you must use brackets instead.
For example, you can use the following syntax to lock the reference to the column named Points within the table named Table1 in an Excel sheet:
=Table1[[Points]:[Points]]*D1
This particular formula will multiply the value in cell D1 by the corresponding value in the Points column of Table1.
When you drag this formula to other cells, the values in the Points column will still be used in the formula since we used brackets to lock this column reference.
The following example shows how to use this formula in practice.
Example: How to Lock a Table Reference in Excel
Suppose we have the following table called Table1 in Excel that contains information about points scored by basketball players on various teams:
Suppose we would like to multiply the values in the Points column 5, 10 and 20 and display these resulting values in new columns.
We can type the following formula into cell D2 to do so:
=Table1[[Points]:[Points]]*D1
We can then click and drag this formula to the right until we hit column F:
The new columns show the corresponding values in the Points column of Table1 multiplied by 5, 10 and 20.
By using the syntax Table1[[Points]:[Points]] we are able to lock the reference to the Points column of Table1.
Thus, when we drag the formula to the right we keep the Points column locked and simply multiply by the new values encountered in the first row.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: Find Text in Range and Return Cell Reference
Excel: How to Use INDIRECT With Sheet Names
Excel: How to Create a Floating Table
