Table of Contents
Locking a table reference in Excel allows you to prevent the reference from changing when you insert or delete rows or columns in your worksheet. This can be useful when you want to maintain a specific range of data for your calculations. To lock a table reference, you can use the “$” symbol in the cell reference. For example, if you want to lock the reference for cell B2, you would write it as $B$2. This will prevent the reference from changing when you copy or move the formula to other cells.
Lock a Table Reference in Excel (With Example)
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.
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: