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.