Monday, May 30, 2022

Rules for changing array formulas [MS]

You're trying to edit an array formula, and you're stopped in your tracks. You click the formula in the cell or formula bar and you can't change a thing. Array formulas are a special case, so do one of the following:

If you've entered a single-cell array formula, select the cell, press F2, make your changes, and then press Ctrl+Shift+Enter..

If you've entered a multi-cell array formula, select all the cells that contain it, press F2, and follow these rules:

  • You can't move the individual cells that contain your formula, but you can move all of them as a group, and the cell references in the formula will change with them. To move them, select all the cells, press Ctrl+X, select the new location and press Ctrl+V.

  • You can't delete cells in an array formula (you'll see a "You cannot change part of an array" error), but you can delete the entire formula and start over.

  • You can't add new cells to a block of result cells, but you can add new data to your worksheet and then expand your formula.

  • After you make your changes, press Ctrl+Shift+Enter.

Finally, you may be able to save time if you use array constants—parts of an array formula that you type in the formula bar. But, they also have a few editing and usage rules. For more about them, see Use array constants in array formulas.

No comments:

Post a Comment

Search This Blog

Upload process overview [MS]

Upload process overview Whenever you upload a video, Microsoft Strea...