Editing an Array Formula in Excel 2007

Editing array formulas in Excel 2007 differs somewhat from editing normal formulas. In editing an array range, you must treat the range as a single unit and edit it in one operation (corresponding to the way in which the array formula was entered).

You cannot edit, clear, move, insert, or delete individual cells in the array range. If you try, Excel will display an Alert dialog box stating “You cannot change part of an array.”

To edit the contents of an array formula, follow these steps:

1. Select a cell in the array range and then activate Edit mode by clicking the formula in the Formula bar or pressing F2.

   
   

   Excel displays the contents of the array formula without the customary braces. Excel also outlines the ranges referred to in the array formula in different colors that match those assigned to the range addresses in the edited formula on the Formula bar.

   
   



2. Edit the array formula contents.

   
   



3. Press Ctrl+Shift+Enter to enter your changes.

   
   

   Excel encloses the array formula in braces once again.

   
   

If you want to convert the results in an array range to their calculated values, select the array range and click the Copy button on the Ribbon’s Home tab or press Ctrl+C. Then, without changing the selection, click the Paste Values option on the Paste button’s drop-down menu. As soon as you convert an array range to its calculated values, Excel no longer treats the cell range as an array.

dummies

Source:http://www.dummies.com/how-to/content/editing-an-array-formula-in-excel-2007.html