Function Problems

Fix Excel Cannot Autofill Dynamic Array Formulas (TEXTSPLIT)

Maira Mehtab Sep 27, 2026 871 views

Question details

The user is unable to use the double-click autofill handle for dynamic array formulas like TEXTSPLIT, despite standard single-cell formulas filling down correctly.

How to Fix Excel Cannot Autofill Dynamic Array Formulas

Product: Excel
Device & OS: not provided
Scenario: Attempting to quickly copy a formula that splits text or generates arrays down an entire column of data.
Observed behavior: Double-clicking the fill handle works for traditional formulas, but fails to populate dynamic array formulas down the column because the results spill into adjacent cells.

Before you start

Verify that your version of Excel fully supports dynamic arrays (Microsoft 365 or Excel 2021) and ensure your workbook is not saved in an older Compatibility Mode (.xls).

Solution 1Recommended

Manually Drag the Fill Handle

Since dynamic array functions alter Excel's double-click autofill boundaries, manually dragging the fill handle is the most direct workaround.

Dynamic array functions naturally spill results into adjacent empty cells. Because the formula output spans multiple columns, Excel's automatic double-click logic cannot correctly determine the bottom boundary of your data, causing the action to do nothing.

Select the formula cell

Click on the cell containing your dynamic array formula (e.g., TEXTSPLIT).

Locate the fill handle

Hover your mouse cursor over the bottom-right corner of the selected cell until the cursor changes into a solid black cross.

Drag to fill

Click and hold the left mouse button, then manually drag the cursor down the column to the last row where you want the formula applied. Release the mouse to calculate the results.

Spill Errors: Ensure that the cells adjacent to your drag path are empty. If there is existing data blocking the way, Excel will return a #SPILL! error.

Solution 2

Use Traditional Text Functions Instead

Replace dynamic arrays with traditional single-cell functions to restore the standard double-click autofill behavior.

Solution 3

Disable Compatibility Mode

Check your workbook format to ensure legacy settings aren't restricting the correct execution of modern formulas.

Easily Split Text without Formulas

Split Text and Data Smoothly in WPS Spreadsheet

If Excel's dynamic array spilling behaviors are frustrating your workflow, you can easily separate text into multiple columns using the built-in Text to Columns feature in WPS Office—no complex formulas required.

1. Open your workbook in WPS: Launch WPS Spreadsheet and open your existing data file.
2. Select the target data: Highlight the column of text you want to split into separate parts.
3. Launch Text to Columns: Navigate to the 'Data' tab on the top ribbon and click on 'Text to Columns'.
4. Apply delimiters: Choose 'Delimited', select your separator (such as a space, comma, or custom character), and click 'Finish' to instantly parse your data.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Intuitive Text to Columns wizard to split data without relying on array formulas.Reliable and predictable data manipulation avoiding #SPILL! errors.Free, lightweight, and offers a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

What does it mean when a formula 'spills' in Excel?

Spilling occurs when a dynamic array formula, such as TEXTSPLIT, FILTER, or SORT, returns multiple values. Instead of fitting all the data into a single cell, Excel automatically places the results into adjacent empty rows or columns, creating a 'spill range'.

Why does double-clicking the fill handle stop working for array formulas?

The double-click autofill feature relies on adjacent data columns to accurately determine how far down to copy a formula. Because dynamic array formulas spill across multiple cells horizontally or vertically, Excel's autofill logic cannot correctly map the boundary, causing the double-click action to be ignored.

Can I use TEXTSPLIT without spilling into multiple columns?

By default, TEXTSPLIT is designed to separate text into multiple cells, which natively causes a spill. If you only want a specific segment of the split text to remain in a single cell, you can wrap your TEXTSPLIT formula inside an INDEX function, such as =INDEX(TEXTSPLIT(A1, "-"), 1, 2) to retrieve only the second segment.

Excel & Data Problems