Fix the Excel Table #SPILL! Error When Using SEQUENCE
Question details
The user needs to generate sequential IR numbers in an Excel table without triggering a #SPILL! error from SEQUENCE.
- Product
- Microsoft Excel table formulas
- Device & OS
- not provided
- Scenario
- An IR Number column uses SEQUENCE to create sequential identifiers inside a structured table.
- Observed behavior
- The SEQUENCE formula returns a #SPILL! error when entered inside the Excel table.
Confirm the IR Number column is inside the table and identify the table name if you plan to use structured references. Check the first data-row cell and the table header row before entering the replacement formula.
Replace SEQUENCE with a row-based IR number formula
Use this solution when SEQUENCE produces a #SPILL! error inside an Excel table and each row needs one sequential IR number.
Dynamic-array functions such as SEQUENCE may not work reliably inside structured tables because the table expects a value in each row rather than a spilled range. A row-based formula calculates the identifier independently for every record.
The formula below adds the prefix NWJDC and starts the numeric sequence at 1000. Adjust the prefix or starting value if your numbering scheme is different.
Open the worksheet, click the first data-row cell in the table's IR Number column, and remove or replace the formula that uses SEQUENCE.
Type ="NWJDC"&ROW(B2)-ROW($B$1)+999 in the selected cell, then press Enter. Change B2 and $B$1 if your table starts in a different column or row.
Use the table's calculated-column fill behavior or drag the fill handle down the IR Number column so each row receives its own sequential value.
For a table named Table1, enter ="NWJDC"&ROW(Table1[@])-ROW(Table1[#Headers])+999 in the IR Number column. Replace Table1 with the actual table name if necessary.
Generate sequential IR numbers in WPS Spreadsheets without #SPILL! errors
WPS Spreadsheets can open compatible Excel workbooks and use a row-based formula in a table column to generate sequential IR numbers without relying on a spilling SEQUENCE range.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and select the Excel .xlsx workbook containing the table.
- 2. Select the first IR Number cell: Click the first data-row cell in the table's IR Number column and place the cursor in the formula bar.
- 3. Enter the compatible formula: Type ="NWJDC"&ROW(B2)-ROW($B$1)+999 in the formula bar, then press Enter.
- 4. Fill the table column: Copy the completed cell or drag its fill handle down the IR Number column so every table row receives a sequential identifier.
Frequently Asked Questions
Why does SEQUENCE return #SPILL! inside an Excel table?
SEQUENCE is a dynamic-array function that returns a spilled range, while a structured table expects formulas and values to be calculated row by row. This conflict can cause the #SPILL! error.
What formula generates sequential IR numbers without SEQUENCE?
Enter ="NWJDC"&ROW(B2)-ROW($B$1)+999 in the first IR Number cell and fill it down the table column. The row calculation creates a separate number for each record.
How can I use a structured reference instead of cell references?
If the table is named Table1, enter ="NWJDC"&ROW(Table1[@])-ROW(Table1[#Headers])+999 in the IR Number column. Replace Table1 with the actual table name.
Can I change the IR number prefix?
Yes. Replace the text "NWJDC" in the formula with your preferred prefix, such as "IR-". Keep the ROW calculations unchanged if you want the same sequential numbering.
Will this formula work in WPS Spreadsheets?
Yes. Enter the row-based formula in the first data row of the table and fill it down the IR Number column. WPS Spreadsheets supports compatible Excel workbook formulas and .xlsx files.
