Locked Table Reference in Excel: Meaning and Fixes

Updated 5 November 2026 · Standard formula, shown below

A locked table reference in Excel is a cell or range reference that stays fixed when a formula is copied to another cell. Add dollar signs to the column, row, or both—for example, $A$1—to control what moves, and use Excel’s reference toggle to switch between the available forms.

What “locked table reference” means

The phrase usually refers to an absolute or mixed cell reference in a spreadsheet formula. It does not normally mean that an Excel table is protected from editing. “Locked” describes how a reference behaves when the formula is copied or filled elsewhere.

In a standard reference such as A1, both the column and row can adjust relative to the formula’s new location. Dollar signs make either part fixed:

A1: relative column and relative row.
$A$1: fixed column A and fixed row 1.
$A1: fixed column A, relative row.
A$1: relative column, fixed row 1.

These are often called relative, absolute, and mixed references. The distinction matters most when copying formulas across rows or columns.

How the reference changes when copied

Excel adjusts relative parts according to the formula’s movement. Suppose cell C2 contains =A2*B1. Copy it one row down to C3, and Excel changes the formula to =A3*B2. Both references moved one row because neither was locked.

If B1 contains a tax rate or another value that should remain constant, write =A2*B$1 or =A2*$B$1, depending on whether the formula will be copied across columns. With B$1, the row remains 1 while the column may change. With $B$1, both stay fixed.

A useful rule is to lock only the dimension that must not change. Locking too much can cause copied formulas to keep pointing at the wrong cells; locking too little can make references drift.

Examples in worksheet formulas

Imagine a price list in column A and a single discount rate in cell D1. To calculate discounted prices in column B, enter =A2*(1-$D$1) in B2 and fill down. The price reference changes to A3, A4, and so on, while the discount rate always points to D1.

For a two-way calculation table, mixed references are especially useful. Suppose row 1 contains quantities and column A contains unit prices. A formula in B2 might be =$A2*B$1. When copied across, $A2 continues to use column A but adjusts its row. When copied down, B$1 continues to use row 1 but adjusts its column. The formula can fill the grid without manually editing each cell.

These examples use ordinary cell addresses. Excel’s structured references, such as Table1[Sales], are a separate notation for referring to table columns. They often adapt automatically as table formulas are filled, and their behavior is not controlled by adding dollar signs to a cell address.

How to lock or change a reference

Select the cell containing the formula, then select the formula bar or press F2 to edit the formula.
Click or place the cursor within the cell reference you want to change. For example, select A1 inside the formula.
Press F4 in Excel for Windows to cycle through $A$1, A$1, $A1, and A1. On some laptops, you may need Fn+F4, depending on the keyboard’s function-key settings.
Press Enter to save the formula, then copy or fill it to check that the reference behaves as intended.

On Excel for Mac, the shortcut can depend on the keyboard and Excel settings; Command+T is commonly used to cycle reference types while editing a formula. If a shortcut does not work, enter the dollar signs directly in the formula bar. In Google Sheets, you can also add or remove the dollar signs manually; keyboard shortcuts can vary by system and browser.

Cell locking is different from a locked reference

A common confusion is between locking a reference and locking a worksheet cell. Dollar signs affect how formulas change when copied. They do not prevent a person from editing the referenced cell.

Worksheet protection is a separate feature. Cells can be marked as locked in their formatting, but that setting only restricts editing after sheet protection is enabled. It does not make a formula reference absolute, and an absolute reference does not protect a cell from being changed.

Common problems and troubleshooting

The reference moves unexpectedly: Add a dollar sign before the row number, column letter, or both, then test the formula in a copied cell.
The whole reference stays fixed: You may have used $A$1 where only a mixed reference was needed. Change it to A$1 or $A1.
F4 changes the computer’s display or does nothing: The key may have a hardware function assigned, or the cursor may not be within a reference being edited. Try the Fn key or type the dollar signs manually.
A table formula looks different from A1 references: Structured references use table and column names. They are not necessarily meant to be converted into dollar-sign references.
A formula displays an error after editing: Check that the cell address is valid and that parentheses and operators remain in place. A dollar sign fixes part of an address; it does not correct a broken formula.

Also remember that copying and moving a formula are not always the same operation. Copying normally adjusts relative references; moving a cell or using a cut-and-paste operation may preserve references differently. Check the resulting formula when the distinction affects your work.

Frequently Asked Questions

What is the shortcut for a locked cell reference in Excel?
In Excel for Windows, press F4 while editing or selecting a reference in a formula to cycle through absolute and mixed forms. Depending on the keyboard, Fn+F4 may be needed.

What does the dollar sign do in an Excel formula?
It fixes the column, row, or both when a formula is copied. In $A$1, neither the column nor row changes; in A$1, only the row is fixed.

How do I lock only a row or only a column?
Put the dollar sign before the part to keep fixed: A$1 locks row 1, while $A1 locks column A.

Does a locked reference protect a cell from editing?
No. It controls how a reference adjusts when a formula is copied. Use worksheet protection if your goal is to restrict editing.

Can I lock a reference in an Excel table formula?
It depends on the reference type. Dollar signs lock conventional cell addresses; structured references use table and column names and follow their own rules. Use the reference style that suits the formula and verify it after filling.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.