Edit and delete array formulas

Editing an array formula requires special steps.

If the array formula is in a single cell, like Total, select the cell, press F2, edit the formula,

such as expanding or contracting the arrays, and then press Ctrl + Shift + Enter.

If the array formula is in multiple cells it’s trickier.

Select all of the cells, press F2, edit the formula, and press Ctrl + Shift + Enter.

Cell F5 displays an error because we contracted the arrays in the formula.

I want to delete the cell, but you can’t delete individual cells that are in an array formula;

you get this error that says you can’t change part of an array. You can delete all of the cells and then re-create the formula.

You can’t add a new cell to a block of cells that already contain an array formula.

I couldn’t add it to G5, for example.

You can type the same array formula in the cell, but then you’d need to remember to update both array formulas.

We recommend that you delete the original formula and re-create it.

You can’t move individual cells that are in an array formula; you’ll get the same error.

You can move all of the cells. Select the cells, right click them, and click Cut.

Right-click where you want to paste them, and click Paste. And the cell references will remain the same.

Now you’ve got a pretty good idea about how to use array formulas. Of course, there’s always more to learn.

So check out the course summary at the end, and best of all, explore Excel 2013 on your own.