Function Problems

Understanding Excel Structured References: Using [@Number] and Table[Column]

Camila Milosovich Oct 10, 2026 872 views

Question details

The user wants to understand the meaning and functionality of structured references generated by Excel tables, specifically references like DataRange_1[Number] and [@Number].

Understanding Excel Structured References: Using [@Number] and Table Names

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Reviewing or writing formulas within a formatted data table.
Observed behavior: Instead of traditional cell references like A1 or B2, the spreadsheet automatically displays structured names using brackets and the @ symbol when referencing table columns.

Before you start

Ensure that your dataset is formatted as a Table (Insert > Table), as structured references will only work within recognized table formats rather than standard cell ranges.

Solution 1Recommended

Decoding Structured References and the @ Symbol

Learn the exact meaning of table column references and current-row operators to read and write dynamic formulas accurately.

Structured references make formulas easier to read and maintain by using defined table and column names instead of standard cell addresses (like A1:A10). When a table expands, structured references automatically include the new data without needing formula adjustments.

Identify the Table Name

In a reference like DataRange_1[Number], 'DataRange_1' refers to the unique name of the entire table. You can locate or change this table name in the Table Design tab on the ribbon.

Understand the Column Reference

The section inside the brackets, such as [Number], refers to the entire column named 'Number' within that specific table. This is highly useful for functions like SUM or AVERAGE that need to evaluate the whole column.

Decode the Current-Row Reference (@)

The @ symbol in [@Number] represents the 'current-row reference'. It tells the spreadsheet to fetch the value from the 'Number' column that specifically resides in the exact same row where you are writing the formula.

Decoding Structured References and the @ Symbol

Implicit Intersection: The @ symbol identifies a current-row reference, replacing older implicit intersection methods. It ensures the formula only calculates using the data point on that specific row, preventing accidental array spillage errors.

Solution 2

Writing Formulas Using Structured References

How to create your own dynamic formulas leveraging the structured reference syntax within a table.

Advanced Table Management

Master Table Formulas Easily with WPS Spreadsheet

WPS Office fully supports Excel structured references, allowing you to use [@Column] and Table[Column] syntax seamlessly. It makes managing large datasets and dynamic calculations straightforward and highly efficient.

1. Format Data as a Table: Highlight your dataset in WPS Spreadsheet and press Ctrl+T (or go to Insert > Table) to format it as a recognizable data table.
2. Generate a Structured Reference: Type '=' in a new column and click on a cell in the same row. WPS Spreadsheet will automatically generate the [@ColumnName] syntax for you.
3. Apply to the Entire Column: Press Enter to finalize the formula. WPS Spreadsheet will instantly apply the calculated structured reference to the entire column.

Natively supports structured table references and the @ symbol operator.100% format compatibility with Microsoft Excel (.xlsx) files and table behaviors.Formulas automatically expand when new rows are added to the table.Free, lightweight, and features an intuitive interface familiar to Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

What does the @ symbol mean in modern spreadsheet formulas?

The @ symbol indicates an implicit intersection or current-row reference. It instructs the software to use only the single value from the current row of a specified column, rather than attempting to evaluate the entire column as an array.

Why did my formula automatically change to use table names instead of cell addresses?

When you convert a range of data into a Table, the spreadsheet defaults to using structured references (like Table1[Column]) because they are dynamic. If the table grows or shrinks, formulas using structured references update automatically without needing manual corrections.

How can I change structured references back to normal cell references like A1?

If you prefer traditional cell formulas over structured references, click anywhere inside your table, navigate to the Table Design tab on the ribbon, and select 'Convert to Range'. This removes the table formatting and reverts all structured references back to standard cell addresses.

Can I use a structured reference outside of the table?

Yes. When writing a formula outside the table, you must include the table name along with the column name (e.g., =SUM(DataRange_1[Number])). However, you cannot use the [@Number] syntax outside the table row structure unless it perfectly aligns with the table's rows.

Excel & Data Problems