Formula Errors

How to Lock an Excel Table Column in a Structured Reference Formula

Jason Miller Oct 14, 2026 870 views

Question details

The user needs to make a structured table reference absolute so that the referenced column does not change when copying or dragging the formula horizontally across adjacent columns.

How to Lock an Excel Table Column in a Structured Reference Formula

Product: Microsoft Excel
Device & OS: not provided
Scenario: Writing formulas using structured references (Table references) and dragging them horizontally to fill adjacent columns.
Observed behavior: By default, structured references behave relatively when dragged horizontally, causing the referenced column to shift. The goal is to lock the reference to a specific column.

Before you start

Ensure your data is formatted as an official Table (using Insert > Table) and identify the exact name of your table and the specific column you want to lock.

Solution 1Recommended

Use the Duplicated Range Syntax to Lock the Column

By repeating the column name separated by a colon within the structured reference brackets, you force the spreadsheet to treat it as an absolute column reference.

Standard absolute references (like $A$1) do not apply to structured table references. Instead, you must use a range syntax that references the column to itself.

Reference a single row context

To lock a reference for a single row within the table, use the @ symbol and duplicate the column name. For example, type `=Table1[@[Quantity]:[Quantity]]`.

Reference the entire column

To lock a reference for the entire column instead of a single row, omit the @ symbol. Type `=Table1[[Quantity]:[Quantity]]`.

Apply and drag the formula

Press Enter to apply the formula. Click the cell, grab the fill handle in the bottom-right corner, and drag it horizontally across adjacent columns. The reference will remain fixed on the 'Quantity' column.

Use the Duplicated Range Syntax to Lock the Column

Pro Tip: This workaround effectively mimics the $ column lock (e.g., $A1) used in traditional cell references, ensuring your calculations remain accurate when expanding formulas across a dashboard.

WPS Spreadsheet Solution

Lock Table References Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced Excel functions, including structured references for tables. You can use the exact same formula syntax to lock columns while enjoying a lightweight, fast, and highly compatible spreadsheet application.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data table.
2. Format data as a table: If not already formatted, highlight your data and go to the Insert tab, then click Table.
3. Enter the locked structured formula: Type your formula using the duplicated column syntax, for example: `=Table1[[Column]:[Column]]`.
4. Drag to fill: Drag the formula horizontally across adjacent cells; the column reference will stay locked perfectly.

Fully compatible with Microsoft Excel formulas, structured references, and table formatting.Free and lightweight alternative for comprehensive spreadsheet data management.Familiar user interface that requires no learning curve to apply advanced formula techniques.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the F4 key make structured references absolute?

The F4 keyboard shortcut is exclusively designed for standard A1-style cell references (converting A1 to $A$1). It does not natively format structured table references, which require the duplicated range syntax (like [@[Column]:[Column]]) to stay locked.

Does locking a column in a structured reference work across different sheets?

Yes. As long as you include the table name in your formula (e.g., =Table1[[Column]:[Column]]), you can place this formula in any worksheet within the same workbook, and it will remain strictly locked to that specific table and column.

How do I lock both the row and the column absolutely in a structured reference?

Structured references inherently adjust to the row context (via the @ symbol) or encompass the entire column. If you need to lock a specific single cell absolutely across both rows and columns (like $B$5), it is usually better to use standard absolute cell references or the INDEX function rather than a structured reference.

Excel & Data Problems