Fix: Cannot Change part of an Array in MS Excel

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


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 


Excel Sample Data



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.


Excel Dlete Second Row Value



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.



Excel Sample Data With Error



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



Select Multiple Rows 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



Editing Go To Special



Step 5: In the window that opens, click on the Current array

Step 6: Click on OK



Current Array



Step 7: You can see that all the array elements that belong to that are array is highlighted as shown below


Selected Rows



Step 8: Delete all the cells

Step 9: Apply the formula again


Apply The Formula



Step 10: Hover the mouse around the point shown below. A Plus symbol appears. Drag and drop to extend the formula to required rows



Drag And Drop To All The Rows


Step 11: The table should look like follows


Finsla Formula Result


That’s All

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.