How to Build Excel 2007 Formulas with the Insert Function Dialog Box
Using AutoSum for Quick Calculations in Excel 2007
Deciphering Error Values in Excel 2010 Formulas

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.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Deconstructing Time with Excel 2007's HOUR, MINUTE, and SECOND Functions
Simplifying Numbers with Excel 2007's INT and TRUNC Functions
Examining Investment Value with Excel 2007's PV and FV Functions
Rounding Numbers in Excel 2007 with ROUND, ROUNDUP, and ROUNDDOWN
Understanding Excel 2007's Formula Auditing Tools
Advertisement

Inside Dummies.com