User must select a range of cell for applying an Array formula in excel?

[i8890321]

I've been a user switching between google sheet and ms excel.
I notice that the google sheet and ms excel treat the array formula differently.

In google sheet, if i have an array formula like A1:A3+B1:B3, all i need to do is select the cell C1 and input that formula then, press ctrl shift enter for "arrayformula(...)", then the result will appears in C1:C3. I don't need to care about the dimension of my array as long as there are blank cells for the result.

But in ms excel, doing the same thing need me to FIRST selecting the cell ranging from C1:C3 then, pressing ctrl shift enter for array formula.

My point is, is there any tricks to avoid step "selecting the output range C1:C3" ?

I googled on the internet,

Array Formula: How It Differs in Google Sheets and Excel

Array Formula in Google Sheets and Excel differ entirely. First, see some of the benefits of using Google Sheets and Excel Array Formulas.

infoinspired.com

The site shows the same experience like i have.

Thanks for any reply.

 

[Peter_SSs]

You do not say what version of Excel you are using, but with the latest versions you simply need to put the formula in the first cell and the other results automatically 'spill' to the other required cells. No Ctrl+Shift+Enter at all as in the latest versions, all formulas are evaluated as array formulas if possible.

23 02 28.xlsm
	A	B	C
1	6	2	8
2	1	3	4
3	7	5	12
Array

Cell Formulas
Range	Formula
C1:C3	C1	=A1:A3+B1:B3
Dynamic array formulas.

My point is, is there any tricks to avoid step "selecting the output range C1:C3" ?

If you have an older Excel version then I think the answer is "no"

 

[i8890321]

You do not say what version of Excel you are using, but with the latest versions you simply need to put the formula in the first cell and the other results automatically 'spill' to the other required cells. No Ctrl+Shift+Enter at all as in the latest versions, all formulas are evaluated as array formulas if possible.

23 02 28.xlsm
	A	B	C
1	6	2	8
2	1	3	4
3	7	5	12
Array

Cell Formulas
Range	Formula
C1:C3	C1	=A1:A3+B1:B3
Dynamic array formulas.

If you have an older Excel version then I think the answer is "no"

Thanks for your reply.
My Excel version is excel 2016.
So what version support that function about "automatically 'spill' to the other required cells" ??

 

[Peter_SSs]

My Excel version is excel 2016.

Please add that to your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

So what version support that function about "automatically 'spill' to the other required cells" ??

Microsoft 365 or Excel 2021 I believe.

 

[Fluff]

Even if you upgrade Excel to 2021 or 365, the formula will not be compatible between Xl & Sheets.