To change the range of an array formula or edit the data source, you need to recreate the formula. Also, you cannot edit values in a range with an array formula.
When editing array formulas, the following is possible:
–If the array formula is set for a single cell, it can be overwritten with any other formula that is not an array formula, or a scalar value.
–If an array formula is specified for a range of cells, it cannot be overwritten by any other formula other than an array formula or a scalar value.
–If the array formula is set for a range of cells, it can be overwritten with another array formula (see the editing procedure described above). In this case, if the array formula contains manually entered values, the values in all cells with the array formula will be recalculated regardless of the recalculation mode selected. If an array formula contains range operations and returns a proportional array, then when the data in the source ranges changes, the values in the cells containing the formula will be recalculated only in automatic recalculation mode or when recalculation is called manually. In manual recalculation mode, only the current active cell of the formula will be recalculated.
To edit the array formula, do the following:
1.Select any cell containing the formula.
2.Edit the formula using one of the following methods:
•Double-click on the cell with the left mouse button and edit the cell.
•Go to the Formula bar and edit it in the row.
3.When editing is complete, press Ctrl+Shift+Enter(Windows, Linux) or ⇧Shift+⌘Cmd+⌤Enter (macOS).