How to Edit or Delete Part of an Excel Array Formula in WPS Office Spreadsheets
Question details
The user needs to edit or delete only part of an Excel-style array formula, but the application reports that part of an array cannot be changed.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is editing a workbook with a legacy CSE array formula or a dynamic array formula and wants to adjust or remove a portion of the array result.
- Observed behavior
- Selecting one cell in the array and trying to edit or delete it triggers the message that part of an array cannot be changed.
Open the workbook in WPS Office Spreadsheets, switch to the worksheet containing the formula, and identify whether you are working with a legacy array formula range or a dynamic array spill range before making changes.
Delete or edit a legacy array formula as a complete range
Use this procedure when you are working with a legacy CSE array formula and WPS Office Spreadsheets blocks changes to a single cell.
A legacy array formula is entered with Ctrl+Shift+Enter and must be edited or deleted as a single range. Trying to change one returned cell breaks the array consistency.
Click any cell inside the legacy array formula and press Ctrl+/ to select the full array range that belongs to that formula.
Press Delete to remove the complete array formula, or click the formula bar, modify the formula, and press Ctrl+Shift+Enter to re-enter the array in the entire selected range.
If you deleted it, type the corrected array formula in the first cell of the intended range, select the full output range, and press Ctrl+Shift+Enter to commit it.
Edit or delete dynamic array formulas from the spill source cell
Use this for newer dynamic array formulas where results spill into adjacent cells and only the source cell should be edited.
Edit and delete Excel array formulas correctly in WPS Office Spreadsheets
WPS Office Spreadsheets opens Excel workbooks and lets you manage legacy and dynamic array formulas with the same range rules as Excel. Use the built-in formula bar and keyboard shortcuts to select, edit, or delete the full array safely.
- 1. Select the array range: Open the workbook in WPS Office Spreadsheets, click any cell inside the legacy array formula, and press Ctrl+/ to select the whole range, or click the source cell of a dynamic array spill range.
- 2. Delete or edit the formula: Press Delete to remove the entire array formula, or click the formula bar and edit the formula directly.
- 3. Commit the array formula: For legacy array formulas, select the output range and press Ctrl+Shift+Enter to re-enter the formula; for dynamic array formulas, press Enter after editing the source cell.
- 4. Verify the results: Check the formula bar and spill range or array outline to confirm only the source formula remains and the corrected results populate.
Frequently Asked Questions
Why does Excel or WPS Office say part of an array cannot be changed?
Because legacy array formulas must be edited or deleted as one range; you cannot modify a single returned cell. Select the entire array range, delete it, and recreate the formula.
How do I select an entire array formula range in WPS Office Spreadsheets?
Click any cell inside the array formula and press Ctrl+/, or use the formula bar to see and edit the complete range before re-entering it with Ctrl+Shift+Enter.
How do I edit a dynamic array formula spill range?
Edit only the source formula cell in the top-left corner of the spill range. Press Enter after changing the formula, and WPS Office Spreadsheets updates the spilled results automatically.
Can I delete just one cell from an array formula spill range?
No. Spilled result cells are display-only. Select the source formula cell and press Delete to clear the entire spill range.
Will WPS Office open my Excel array formula files?
Yes. WPS Office Spreadsheets is compatible with Excel .xls and .xlsx files and can open, edit, and save legacy and dynamic array formulas.
