7

Suppose, I want to reduce an array size (count of cells). Array had been created by Ctrl+Shift+Enter.

If I try to select part of array and press Ctrl+Shift+Enter, Excel prints an error message

"You cannot change part of an array."

It does not allow me to change the array size, because I had not selected the whole array.

So, I have to select all the array, remove it, select the reduced cell range and enter the formula again (I save the formula into clipboard before this).

Is there any method without the array removal?

Improve this question

1 Answer 1

Reset to default
11

Select the array, for example A1 to A10, click in the formula bar and hit Ctrl-Enter. Now each cell will contain the formula as a regular formula. Select the smaller range, for example A2 to A9, click in the formula bar and then hit Ctrl-Shift-Enter. Now the smaller range has the array formula and you can remove the non array entered formulas from cells A1 and A10.

Improve this answer
1
  • 3
    Very valuable tip!
    – Gary's Student
    Commented Feb 20, 2016 at 21:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .