Use Named Ranges in Excel Table Formulas Without #SPILL!
Question details
Learn how to avoid a #SPILL! error when formulas using named ranges are entered in an Excel table.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to multiply Price and Quantity values in each row of an Excel table while using named ranges.
- Observed behavior
- A formula such as =price*quantity refers to complete named ranges, returns an array, and cannot spill within an Excel table.
Confirm that price and quantity are names referring to entire worksheet columns or multiple cells. Identify whether the formula is being entered in a table calculated column or in a normal range outside the table.
Use row-specific structured references in the Excel table
Use this method for a calculation that should return one result per row in a table calculated column.
Excel tables cannot contain a dynamic-array result that spills into neighboring table cells. Structured references with the @ symbol use only the values from the current table row.
Click the first data cell in the table column where you want to calculate the result, such as the Total column.
Type =[@price]*[@quantity] in the formula bar or directly in the selected table cell.
Press Enter. Excel fills the formula down the table column and calculates each row using its own price and quantity values.
Place the named-range array formula outside the Excel table
Use this option when you need the full array produced by the named ranges rather than one result per table row.
Calculate Each Table Row with Structured References in WPS Spreadsheets
WPS Spreadsheets provides familiar spreadsheet formula tools for calculating values row by row. Use table-style references for per-row calculations, or place multi-cell array results in an open range outside your data table.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the table.
- 2. Select the table calculation cell: Click the first cell in the table column where the row total should appear.
- 3. Enter the current-row calculation: In the formula bar, enter =[@price]*[@quantity] to multiply the Price and Quantity values for the current row.
- 4. Confirm and review the column: Press Enter and verify that each table row displays an individual result rather than a spilled array.
Frequently Asked Questions
Why does =price*quantity show #SPILL! inside an Excel table?
If price and quantity refer to entire columns or multi-cell ranges, the formula returns multiple values. Excel tables do not allow that dynamic array to spill across table cells.
What does the @ symbol mean in an Excel table formula?
In a structured reference, @ means the current row. For example, =[@price]*[@quantity] multiplies only the price and quantity values in the same row.
Can I use named ranges in an Excel table calculated column?
Yes, but names that refer to a single cell can be used directly. For names that refer to multiple cells or whole columns, use row-specific structured references for calculations inside the table.
Where can I use =price*quantity without a #SPILL! error?
Enter it in a normal worksheet cell outside the table, where the required spill range is empty and is not blocked by cells, merged cells, or another table.
