Editing an Array Formula in Excel - dummies

By Conrad Carlberg

Inevitably there comes a time when you have to make a change in an array formula in your Excel worksheet. If it’s a small change that you want to make, it’s usually pretty easy to do so.

Suppose you want to change a range address used in an array formula from A2:A21 to A2:A22. Just select a cell in the array formula’s range, make your change, and press Ctrl+Shift+Enter. Just as when you array-enter a formula to begin, you need to use all three keys when you edit it. However, you need not select the entire range occupied by the array formula in order to edit it. You have to do that when you establish the formula, but not when you edit it.

Sometimes you need to reduce the dimensions of the range that an array formula occupies. Then, it’s a little trickier to manage. Suppose that a LINEST analysis tells you that you might as well forecast column C from column A, instead of from both columns A and B.

Now you want to do two things:

  • Remove the reference to column B from the LINEST arguments.
  • Remove one column from the range that LINEST occupies.

If all you did was to remove the reference to column B from the LINEST arguments, you’d wind up with a bunch of #N/A values in its final column. So, one way to manage things is to select a cell in the array formula’s range, delete the equal sign at the start of the formula, and reenter as a conventional formula what has become a text value by Ctrl+Enter.

Then you select the original range except for its final column. Alter the LINEST argument as necessary (including replacing the equal sign) and establish the edit with Ctrl+Shift+Enter. Finish by clearing what had been the range’s final column — you can do that now because it’s no longer part of an existing array formula. You now have a revised formula with no detritus from the original version hanging around.