To keep a cell reference fixed when you fill a formula down an Excel table, add dollar signs to its column and row, such as $G$2. For example, a table formula can multiply each row’s quantity and unit price by the fixed value in G2: enter =[@Quantity]*[@UnitPrice]*$G$2 in the calculated column; Excel keeps G2 fixed as the formula fills down.
What locking a cell reference means
Excel normally treats a cell reference as relative. When you copy or fill a formula into another row, Excel changes the reference to match the new position. That behavior is useful when each row should use its own values, but it causes a problem when every row should refer to the same input cell, rate, or assumption.
A dollar sign makes a reference absolute. In $G$2, the first dollar sign locks column G and the second locks row 2. So if a formula moves down, up, or across, it continues to point to G2. This is what people usually mean when they ask how to lock a cell reference while dragging a formula.
There are also mixed references. G$2 locks row 2 but lets the column change, while $G2 locks column G but lets the row change. These are useful for formulas filled across a grid, such as a multiplication table, but a fixed setting cell usually needs the fully locked form, $G$2.
Why Excel tables behave differently from ordinary ranges
An Excel table is more than a formatted block of cells. When you enter a formula in a table column, Excel commonly treats that column as a calculated column and copies the formula to the other data rows. The formula can also extend to new rows added to the table. This automatic filling is why dragging may not be necessary: entering the formula once may be enough.
Table formulas often use structured references, which refer to table headers and rows by name instead of using ordinary addresses such as A2 or B2. For example, [@Quantity] means the Quantity value in the current table row. When Excel fills that formula down, the current-row reference continues to mean the value on each respective row.
Structured references and absolute references can be combined. Suppose a table named Sales has Quantity and UnitPrice columns, and G2 contains a multiplier of 1.08. With quantity 3 and unit price 12.50 in a row, the formula =[@Quantity]*[@UnitPrice]*$G$2 returns 40.50. The two structured references use that row’s values, while the dollar signs keep the multiplier at G2. If G2 changes to 1.10, the table’s calculated results update accordingly.
How to lock a reference before filling a formula
In Excel for Microsoft 365 on Windows, enter the formula in the first data cell of the table column, make the fixed reference absolute, and press Enter. Excel will usually fill the calculated column automatically. If you are working in a regular range instead, or need to fill the formula manually, use the fill handle at the lower-right corner of the selected cell and drag it down.
- Select the first formula cell and type the formula, including the reference that should stay fixed.
- Place the insertion point on that cell reference while editing the formula. Select the reference itself, not the whole formula.
- Press F4 to cycle the reference through relative, absolute, and mixed forms. Stop when both the column and row have dollar signs, as in
$G$2. You can also type the dollar signs yourself. - Press Enter to commit the formula. Check a few rows to confirm that the row-specific references changed as intended and the locked reference did not.
For example, with quantities in B2:B20 and a fixed rate in F1, a formula entered in C2 could be =B2*$F$1. When filled down one row, the quantity reference becomes B3, while the rate remains F1. This is a regular-range example; inside a table, you may prefer a structured reference for the row-specific quantity and an absolute address for the fixed rate.
On Excel for Mac, select the reference while editing and use Command+T to cycle its reference style. Keyboard behavior can vary with keyboard settings and function-key configuration, so typing the dollar signs directly is a reliable alternative. Excel for the web supports absolute references in formulas too, but function-key shortcuts can depend on the browser and keyboard; enter the dollar signs directly if the shortcut does not change the reference.
Choosing between relative, absolute, and mixed references
Use the reference style that matches what should move when Excel copies the formula. The choice is about the behavior of the formula, not about whether the cell is inside a table.
- Relative, such as A1: both row and column can change when the formula is copied. Use it for values that should follow each row or column.
- Absolute, such as $A$1: neither row nor column changes. Use it for one fixed setting, rate, target, or lookup location.
- Mixed, such as A$1: the row stays fixed but the column can change. This is useful when filling across columns that all refer to a header row.
- Mixed, such as $A1: the column stays fixed but the row can change. This can help when filling down a model that always uses one input column.
For a table column, the usual pattern is a row-aware structured reference for the current record and an absolute A1 reference for a fixed cell outside the table. Do not lock every reference automatically. If you turn a row-specific reference into an absolute one by mistake, every table row can calculate from the same source row and return repeated or incorrect results.
Structured references versus dollar signs
Structured references do not use dollar signs in the same way as A1 references. In a table formula, [@UnitPrice] means the UnitPrice value in the current row; it is not a request to lock a particular worksheet cell. Excel maintains that row context as the calculated-column formula is filled down. A reference such as Sales[UnitPrice] refers to the whole UnitPrice data column of the Sales table, rather than only the current row.
Use a current-row structured reference when each record should calculate from its own data. Use a whole-column structured reference when a function or formula needs an entire table column. Use an absolute cell reference when a particular worksheet cell must remain the source. Mixing these reference types is normal, but be clear about whether the formula needs one row, an entire column, or one fixed cell.
For example, if a table column named ExtendedCost should multiply each row’s Quantity by its UnitPrice, a formula using =[@Quantity]*[@UnitPrice] refers to the current record and does not need dollar signs. If all rows also use one adjustment factor stored in G2, append *$G$2. These examples assume the table has Quantity and UnitPrice headers and G2 contains the adjustment factor.
When a table formula does not fill or stay fixed
If a formula appears only in one table row, first check whether the cell is actually inside the table. A formatted range can look like a table without being one. Click in the data and look for the Table Design tab; if it appears, Excel recognizes the selection as a table. If the table is recognized, enter the formula in a cell in the intended table column and check whether it populates the other data rows.
Excel’s calculated-column behavior can be changed. In Excel for Microsoft 365 on Windows, the option is under File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type; look for Fill formulas in tables to create calculated columns. If it is turned off, formulas may not propagate automatically. You can fill the column manually, or adjust the setting if automatic calculated columns suit your workflow. Menu wording and availability can differ in other Excel versions and on Mac.
When a formula does fill but produces the wrong result, inspect the references in a later row. If the fixed cell shows as G3 instead of $G$2, the address was not locked. If every row uses the same quantity or unit price, a row-specific reference may have been made absolute accidentally. Also check that the fixed input cell is the one you intended and that it contains a numeric value when the calculation expects one.
A further source of confusion is table expansion. Excel may extend a calculated-column formula when a new row is added, but a manually entered formula can differ from the rest of the column. If Excel displays a warning or inconsistent formula indicator, compare the formulas and correct the column so every row follows the intended calculation.
Practical checks before relying on the result
After filling a formula, verify its behavior rather than trusting the appearance of the first row. Click a formula cell several rows lower and inspect the formula bar. For an ordinary range, the row-specific reference should have advanced while the absolute reference should be unchanged. In a table, confirm that structured references still point to the current row and that the absolute reference still points to the fixed input cell.
- Change the fixed input temporarily to a different test value and confirm the affected results update.
- Compare one result with a hand calculation using that row’s source values.
- Add a test row if the table is expected to grow, and check whether the calculated-column formula appears.
- Undo the test change or restore the intended input value before using the workbook.
These checks are especially helpful in shared workbooks, where another person may have changed a table option, inserted rows, or copied a formula from elsewhere. A locked reference controls which cell an A1 reference points to; it does not protect the cell from being edited, prevent the table from changing, or guarantee that the formula’s logic is correct.
Frequently Asked Questions
How do I lock a cell in an Excel table formula?
Put dollar signs before both the column letter and row number, such as $G$2. Use that reference in the formula, then enter it in the table column. The dollar signs keep the worksheet reference fixed as the formula is copied or filled.
Does Excel automatically copy formulas down a table?
Usually, yes. Entering a formula in a table column commonly creates a calculated column and fills the formula through its data rows. If it does not, check that the cells belong to a real table and review the Fill formulas in tables to create calculated columns option in Excel’s AutoFormat As You Type settings.
Why does my formula change when I drag it down?
Excel adjusts relative references as formulas move. Add dollar signs to any row or column that must stay fixed. A reference such as G2 changes when filled; $G$2 does not. Keep references to each table row relative or use current-row structured references where appropriate.
Do structured references need dollar signs?
No, not for the usual current-row table reference. A reference such as [@Quantity] identifies the Quantity value in the formula’s current row. Use dollar signs for an A1-style cell address that must remain fixed, such as a setting stored in G2.
What is the shortcut to make a reference absolute?
On Windows, select the cell reference while editing the formula and press F4 until the reference has dollar signs before both its column and row. On Mac, use Command+T to cycle the reference style; if the shortcut is unavailable on your keyboard, type the dollar signs directly.