Excel tables do not use dollar signs to make structured references absolute: a reference such as Orders[Amount] continues to refer to that table column when you copy a formula. To lock an ordinary cell reference, use dollar signs—for example, $F$1—or use a table’s structured reference when you want a reference that follows the table and its columns.
What “absolute reference” means in an Excel table
In a standard worksheet formula, an absolute reference uses dollar signs to keep a row, a column, or both from changing when the formula is copied. For example, $F$1 locks both the column and row, while F$1 locks only the row and $F1 locks only the column. These are ordinary cell references, not table references.
Excel tables use structured references, which identify data by table and column names rather than by worksheet coordinates. For example, Orders[Amount] means the Amount column in the table named Orders. Structured references have their own behavior: Excel keeps the reference tied to that table and column when rows are added or the table moves. You cannot add dollar signs to a structured reference to make it “more absolute.”
This distinction matters because “absolute” can mean two different things in a question about tables. You might want a formula to keep pointing to one fixed cell when copied, or you might want it to keep pointing to a particular table column as the table grows. Dollar signs solve the first problem; structured references are usually a better fit for the second.
How structured references identify table data
A structured reference can name a whole table column, a current-row value, or a special part of a table. The table name and column name make the reference easier to interpret than a coordinate such as D2:D500. Common forms include:
Orders[Amount]refers to the data cells in the Amount column of Orders.[@Amount]refers to the Amount value in the current row when the formula is entered inside the table.Orders[[#Headers],[Amount]]refers to the Amount column’s header cell.Orders[[#Totals],[Amount]]refers to the Amount cell in the table’s totals row, if that row is turned on.Orders[#Data]refers to the table’s data rows, excluding its header and totals row.
Excel generally inserts structured-reference syntax for you when you type a formula and select cells in a table. Table and column names with spaces or punctuation use additional brackets in some forms. If a table is renamed, Excel updates references to it; if a column is renamed, references to that column normally update too. That is one reason structured references are useful in workbooks that change over time.
What happens when you copy a table formula
Inside a table, a formula that uses a current-row reference is designed to calculate separately for each row. When you enter a formula in a table column, Excel can fill it down as a calculated column, applying the formula to the other rows and to new rows added later. The @ indicates the current row; it does not mean “absolute.”
For instance, suppose a table named Orders has Quantity and Unit Price columns. With those columns in the table and the current row containing a quantity of 3 and a unit price of 12.50, the formula =[@Quantity]*[@[Unit Price]] returns 37.50 in that row. In another row, the same formula uses that row’s values. You do not need to change it to =$Quantity*$Unit Price; that is not valid structured-reference syntax.
Outside the table, a reference such as Orders[Amount] remains associated with the Amount column when used in a formula. This differs from a relative cell reference, which may shift as you copy it. Structured references are not ordinary A1 references with hidden dollar signs, though: their behavior depends on the table and reference type. Check the resulting formula if you copy it across columns or combine structured and cell references, rather than assuming every copy operation behaves like dragging a formula down one table column.
Examples: table references and dollar signs
Consider a table named Orders with an Amount column containing 120, 80, and 150. With those three values in Orders[Amount], the formula =SUM(Orders[Amount]) returns 350. The table reference includes the column’s data, and it can include additional table rows as the table expands. You do not need to predict a final row number or edit a range such as D2:D4 when another order is added.
A fixed cell is different. Suppose F1 contains a tax rate of 8%, and B2 contains a taxable amount of 100. With the taxable amount in B2 and the rate in F1, the formula =B2*$F$1 returns 8. If you copy it down one row, B2 becomes B3 while $F$1 stays fixed. This is a useful combination: the amount changes by row, while the shared rate does not.
To enter an absolute reference while editing a formula in desktop Excel for Windows, select the cell reference in the formula bar or formula, then press F4 to cycle through relative, absolute, and mixed-reference forms. On many Mac keyboards, use Command+T to cycle reference types; keyboard settings and function-key behavior can vary, so you may need to use Fn with the key or edit the dollar signs directly. These shortcuts apply to ordinary cell references, not to structured references such as Orders[Amount].
Choosing between a structured reference and a fixed cell
Choose the reference style based on what the formula is supposed to keep pointing at. Structured references are usually the clearest choice when the source data is a table and the formula should follow a named field as the table changes. Absolute cell references are appropriate when a formula depends on one particular worksheet cell, such as a single rate, target, or assumption.
- Use a current-row reference such as
[@Amount]for a calculation based on the values in the same table row. - Use a table-column reference such as
Orders[Amount]for a calculation that uses the whole column, such as a total or lookup source. - Use
$F$1when a copied formula must keep pointing to cell F1. - Use a mixed reference such as
F$1or$F1when only one dimension should stay fixed as the formula is copied.
For example, a per-row calculation can use table columns while a common assumption remains in a worksheet cell. This gives the table part room to grow without giving up a fixed input. If the assumption itself belongs with the data and should be named and managed as a field, consider putting it in a table or using a named range; do not choose a reference style solely because it looks more “locked.”
Common problems and misconceptions
A frequent mistake is typing dollar signs into structured-reference syntax. Excel does not use forms such as Orders$[Amount] or $Orders[Amount] to lock a table reference. Use the table and column name as structured-reference syntax provides them, or switch to a regular cell reference with dollar signs if a fixed worksheet coordinate is the actual requirement.
Another source of confusion is the @ symbol. In a table formula, [@Amount] identifies the current row’s Amount value. It is not a shortcut for an absolute reference, and it does not make the column constant when copied in every possible direction. Similarly, a whole-column reference such as Orders[Amount] should not be confused with a current-row reference.
If Excel does not recognize a reference, check the table name and column heading, including spaces and punctuation. Click a cell in the intended table column while editing the formula to let Excel insert the correct syntax. Also check whether the formula is inside the table: a current-row reference makes sense in a table row, while an external formula may need the table name and column name. If the table’s totals row is enabled, be clear whether the formula should refer to data rows or to a total cell.
Frequently Asked Questions
Can I use dollar signs in an Excel table reference?
No. Dollar signs lock row or column coordinates in ordinary cell references such as $A$1. Structured references use table and column names and do not have a dollar-sign form.
Does an Excel table reference stay fixed when I copy a formula?
A structured reference remains tied to its named table and column rather than shifting like a relative A1 reference. Its exact behavior can depend on whether it refers to a current row, a column, or a special table section, so inspect the formula after copying it in a different direction.
How do I reference the current row in a table?
Use the at sign in a structured reference, such as [@Quantity], inside a table formula. It refers to the Quantity value in that formula’s row, not to an absolute cell address.
How do I keep one cell fixed in a formula next to a table?
Use an ordinary absolute reference, such as $F$1, for the fixed cell. You can combine that with structured references or with a relative reference for values that should change by row.
Will a structured reference include new table rows?
A reference to a table column, such as Orders[Amount], refers to that column’s data and normally includes rows added to the table. This is a key advantage over a manually specified range that stops at a fixed row number.
Practical takeaway
There is no separate “absolute structured reference” syntax in Excel. Use table references to stay connected to named table data as it changes, use @ for the current row, and use dollar signs on ordinary cell references when a row or column coordinate must remain fixed during copying. Once you identify what needs to stay the same—the table field, the current row, or a particular worksheet cell—you can choose the reference form that expresses that intent clearly.
Related
- Data Validation Greyed Out in Excel? Causes and Fixes
- Excel Data Validation Guidelines for Reliable Spreadsheets
- Excel Data Validation From Another Workbook: Best Methods
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Create a Custom Distribution List in Outlook and Use ItTo create a personal distribution list in Outlook, open People, choose New contact list in new Outlook or…
- How to Filter Rows in Google SheetsTo filter rows in Google Sheets, select a cell in your data, choose Data > Create a filter,…
- Conditional Formatting Rows in Excel: A Practical GuideTo conditionally format an entire row in Excel, select the rows and columns you want formatted, choose Home…
- Power Query Basics: Import, Clean, and Transform DataPower Query is Excel’s data-import and transformation tool: it connects to files, databases, and other sources, then lets…
- How to Wrap Text in Google Sheets Manually: Cell GuideTo wrap text manually in Google Sheets, select the cell or range, then choose Format > Wrapping >…
- How to Present from Notes in PowerPoint with Presenter ViewTo present with your speaker notes visible only to you, add notes to each slide, turn on Presenter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.