Formulas that result in arrays differ significantly from formulas that result in a single value. Arrays are really powerful in Excel that help you perform multiple calculations using just one formula. There are two types of arrays in Excel: Single-cell and Multi-cell arrays. The process of changing arrays differs depending on these types.
How to Change a Single-Cell Array in Excel
You can change a single-cell array in Excel just as how you would change a standard formula in Excel. The only difference is, you must use Ctrl + Shift + Enter to return the value, and not the Enter key. If you use Enter, your array will only return the first value of the array and not the complete set.
- Select your cell with the array formula.
- Use F2 to enable cell editing.
- Edit your formula in the formula bar.
- Use Ctrl + Shift + Enter to return a value.
How to Change Multi-Cell Array in Excel
A multi-cell array returns more than one value that spills over to the adjacent cells.
The ROW function, for instance, is a type of multi-cell array. If I type =ROW(1:10) in the formula bar and use Ctrl + Shift + Enter, the function will return numbers from 1-10 individually in adjacent cells.
While you can change the first value of a multi-cell array, that is, the cell with the formula, you cannot change the array spilled from the first cell. To change a multi-cell array, select the first cell where you’ve entered the formula and select F2. Head on to the formula bar, change the values, and use Ctrl + Shift + Enter.
If you decide to shrink your array, the cells that previously contained data will be changed to the #N/A error code. This simply means that there is no data to be displayed in the cell. You can select those data and select Clear contents from Home > Clear.
Limitations of Changing a Multi-Cell Array
There are a few changes you cannot make in a multi-cell array. If you relate to either of these instances, it’s best if you create a new formula to meet your requirements.
- You cannot extend a multi-cell array. For example, if your formula is
{=ROW(1:10)}and you wish to add two more numbers, changing the formula to{=ROW(1:12)}won’t work. - You cannot change the spilled array.
- You cannot move data from the spilled array. To move your data, however, you will need to change your formula to values. Copy your data and right-click on a cell. Select Values under the Paste Options.
- You can only make changes in the first cell.
Why am I Getting the “You Cannot Change Part of an Array” Message?
You selected a cell from an array and tried to edit it from the formula bar, or used F2 to enable cell editing mode. However, the “You cannot change part of an array” error stopped you in your tracks. Well, Excel does not allow you to change the values that have been spilled over from a multi-cell array formula.
When you enter a multi-cell array, Excel spills your value over to the neighboring cells. The spilled values are not individual values but extensions of the formula you entered in the first cell.
Therefore, if you wish to change the array, you must adjust the formula accordingly in the first cell where you entered your multi-cell array formula.