Yes—you can use absolute cell references in Excel table formulas by adding dollar signs to the cell address, such as $H$2. For example, a table formula can combine a row-specific structured reference with a fixed worksheet cell: =[@Units]*[@UnitPrice]*(1+$H$2).
What an absolute reference does
An absolute reference keeps a cell address fixed when a formula is copied or filled into another location. In a reference such as $H$2, the dollar sign before H locks the column, and the one before 2 locks the row. That means the reference continues to point to H2 rather than shifting to another cell as the formula is copied.
This matters when a calculation combines changing row data with a constant elsewhere in the worksheet. Examples include a tax rate, commission percentage, exchange rate, discount threshold, or target value. You generally want the row-specific amount to change for each record while the shared assumption remains in one cell.
Excel also supports partly fixed references. H$2 keeps row 2 fixed but allows the column to change, while $H2 keeps column H fixed but allows the row to change. Those mixed references are useful when copying formulas across a grid. For a single shared input used by a table column, the fully absolute form, such as $H$2, is usually easiest to understand.
How table formulas handle references
Excel tables use structured references to identify columns and rows by their names. In a calculated column, a reference such as [@Units] means the Units value on the current table row. When Excel fills the formula down the column, each row uses its own Units value. You do not have to adjust a conventional row number in the formula.
That behavior is different from an ordinary cell reference such as A2. In a standard worksheet formula, a relative reference may shift when copied; in a table formula, a current-row structured reference stays meaningful as the formula fills down. The table does not need an absolute marker for that row-specific reference because its meaning is already tied to the current record.
A cell outside the table is different. If a table calculation refers to a shared value in H2, the ordinary cell address can move when Excel copies the formula down. Add dollar signs to lock it. The result is a mixed formula: structured references identify the relevant table row, and an absolute cell reference points to the fixed assumption.
Build a formula with a fixed worksheet cell
Suppose a table named Sales has Units and UnitPrice columns, and cell H2 contains a tax rate of 5%. Add a Tax-inclusive column to the table and enter the formula below in its first data row. The assumed first-row values are 3 units at 20 each, so the calculation is 3 × 20 × 1.05, or 63.
With the Sales table containing Units = 3 and UnitPrice = 20 on the current row, and H2 containing 5%, enter =[@Units]*[@UnitPrice]*(1+$H$2). The result is 63. Excel will normally fill the formula through the calculated column, applying each row’s Units and UnitPrice while continuing to use H2 for the tax rate.
To enter it, click the first data cell in the new table column, type the formula, and press Enter. If Excel does not fill the column automatically, check the calculated-column behavior in Excel’s AutoCorrect options, or copy the formula down the table column. When you add new rows to the table, Excel generally extends calculated-column formulas to those rows as well.
You can lock an existing reference while editing a formula. Select or place the cursor on the cell reference in the formula bar, then press F4 in Excel for Microsoft 365 on Windows to cycle through relative, absolute, and mixed forms. On a Mac, Command+T commonly cycles reference styles; depending on the keyboard and function-key settings, Fn+F4 may be available as well. The exact key behavior can vary with keyboard configuration.
Structured references are not made absolute with dollar signs
A common misconception is that a structured reference can be locked by adding dollar signs, as if it were a conventional address. Table references use their own syntax: Sales[UnitPrice] identifies the UnitPrice column in the Sales table, and [@UnitPrice] identifies that column’s value on the current row. Do not try to turn these into forms such as $Sales[$UnitPrice]; dollar signs are not the mechanism for fixing structured references.
Usually, there is no need to lock a table column reference. A formula that refers to a named table column continues to refer to that column when the table grows, and a current-row reference continues to mean the current record when the calculated-column formula is filled. This is one reason structured references are often clearer than hard-coded ranges.
There is an important distinction between a fixed column and a fixed worksheet location. If the goal is to use the same value stored in H2, use an absolute cell reference or define a workbook name for that cell. If the goal is to refer to the same table column, use its structured column name. The right approach depends on whether the value is an input cell or a table field.
What happens when formulas are copied or the table grows
Excel tables make formulas easier to extend, but they do not change the meaning of every reference in the same way. Structured references are designed to follow table rows and columns. Ordinary relative references can shift as formulas are copied, while absolute references remain anchored to their specified worksheet cells. Mixed references lock only the row or column marked with a dollar sign.
For example, if you copy a formula down one row, a reference to A2 normally adjusts to A3, while a reference to $H$2 remains $H$2. In a table calculated column, Excel often manages the fill for you, but the same distinction still matters for the ordinary cell reference embedded in the formula. Confirm the formula in a few rows if the workbook has unusual fill settings or manually edited formulas.
When inserting or deleting rows and columns, Excel may update references to preserve their relationship to cells. An absolute reference is not a promise that a value can never move; it means the reference is not adjusted merely because the formula is copied or filled. If you move the input itself, inspect the resulting formula and verify that it still points to the intended assumption.
Common problems and how to fix them
- The rate changes as the formula fills down. Check that both the row and column are locked, for example by using
$H$2, rather than leaving the reference relative. - The formula displays an error after a table column is renamed. Structured references depend on the table and column names. Use the current header name, and check for accidental spaces or misspellings in the header.
- The table fills formulas unexpectedly. A table calculated column normally applies one formula pattern to its rows. If different row formulas are intentional, verify the calculated-column settings and check whether Excel has restored a consistent formula.
- A copied formula points to the wrong input. Make sure the reference is absolute before copying. If only the row or only the column should stay fixed, choose the appropriate mixed reference instead of locking both parts.
- A formula is written outside the table. Structured references still work outside a table, but a current-row reference such as
[@Units]is intended for a row associated with that table. For a summary elsewhere, use a full column reference or a conventional cell address appropriate to the calculation.
Also check that the shared input cell contains a numeric value in the intended form. A percentage displayed as 5% is stored as 0.05, so multiplying by one plus that rate produces the expected increase. If a value is stored as text, the formula may return an error or an unexpected result.
When to use a named range instead
If a fixed input appears in many formulas, a defined name can make the formulas easier to read than repeatedly using an address such as $H$2. For example, name the cell TaxRate and refer to that name in the table formula. In Microsoft 365 for Windows, select the cell, use Formulas > Define Name, enter the name, and confirm the reference. Names must follow Excel’s naming rules and should not look like cell addresses.
With the Sales table’s current row containing Units = 3 and UnitPrice = 20, and a workbook-level name TaxRate referring to H2, which contains 5%, enter =[@Units]*[@UnitPrice]*(1+TaxRate). The result is 63. The defined name refers to the same input without needing dollar signs in the formula, and it can be easier to understand when several formulas share that assumption.
A name is not automatically safer than an absolute address. If it refers to the wrong cell, every formula using it will use the wrong input. Check the name’s scope and Refers to setting in Name Manager, especially when a workbook has both worksheet-level and workbook-level names with similar labels. Use a direct absolute reference for a simple one-off calculation; consider a descriptive name when the assumption is reused or needs to be obvious to other workbook users.
Frequently Asked Questions
Can I use $A$1 in an Excel table formula?
Yes. An absolute worksheet reference such as $A$1 can be used in a table formula and will stay fixed when the formula is copied or filled. Combine it with structured references when each table row needs its own values.
How do I lock a cell reference in a table formula?
Use dollar signs around the column letter and row number, such as $H$2. While editing the formula, select the reference and press F4 on Windows to cycle through reference styles; Mac keyboard shortcuts can depend on function-key settings.
Can I make a structured reference absolute?
Not with dollar signs. Structured references identify table columns or the current row, and Excel handles their table-aware behavior. Use an absolute cell reference or a defined name when you need to hold a worksheet input fixed.
Do table formulas automatically copy to new rows?
Usually, yes. Excel treats formulas in a table column as calculated-column formulas and commonly extends them to existing and new rows. If that does not happen, check Excel’s AutoCorrect settings and whether the formula column has been manually changed.
Why does my table formula keep changing the cell address?
The address is probably relative, so Excel adjusts it when filling the formula. Add dollar signs to lock the required row and column, then inspect a filled row to confirm the reference points to the intended cell.
Related
- Analysis Tab in Excel: Where It Is and What It Does
- Analysis Group in Excel: Where It Is and How to Use It
- Excel Analysis ToolPak: Setup, Uses, and Limitations
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP Formula in Excel to Find DuplicatesTo check whether a value in one Excel list also appears in another, use an exact-match VLOOKUP wrapped…
- Excel Formula Bot Free? Free Plans, Limits, and Safe UseYes—many Excel formula bots offer free access, but it is usually limited by usage, features, or account requirements.…
- Excel Merge Data From 2 Cells: Formulas and MethodsTo merge the contents of two cells in Excel, combine them with a formula such as =A2&" "&B2.…
- Xlookup vs vlookup?XLOOKUP defaults to exact matches and can return values from either side. See where VLOOKUP still fits and…
- Excel Compare Two Sheets: Formulas and ToolsTo compare corresponding cells on two Excel sheets, enter =IF(Sheet1!A1=Sheet2!A1,"Same","Different") in a comparison cell, then fill the formula…
- Excel Filter Shortcut: Turn Filters On and Use ThemIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off for the current…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.