Remove Columns from a Dynamic Array in Microsoft Excel
Question details
The user needs to remove selected columns from a spilled dynamic array without rebuilding its source formula.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A three-by-three dynamic array is spilled in a worksheet, and the user wants to keep only part of it or remove columns from either end.
- Observed behavior
- The array is generated by a formula and needs to be reduced with a dynamic-array function rather than manually recreated.
Confirm that the workbook is opened in Microsoft 365 or a current Excel version supporting dynamic arrays, DROP, and TAKE. Identify the spill reference, such as A1#, and make sure the destination cell has enough empty space for the resulting array.
Use DROP to remove the last two columns from the spilled array
Use this formula when you want to remove columns from the right side while preserving the remaining rows and columns.
DROP removes a specified number of rows or columns from an array. A negative column value removes columns from the end of the array, so -2 removes the final two columns.
Click a blank cell outside the existing spill range where the shortened array can expand.
Type =DROP(A1#,,-2), replacing A1# with the reference to your spilled array, then press Enter.
Verify that the result contains the original rows with the final two columns removed. If Excel displays #SPILL!, clear the cells blocking the output range.
Use DROP or TAKE for other column selections
Use these variations when you need to remove columns from the left or keep a specific number of columns.
Work with Excel-Compatible Spreadsheets in WPS Office
WPS Office is a free, lightweight Microsoft Office alternative with a familiar spreadsheet interface and strong XLSX compatibility. It provides a convenient way to open, review, and migrate Excel workbooks while keeping common spreadsheet formatting and formulas accessible.
Frequently Asked Questions
What formula removes the last two columns from a spilled array in Excel?
Use =DROP(A1#,, -2) without the space, written as =DROP(A1#,,-2), where A1# refers to the anchor cell and its full spilled range.
How can I remove the first two columns instead?
Use =DROP(A1#,,2). A positive column argument removes columns from the left side of the array.
Can I keep only the first column without using DROP?
Yes. Enter =TAKE(A1#,,1) in an empty cell to return only the first column of the spilled array.
Why does Excel show a #NAME? error for DROP or TAKE?
The Excel version may not support these dynamic-array functions. Use Microsoft 365 or a current Excel release with DROP and TAKE support.
What does the # symbol mean in A1#?
The # spill operator refers to the entire dynamic array starting at cell A1, including cells that the formula currently spills into.
