Expand an array formula

You've created this great array formula, but now you need to add a dozen rows of data, and you're finding you can't just add that data to your formula. Here's what you need to do.

1. Select the range of cells that contains your current array formula, plus the empty cells next to the new data.

   

2. Press F2. Now you can edit the formula.

3. Replace the old range of data cells with the new one.

   For example, if your old data stopped at cells C11 and D11, and the new data ends at cells C13 and D13, replace C11 and D11 in the formula with C13 and D13.

4. Press Ctrl+Shift+Enter.

   Notes: 

   • Remember you can't shrink an array formula. If you expand an array formula too far, delete the formula and start over.

   • If you need to do more than expand an array formula, see Rules for changing array formulas. Also:

   • Delete an array formula (you press Ctrl+Shift+Enter there, too)

   • Use array constants in array formulas (they can be handy time savers)

   • Name an array constant (they can make constants easier to use)

Top of Page