Excel Spill Error: Does It Create New Rows?

No. Excel’s #SPILL! error does not create worksheet rows; it means a dynamic-array formula cannot place its results in the cells it needs. Clear or move whatever blocks the spill, or insert worksheet rows separately if you need actual new rows.

What a spill error means

In Microsoft 365 Excel, some formulas return several results instead of just one. Excel places those results in neighboring cells automatically, a behavior called spilling. The cell containing the formula is the anchor cell; the cells filled with its results make up the spill range. When Excel cannot put the results into that range, it displays #SPILL! in the anchor cell.

The error is about where formula results can go, not about the worksheet’s structure. Excel has not inserted rows, shifted cells, or expanded a table. It has simply failed to display the formula’s complete result. If the obstacle is removed, the formula may recalculate and fill the available cells without any row insertion.

For instance, a formula can return five values into five existing worksheet cells. Those cells remain in their original rows; Excel does not add five rows to the sheet. This distinction matters if you are building a report, adding records to a dataset, or expecting a formula to make a table longer.

How dynamic arrays use existing cells

A dynamic-array formula calculates a result whose size may be one cell or many. When the result has multiple values, Excel needs an unobstructed rectangular area starting at the formula cell. The array can spill down, across, or in both directions, depending on what the formula returns. The surrounding grid already exists: spilling fills cells in it rather than creating new worksheet rows or columns.

For a simple example, enter a formula in A1 on a blank worksheet. It returns five numbers in A1:A5, using cells that are already part of the worksheet.

With A1 available as the anchor and A2:A5 empty, enter =SEQUENCE(5). The result is 1, 2, 3, 4, and 5 vertically in A1:A5. It does not insert four new rows. SEQUENCE is available in Microsoft 365 and Excel 2021 or later.

Only the anchor cell contains the formula. The other displayed values belong to the spill range, so you generally cannot edit one of those result cells independently. Change the formula in the anchor cell or adjust its inputs to change the array. A dotted outline may show the intended spill area when Excel reports a blockage.

Find and clear the obstruction

The most frequent cause is that one or more cells in the intended spill range are not empty. A cell can block a spill even if it looks blank: it may contain a space, a formula that returns an empty string, or a value formatted so it is hard to see. Formatting by itself usually is not the obstruction; contents are what matter.

To investigate a #SPILL! result in Excel for Microsoft 365 on Windows:

Select the cell showing #SPILL! and inspect the warning icon or message for the reported cause.
Look at the outlined spill area. Check every cell in that area, not only the first cell below the formula.
If Excel offers Select Obstructing Cells in the error options, use it to locate cells in the way.
Review any values or formulas you find before clearing them. Move or remove only content that is safe to change, or move the spill formula to a larger clear area.

Do not delete worksheet rows just because the error says #SPILL!. Deleting rows can remove unrelated data and change references, while leaving the actual blocking cell untouched. If the obstruction is a necessary value, choose a different location for the formula or revise the formula so it returns fewer results.

Other reasons a spill can fail

A blocked cell is common, but it is not the only possible explanation. Check the error message and consider these situations:

Merged cells: A spill range cannot occupy cells that include merged cells. Unmerge the relevant area if appropriate, or place the formula somewhere else.
Excel table formulas: Dynamic-array formulas that spill are not supported inside an Excel table. Put the formula outside the table, leaving room for its result.
Worksheet edge: An array cannot extend beyond the worksheet’s available rows or columns. Move the formula to a position where the complete result fits.
Unpredictable array size: Some formulas cannot establish a usable spill range in particular circumstances. The error detail can identify this type of issue; changing the formula or using a more stable range may help.
Insufficient resources: A very large result can exceed what Excel can calculate or display in the available context. Reducing the input range or returning only the needed records can make the calculation manageable.

Excel worksheets have a fixed grid, including 1,048,576 rows per sheet. A spill formula does not extend that limit. A formula that would need more rows than remain below its anchor cannot place its entire result on that sheet.

Return a changing list without inserting rows

