Excel Structured References Return One Value Instead of Spilling a Table Column
Question details
The user wants an Excel structured reference to return and spill an entire table column instead of returning only one value.
- Product
- Microsoft Excel tables and structured references
- Device & OS
- not provided
- Scenario
- A formula references a column in an Excel table and is expected to display the full column outside the table.
- Observed behavior
- The structured reference returns a single value, often the value from the current row, rather than spilling the full column.
Check the table name and column header in Table Design before editing the formula. Make sure the cells below and to the right of the formula cell are empty so a dynamic-array result has room to spill.
Enter the full table-column reference outside the Excel table
Use this method when you want a structured reference to spill every value from a table column in Microsoft 365 Excel.
A formula entered inside a table is often evaluated in the context of the current row. Entering the formula in a normal worksheet cell outside the table lets Excel return the complete referenced column when dynamic arrays are supported.
Click an empty worksheet cell that is not part of Table1 and has clear cells underneath it for the spilled results.
Type =Table1[Column] in the formula bar, replacing Table1 and Column with the exact table and column names, then press Enter.
If Excel shows a #SPILL! error, select the formula cell and clear or move any values, merged cells, or other content blocking the outlined spill range.
Use the #Data item specifier to return only the table data body
Use this reference when you need the column's data rows and do not want a header or totals-row value included.
Verify dynamic-array support and remove implicit intersection
Use this check when the formula still returns one value outside the table or includes an implicit-intersection operator.
Use WPS Spreadsheet for Compatible Excel Table and Formula Workbooks
If Excel version differences or workbook behavior interrupt your spreadsheet work, WPS Office provides a free, lightweight alternative with a familiar spreadsheet interface. Open and edit compatible Excel files, continue working with tables and formulas, and migrate between office suites with minimal disruption.
Frequently Asked Questions
Why does =Table1[Column] return only the current row value?
This usually happens when the formula is entered inside the table. Excel treats structured references in table calculated columns as current-row references. Enter the formula in a cell outside the table to return the full column in a dynamic-array version of Excel.
What is the difference between Table1[Column] and Table1[[#Data],[Column]]?
=Table1[Column] references the named table column, while =Table1[[#Data],[Column]] explicitly references only its data body. Use #Data when you want only data rows and want to exclude table header or totals-row context.
What does the @ symbol mean in an Excel structured reference?
The @ operator applies implicit intersection, which reduces a reference to one value for the current row. Check the formula bar and remove @ only when the formula is outside the table and should return a spilled array.
Why does Excel show #SPILL! for a table column reference?
The destination cells for the array are blocked. Select the formula cell, review the highlighted spill range, and clear values, unmerge cells, or move objects that prevent the results from expanding.
