Array formulas

Edit and delete array formulas

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Editing an array formula requires special steps, and this video shows you what they are. You'll also see how to delete array formulas.

Want more?

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.

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×