Cái thông báo đó có nghĩa là Bạn không thể sửa 1 phần của 1 mảng. Vì vậy, nếu muốn xóa bạn phải xóa tất cả các ô mà bạn đã nhập công thức mảng này cùng lúc -> Chọn vùng đã nhập công thức mảng và nhấn nút delete.
You're trying to edit an array formula, and you're stopped in your tracks. You click the formula in the cell or formula bar and you can't change a thing. Array formulas are a special case, so do one of the following:
If you've entered a single-cell array formula, select the cell, press F2, make your changes, and then press Ctrl+Shift+Enter..
If you've entered a multi-cell array formula, select all the cells that contain it, press F2, and follow these rules:
- You can't move the individual cells that contain your formula, but you can move all of them as a group, and the cell references in the formula will change with them. To move them, select all the cells, press Ctrl+X, select the new location and press Ctrl+V.
- You can't delete cells in an array formula (you'll see a "You cannot change part of an array" error), but you can delete the entire formula and start over.
- You can't add new cells to a block of result cells, but you can add new data to your worksheet and then expand your formula.
- After you make your changes, press Ctrl+Shift+Enter.
Finally, you may be able to save time if you use array constants—parts of an array formula that you type in the formula bar. But, they also have a few editing and usage rules. For more about them, see Use array constants in array formulas.
Need more help?
Want more options?
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
If you get the You can’t change part of an array error in Microsoft Excel, this post will help you fix the error. An array is essentially a collection of items which may be text or numbers present in a row, column, or multiple rows and columns. An array formula is used to perform multiple calculations on a single or multiple items present in an array.
Some Excel users have reported getting this You can’t change part of an array error in Excel when trying to sort, edit, or delete cells in a worksheet. If you are experiencing this error, we will show you all the working solutions to fix it.
If you see You can’t change part of an array error while trying to use the Sort, Delete, Edit, or some other feature in Microsoft Excel, use the below methods to fix the error:
- Use Copy and Paste special functions.
- Use the Go to Special feature.
- Check for array formulas and use the Evaluate Formula feature.
- Some other basic troubleshooting tips and tricks.
1] Use Copy and Paste special functions
Some affected users have reported that selecting the entire array and copying and pasting them as values in a new grid helped them fix the error. So, you can try doing the same and see if it works for you. Here’s how to do that:
First, select all the array elements and copy them using the CTRL + C hotkey or the Home tab > Copy option.
Now, create a separate grid in the same worksheet.
Once the new grid is created, paste the earlier copied arrays. To do that, go to the Home tab and click on the Paste drop-down button. Next, select the Paste Special option.
Now, choose Values and click on OK.
See if you can sort the column now without the You can’t change part of an array error.
2] Use the Go to Special feature
If you are unable to delete cells from your worksheet due to this error, you can try using this workaround that helped some affected users. Here’s the workaround:
First, go to the Home tab and click on the Find & Select drop-down button.
Now, choose the Go to Special option.
Now, click on the Current array option in the opened prompt and press the OK button. This will highlight the entire array.
After that, press the Delete button to delete the highlighted cell values.
Once deleted, add the array formula again in the first cell and then copy it down to the required cells.
Read: Excel ran out of resources while attempting to calculate formulas.
3] Check for array formulas and use the Evaluate Formula feature
You can also try using Excel’s built-in Evaluate Formula feature to review array formulas. Here’s how you can do that:
First, select the problematic cell and look into its formula bar. If there are curly braces around the formula, it is an array formula.
Once the cell with an array formula is identified, select it and go to the Formulas tab on the ribbon.
Next, find the Evaluate Formula option present in the Formula Auditing group and click on it.
After that, in the prompt that appears, press the Evaluate button and check which part of the formula is causing the problem. Then, try fixing the issue according to the evaluation results.
4] Some other basic troubleshooting tips and tricks
There are some other troubleshooting tips and tricks you can use to resolve this error. Here are the tips and tricks you can follow:
- If you have applied conditional formatting rules, check them and check whether there are rules that reference arrays. For that, use Home > Conditional Formatting > Manage Rules.
- Check for named ranges and ensure that they are not causing the issue. You can use the Formulas > Name Manager option for that.
- If the workbook is password protected, unprotect it and then try making changes.
- If a VBA code is used, review and correct it.
- In case you get this error while sorting a column, you can try using the SORT function in another column.
Read: Fix There’s a problem with the clipboard Excel error.
I hope you won’t get this error now.
How do you fix An array error in Excel?
If you get an error like An array value could not be found in Excel, you can try using the ARRAYFORMULA function with the SUBSTITUTE function to manage multiple cells at once. Besides that, you can also use the REGEXMATCH, REGEXREPLACE, or combine the ARRAYFORMULA and REGEXMATCH formulas to fix this error.
How can I change part of an array in Excel?
To edit an array formula in Excel, click on the cell containing an array formula and then select the formula bar present at the top. You can now modify the formula as per your requirements. Once done, press the Ctrl+Shift+Enter button and then click on the File > Save button to save the modifications.