Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsSome links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
You can insert a worksheet row above or below a dynamic array, but you cannot type an independent value into the array’s spilled results. To add a row to the results, change the source data or build the extra row into the formula. Which method is right depends on whether you mean a row on the worksheet, a source-data record, or a row in the returned array.
First, identify which kind of row you need
A dynamic-array formula is entered in one top-left cell. Excel generates the other results in the spill range; those cells are not separate, editable formula cells. For example, if you enter this in E2, its results might fill E2:G20:
=FILTER(A2:C100,C2:C100="Open")You can select the output to see its spill range, but edit the formula in E2, not an individual result cell. The reference E2# means the entire current spill range starting at E2; it expands or contracts as the result changes. See Microsoft’s dynamic-array behavior guide and spilled-range operator reference.
- Worksheet row: Changes the physical grid and may move the formula and its output.
- Source-data row: Adds a record for the formula to process. Usually best when the result is generated from a list or table.
- Array row: Adds a header, blank line, note, or custom record to the formula’s returned results. Change the formula rather than typing into the spill.
Insert a worksheet row above the array
Use this when you want to move the output down or make room above it. Select the worksheet row heading containing the formula’s top-left cell, then choose Home > Insert > Insert Sheet Rows. You can also right-click the row heading and choose Insert. Excel inserts a physical worksheet row and adjusts cell positions; it does not add an item to the formula’s result.
For instance, if the formula is in E2, inserting worksheet row 2 moves the formula and its spill output down. Check formulas or references elsewhere in the workbook that rely on the old positions. Microsoft’s row and column insertion instructions also cover selecting multiple worksheet rows before inserting them.
Insert a worksheet row below the current spill
If you need separate worksheet content underneath the current output, insert a row below the last row currently occupied by the spill: select that worksheet row heading, right-click, and choose Insert. This is not a permanent safe boundary if the formula can return more rows. If the array later grows into the new content, Excel may show #SPILL!.
For a variable-height result, keep separate content on another worksheet or in a reserved area with enough clear space. If the extra line belongs with the results, append it in the formula with VSTACK instead.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Add a row to the returned array with VSTACK
In modern Excel builds that include VSTACK, use it to prepend or append a row as part of the formula’s complete output. The added row must have the same number of columns as the array.
Add a header above filtered results:
=VSTACK(
{"ID","Customer","Status"},
FILTER(tblOrders,tblOrders[Status]="Open")
)Append a blank row for visual spacing:
=VSTACK(
FILTER(tblOrders,tblOrders[Status]="Open"),
{"","",""}
)Or append a custom record:
=VSTACK(
FILTER(tblOrders,tblOrders[Status]="Open"),
{"1001","New customer","Open"}
)To put that record above the filtered results, place its array constant before FILTER. A row created this way is still part of the spill: it is not a separate worksheet row that you can edit independently. The destination cells must be clear, and an empty or error result from the inner formula may require appropriate error handling.
Add a source record so the result updates automatically
If you want the filtered results to include another real record, add it to the source data rather than editing the output. For a more robust setup, format the source as an Excel Table named tblOrders with columns such as OrderID, Customer, and Status, then put this formula in a normal worksheet cell outside the Table:
=FILTER(tblOrders,tblOrders[Status]="Open")To add a Table record, select a cell in the Table, right-click, and choose Insert > Table Rows Above or Insert > Table Rows Below. Enter the new values in the row. The Table resizes and its structured references adjust, so the formula can include the new record when it matches the filter. See Microsoft’s guide to adding or removing Table rows.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchImportant: Put the source data in the Table, but place the spilling formula in the worksheet grid outside it. Spilled-array formulas are not supported inside Excel Tables. A Table is also generally safer than a fixed range such as A2:C100, which excludes a record entered in row 101 unless you update the formula’s range.
Fix #SPILL! after inserting or adding a row
#SPILL! often means Excel cannot place the results in the required cells; it does not necessarily mean the formula is wrong. Check these common causes:
- Something occupies the output area. Move or remove the existing value or formula blocking the intended spill range, then check that the output area is clear.
- The output overlaps a merged cell or a Table. Move the formula or clear a destination area that can accept the spill. Do not place the spilling formula inside a Table.
- Manually entered content sits below a variable spill. Move that content elsewhere, add it through the source data, or include it with
VSTACK. - The output would extend past the worksheet edge. Excel’s worksheet limit is 1,048,576 rows. Move the formula higher, use a bounded or Table-based source, or filter out unnecessary blank rows. See Microsoft’s explanation of spill errors at the worksheet edge.
If the formula depends on a spill in another workbook, note that the # spilled-range operator does not support references to a closed source workbook; the reference may return #REF! until that workbook is open. See Microsoft’s operator guidance.
Dynamic arrays are not the same as legacy array formulas
| Dynamic array | Legacy CSE array formula | |
|---|---|---|
| Formula location | Top-left cell generates the spill | Entered across a selected range |
| Output size | Can expand or contract | Fixed to the selected range |
| Typical entry | Press Enter | Ctrl+Shift+Enter |
Inserting or deleting rows or columns inside an active legacy Ctrl+Shift+Enter array range can be restricted. Do not assume instructions for a spilling formula apply to a legacy array formula; Microsoft explains the differences in its dynamic-array and legacy CSE comparison. Dynamic-array support and individual functions also vary by Excel edition and platform. Check availability for your version, particularly before using VSTACK; older non-dynamic-aware versions handle these formulas differently (see Microsoft’s note on non-dynamic-aware Excel).
Recommended Free Tools
Choose a layout that can grow
Keep source records in a Table and put the dynamic result outside it, in a dedicated area or on a separate worksheet. Avoid placing fixed content immediately beneath a result whose height can change. When another formula needs to use the whole current result, refer to its anchor with the spill operator—for example, =E2#—rather than guessing the result’s current address.
Quick Recap
| Your goal | Use this approach |
|---|---|
| Move the output down | Insert a worksheet row above the formula. |
| Place unrelated content below it | Insert a worksheet row below the current spill, but leave room for future growth or move the content elsewhere. |
| Include a new data record | Add a row to the source Table or update the source range. |
| Add a header, blank line, note, or custom record to the results | Build the row into the formula, for example with VSTACK. |
| Edit one returned result manually | Change the source or formula; if a fixed snapshot is genuinely needed, copy the results and paste as values in a separate area. |
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
