Contents
In the previous lessons, we discussed the basic concepts and information regarding arrays in Excel. In this lesson, we will continue to study array formulas, but with a greater emphasis on their practical application. So, how do you change an already existing array formula in Excel?
Rules for editing array formulas
When an array formula is placed in one cell, then editing it in Excel is usually not particularly difficult. The main thing here is not to forget to finish editing with a key combination Ctrl + Shift + Enter.
If the formula is multicell, i.e. returns an array, then certain difficulties immediately arise, especially for novice users. Let’s look at a few rules that you need to understand before you start editing an array.
- You cannot change the contents of a single cell containing an array formula. But each cell can have its own formatting.
- You cannot delete cells that are part of an array formula. You can only delete the entire array.
- You cannot move cells that are part of an array formula. But you can move the entire array.
- You cannot insert new cells, including rows and columns, into an array range.
- You cannot use multicell array formulas in tables created with the command Table.
As you can see, all the rules listed above emphasize that an array is one whole. If you do not follow at least one of the above rules, Excel will not let you edit the array and will issue the following warning:
Selecting an array in Excel
If you need to change an array formula, the first thing to do is select the range that contains the array. In Excel, there are at least 3 ways to do this:
- Select array range manually, i.e. using the mouse. This is the simplest, but in some cases absolutely unsuitable method.
- Using the dialog box Select a group of cells. To do this, select any cell that belongs to the array:And then on the Home tab from the drop down list Find and select click Select a group of cells.
A dialog box will open Select a group of cells. Set the radio button to Current Array and click OK.
The current array will be highlighted:
- Using key combinations CTRL+/. To do this, select any cell in the array and press the combination.
And then on the Home tab from the drop down list Find and select click Select a group of cells.
How to delete an array formula
The easiest thing you can do with an array in Excel is to delete it. To do this, just select the desired array and press the key Delete.
How to edit an array formula
The figure below shows an array formula that adds the values of two ranges. It can be seen from the figure that when entering the formula, we made a small mistake, our task is to correct it.
To edit an array formula, do the following:
- Select the range of the array using any of the methods known to you. In our case, this is the range C1:C12.
- Switch to the formula editing mode by clicking on the formula bar or by pressing the key F2. Excel will remove the curly braces around the array formula.
- Make the necessary adjustments to the formula:
- And then press the key combination Ctrl + Shift + Enterto save changes. The formula will be edited.
Resizing an array formula
Very often there is a need to reduce or increase the number of cells in an array formula. I will say right away that this is not an easy task and in most cases it will be easier to delete the old array and create a new one.
Before deleting the old array, copy its formula as text and then use it in the new array. With cumbersome formulas, this approach will save a lot of time.
If you need to change the location of the array on the worksheet without changing its dimension, just move it like a normal range.
There are several approaches to editing array sizes that you may find useful. The approaches are given in this lesson.
So, today you have learned how to select, delete and edit array formulas, and also learned some useful rules for working with them. If you want to learn even more about arrays in Excel, read the following articles:
- Introduction to array formulas in Excel
- Multicell array formulas in Excel
- Single cell array formulas in Excel
- Arrays of constants in Excel
- Applying array formulas in Excel
- Approaches to editing array formulas in Excel