To keep an Excel table column fixed when copying a formula, refer to the column by its table name, such as Orders[Amount], rather than by a shifting cell address. Structured references name the table and column, so copying the formula does not turn the reference into a neighboring column; table references do not use dollar signs.
What “fixed” means in an Excel table
In a regular worksheet formula, a reference such as B2 is relative: copying the formula one column right can change it to C2. A dollar sign makes an A1-style reference absolute or partly absolute, as in $B$2 or $B2. Excel tables offer another approach. A structured reference identifies a table column by its heading, so a formula can keep pointing to the Amount column without relying on its current letter.
For example, if a table is named Orders, Orders[Amount] means the Amount data column in that table. The table could move to another part of the sheet, or columns could be inserted before Amount, without changing what the reference means. This is usually what someone means by keeping a table column fixed in a formula.
There are two useful kinds of table reference:
Orders[Amount]refers to the Amount column’s data rows.[@Amount]refers to the Amount cell in the current row, when the formula is inside the table.
The first is useful for calculations across a whole column; the second is useful for a row-by-row calculation. Neither requires a dollar sign.
How structured references work
A table reference is built from the table name and one or more column headings. Excel uses the heading text, not the column’s worksheet letter. This makes formulas easier to understand later: Orders[Amount] tells a reader what the referenced values represent, while a reference such as $D$2:$D$100 may not.
Excel also has row-specific structured references. The at sign in [@Qty] means “this row.” When you enter a formula in a table column, Excel commonly fills that formula down the calculated column and adjusts the current-row context for each row. A formula referring to another named table column remains tied to that heading even if the table’s layout changes.
Structured references can also identify parts of a table with item specifiers. For example, Orders[#Headers] refers to the header row, and Orders[#Data] refers to the table’s data rows. A regular reference such as Orders[Amount] is generally the right choice when the calculation should use the data values rather than headers or a total row.
Set up a table and identify its columns
In Excel for Microsoft 365 on Windows, select a cell in your data and choose Insert > Table, or press Ctrl+T. Confirm that the selected range is correct and check My table has headers if the first row contains column names. Give the table a meaningful name by selecting it and using Table Design > Table Name. The table name cannot contain spaces, but column headings can.
For instance, suppose the table has headings Item, Qty, Unit Price, and Amount. If Excel named it Table1, you can change its name to Orders. The exact name appears in structured references. If a heading has spaces, include the heading in brackets; Excel will format the reference correctly when you select the column while building a formula. On Mac, select the table to show its contextual table ribbon tab; the tab’s label can vary by Excel build, but the table name field is in that contextual tab.
Use distinct, descriptive headings. Excel uses headings as identifiers, and changing a heading can update formulas that refer to it. If you rename a table, Excel normally updates its structured references as well. When entering a formula, typing the table name and an opening bracket can display available columns in formula autocomplete.
Examples of formulas that keep the column fixed
Assume Orders contains the rows below, with Amount values of 12, 20, and 8. In any cell outside the table, use this formula to total the entire Amount data column:
With a table named Orders and Amount values 12, 20, and 8, enter =SUM(Orders[Amount]). It returns 40.
The named column stays Amount if you copy this formula to another cell, including one to the right. It also includes new data rows when they become part of the table, which can be more convenient than revising a fixed range such as D2:D100.
For a row-by-row calculation, put the formula in the Amount column of the table. With Qty and Unit Price columns in the current Orders row, enter =[@Qty]*[@[Unit Price]]. If that row has a quantity of 3 and a unit price of 4, the result is 12. Excel can fill the formula down the calculated column, using each row’s quantity and unit price.
For a conditional total, suppose G2 contains the region to include and Orders has Region and Amount columns. With that layout, enter =SUMIFS(Orders[Amount],Orders[Region],$G$2). If G2 contains East and the East rows have Amount values 12 and 8, the result is 20. The structured references keep the calculation tied to the Amount and Region columns; $G$2 separately keeps the criteria cell fixed if you copy the formula.
Copying formulas and the difference from dollar signs
Structured references and absolute references solve related but different problems. A structured reference fixes the identity of a table column by name. A dollar sign fixes a row or column position in an A1-style reference. You do not add dollar signs to a table column reference: forms such as Orders[$Amount] are not the way to lock a structured reference.
If you copy =SUM(Orders[Amount]) horizontally, its named column reference remains Amount. By contrast, a formula using a relative worksheet reference can shift as it is copied. If you deliberately need ordinary A1-style behavior, use the dollar signs appropriate to the task: $B$2 fixes both the column and row; $B2 fixes the column but allows the row to change; B$2 fixes the row but allows the column to change.
Do not confuse a fixed column with a fixed row. Orders[Amount] denotes the column’s data, while [@Amount] denotes the Amount value for the current table row. The at-sign form is not an absolute reference; it is a row-context reference. It is suitable for formulas inside a table, but it does not mean “always use the first row” or “keep one particular cell unchanged.”
Common problems and how to resolve them
- The table name is wrong: Click inside the table and check Table Design > Table Name. A formula that refers to a different table will not calculate the intended column.
- A column heading has spaces or punctuation: Use the exact heading. Excel may add nested brackets, as in
[@[Unit Price]], to distinguish the column name from the reference syntax. - The formula returns an error after editing a heading: Check whether the formula still points to the intended column. Excel updates structured references for many table edits, but a deleted or replaced column can leave a reference invalid.
- New rows are not included: Confirm the new records are part of the table, rather than merely typed below it. Pressing Tab in the last table cell or typing immediately below the table commonly expands it; you can also resize it from Table Design > Resize Table.
- The reference points to one row instead of the whole column: Inside a table,
[@Amount]is current-row-only. UseOrders[Amount]when the calculation needs the full data column.
Another source of confusion is the Total Row. A regular column reference such as Orders[Amount] is designed to refer to the data column; it is not a request to add the displayed total back into the calculation. If you are using table item specifiers or a formula that includes the total row, check the reference scope carefully to avoid counting a total twice.
When a regular absolute reference is a better choice
Structured references are usually the clearest choice when a formula should follow a table column as the table grows or changes position. But not every formula is written against a table. For a separate range or a formula that must point to a particular worksheet location, an A1-style absolute reference may be more appropriate. For example, $B$2 always identifies cell B2, whereas a table reference identifies a named table field wherever that field is located.
For formulas involving a table and an external input cell, combine the two reference styles. Use a structured reference for the table data and dollar signs for the input cell if that input should remain fixed during copying. This makes each part’s purpose explicit instead of trying to make one notation do both jobs.
Also consider whether you want a whole table column or a current-row value. A whole-column reference is appropriate for SUM, COUNT, and similar calculations across records. A current-row reference is appropriate for calculating a line total or margin for each record. Choosing the correct scope avoids both accidental shifts and unintended calculations.
Frequently Asked Questions
Do Excel table references need dollar signs?
No. Structured references such as Orders[Amount] identify table columns by name. Dollar signs apply to A1-style cell references, for example $B$2.
How do I lock a table column when copying a formula to the right?
Use the table’s named column, such as Orders[Amount], rather than a relative worksheet address. Copying a formula with that structured reference keeps it associated with Amount.
What does the @ symbol mean in a table formula?
In a reference such as [@Amount], @ means the value in Amount for the current table row. It is a row-context marker, not a dollar-sign-style lock.
Will a table formula include new rows?
A structured reference to a table column includes the table’s data rows, including rows added to the table. If new entries are outside the table, expand the table or add those rows to it.
Can I keep one worksheet cell fixed while referring to a table column?
Yes. Use the structured reference for the table column and an absolute A1 reference for the cell, such as $G$2. The table field remains named, while the dollar signs keep the criteria cell fixed when copying.
Why does my formula use [@Amount] instead of Orders[Amount]?
The formula may be inside the table, where Excel uses a current-row reference for that row’s value. Use the table-qualified form when the formula needs the whole Amount data column.
Related
- Excel German Number Format: Commas, Dates, and Currency
- Excel General Number Format: How It Works and When to Use It
- Excel Number Format: How Many Digits Can It Really Show?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIFS in Excel: Count or Sum Matching Values?SUMIFS in Excel adds numeric values that meet one or more conditions; it does not normally count matching…
- Excel Goal Seeking: Find a Target Value with What-If ToolsExcel Goal Seek finds the input value that makes a formula return a result you specify. In Excel…
- Excel Find and Remove Text: Replace, Formulas, and TipsTo find and remove text in Excel, press Ctrl+H, enter the text in Find what, leave Replace with…
- Days Between Dates in Excel: TODAY Formula and ExamplesTo calculate the number of days from a date in Excel to today, subtract the date cell from…
- COUNTIF Criteria Examples for ExcelIn Excel for Microsoft 365, COUNTIF counts cells in one range that meet one condition: enter the range…
- Excel RIGHT, LEFT, and MID Formulas: Syntax and ExamplesIn Excel, RIGHT extracts characters from the end of text, LEFT extracts them from the beginning, and MID…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.