Most of the MS Excel users might have encountered the following error when you try to delete the array formula from a cell.
You cant change part of an array
This can be really annoying as you cannot delete the existing formula on that cell(the one that has wrong values) nor add anything new to that cell. If you are looking forward to fixing this issue, read along. In this article, we will first be discussing the issue and then find ways to fix the same
Understanding the issue with sample data
1. Consider a table with sample data as shown below. The Cost column should have the value corresponding to Cost Per Item*Number of Items
2.But as seen above, Only the first entry ( 25 *10=250) has the right value. All other values in the Cost column hold wrong values.
3. In order to correct the value from the second row (45*10=250) of the cost column, you click on the cell as shown below and try to delete it.
4. An error stating You cant change part of an array is displayed. This message is seen when you try to delete any or all of the other cells within the Cost Column.
5. Let’s say you are trying to select a few cells with the wrong value and then delete them, you see the same error
This value can’t be deleted or modified and the error message keeps popping up again and again.
Now, that we have understood the issue, let us find different ways to fix this issue.
Fixing the You cant change part of an array
Step 1: Click on any one of the cells that you are not able to delete
Step 2: From the top menu, make sure you are in the Home tab
Step 3: From the Editing section, click on Find and Select
Step 4: From the drop-down, choose Go to Special
Step 5: In the window that opens, click on the Current array
Step 6: Click on OK
Step 7: You can see that all the array elements that belong to that are array is highlighted as shown below
Step 8: Delete all the cells
Step 9: Apply the formula again
Step 10: Hover the mouse around the point shown below. A Plus symbol appears. Drag and drop to extend the formula to required rows
Step 11: The table should look like follows
We hope this article has been informative. Thank you for Reading.
Kindly comment and let us know if this helped you fix the issue.
Stay tuned for more tips and tricks.
Anusha Pai is a Software Engineer having a long experience in IT industry and having a passion to write.