Approaches to editing array formulas in Excel

Using array formulas in Excel has a number of advantages. Even the complexity of working with them can often be taken as an advantage, since complexity immediately weeds out amateurs. In this tutorial, we will explore two approaches to editing array formulas in Excel. They’re not perfect, but you might find them helpful.

Approach #1

The figure below shows an array formula whose size needs to be expanded from C3:C8 to C1:C10.

To do this, follow these steps:

  1. Select the range of cells that will contain the new array. The main thing here is that the new array overlaps the old one, otherwise Excel will not let you finish editing and will issue a warning. In our case, this is the range C1:C10.
  2. Next, you need to make sure that the active cell is the one that belongs to the old array. This will preserve the structure of the formula, albeit with “false” cell references. Since in our case an empty cell is active, we select the appropriate one. Let’s say C3:

You can make a cell active in the selected range using the key Enter.

  1. Let’s switch to the formula editing mode (key F2). At this stage, you can redefine the areas involved in the formation of the array. In our case, we will reset both areas:
  2. Save the changes in the formula by pressing the key combination Ctrl + Shift + Enter. The array formula will be updated.

Approach #2

The second approach can be applied to both reduce and increase the size of an array in Excel. The main thing here is that the old and new arrays have at least one common cell.

The figure below shows an array whose range needs to be reduced from C1:C10 to C1:C5.

To do this, follow these steps:

  1. Select the range containing the array formula.
  2. Press the key F2to enter edit mode.
  3. Press the key combination Ctrl + Enter. This action enters a separate formula into each of the cells in the range. Moreover, these formulas are not array formulas. For example, cell C3 contains the following formula:
  4. Reset the range that the new array will be in and make the active cell from the old array. This will save the formula, albeit with “false” references. In our case, we will simply reduce the range size to C1:C5:
  5. Switch back to edit mode. At this stage, you can redefine the areas involved in the formation of the array. In our case, we will reset both areas:
  6. Press the key combination Ctrl + Shift + Enterto save changes. The array size will be reduced.
  7. Remove unnecessary formulas from the C6:C10 range.

As you can see, both approaches are quite complex and confusing, so their real applicability in Excel is a big question. All the manipulations carried out above come down to one thing – save the formula. To be more precise, its structure, since links in most cases have to be redefined. It’s much easier to copy the formula from the old array, then delete it and create a new one.

So, in this lesson, we looked at several approaches to editing array formulas. Whether you use them or not is your right. 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
  • Editing array formulas in Excel
  • Applying array formulas in Excel

Leave a Reply