If your goal is to show a list that grows when source data changes, a dynamic-array formula can be useful. The formula can return more results as the source gains qualifying records, provided the cells beneath or beside the formula are clear. That is a changing output range, not a structural insertion of worksheet rows.

For example, suppose A2:A5 contains Widget, Cable, Mouse, and Stand, while B2:B5 contains Open, Closed, Open, and Open. Put the following formula in D2, with D2:D4 free for the results:

With item names in A2:A5 and their statuses in B2:B5, enter =FILTER(A2:A5,B2:B5="Open","No matches") in D2. It spills Widget, Mouse, and Stand into D2:D4. If a status changes, the returned list can recalculate; Excel still does not insert rows. FILTER is available in Microsoft 365 and Excel 2021 or later.

When a spill grows, it needs more unoccupied cells. For example, if a fourth item becomes Open, D5 must also be available. If it is occupied, Excel may show #SPILL! rather than pushing that cell’s contents down. Dynamic arrays do not automatically move existing content out of their way.

Spill formulas and Excel tables

An Excel table is a structured data object with column headers, filter controls, and features such as calculated columns. It is different from a spill range. A table’s data rows are managed as part of the table, while a spill range is the output of a formula occupying cells on the worksheet.

If you want a filtered list based on table data, keep the source data in a table and put the dynamic-array formula in a clear area outside it. For example, a table named Sales might have Item and Status columns. A FILTER formula can refer to those columns and spill matching items beside the table, but the formula itself should not be placed in a table cell if it needs to spill.

To add a genuine table record, enter data in the row immediately below the table or use the table-row insertion command. Excel often expands a table when you type directly underneath it, though automatic expansion depends on the application’s settings. Check that the new record is actually included in the table before relying on table formulas or reports.

Use the table when you need a maintained dataset with real records and columns. Use a spill formula when you need a calculated view of existing records. They can work together, but one does not turn into the other: a formula result is not automatically a newly inserted table record.

Choose the right way to add rows

The right solution depends on what “new rows” means in your task. If you need more visible formula results, clear room for the spill or move the formula. If you need to add records to a dataset, add rows to the worksheet or table. If you need a repeatable import or transformation, consider a data-import workflow rather than expecting a cell formula to alter the sheet structure.

More calculated results: Use a dynamic-array formula outside a table and leave enough blank cells for its output.
More source records: Insert worksheet rows or add rows to the relevant Excel table, then enter the records.
Repeated data imports: Power Query can load and refresh transformed data into a worksheet or table. Its refresh output is distinct from a formula inserting rows one at a time.
Automated structural changes: A script or macro can manipulate workbook structure, but that is a separate automation task and requires suitable permissions and safeguards.

In Excel for Mac and Excel for the web, dynamic-array formulas have the same basic spill concept, although menus and controls can differ from Windows. If a workbook is opened in an older Excel version that does not support a particular dynamic-array function, compatibility may also affect what users see. Microsoft 365 and Excel 2021 or later support functions such as SEQUENCE and FILTER.

Frequently Asked Questions

Does #SPILL! mean Excel needs more rows?
No. It means Excel cannot place the formula’s full result in its intended spill range. The worksheet grid is already there; clear a blocker, move the formula, or reduce the output.

Will Excel push existing cells down to make room?
No. A spill formula will not move existing values, formulas, or other content to create space. Move the formula or safely relocate the obstructing content yourself.

Can a spill formula add rows to an Excel table?
No. A spilled result is not a table-row insertion. Put spill formulas outside the table, and add actual records to the table using its row-entry or insertion features.

Why does #SPILL! appear when the cells look empty?
A cell may contain a space, a formula returning an empty string, or another value that is not obvious from its display. Select the obstruction Excel identifies and inspect its contents before clearing it.

How do I make a list grow when new records are added?
Use a dynamic-array formula that refers to the growing source, place it outside a table if it must spill, and keep the output area clear. The list can grow as the formula returns more matches, but it does not insert worksheet rows.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.