Forum Discussion
Array Formulas
Hello! I have a problem with array formulas. Let's say I have an array in cells A1:A3 (A1 = 1, A2 = 2, A3 = 3, for instance). If I type in cell D1 the formula = A1:A3, D2 and D3 will be left empty while D1 shows 1. The formula will still show =A1:A3. If I move the target cell to D2, the formula in D2 shows =A1:A3, but cell D2 now shows 2 while cells D1, D3 and D4 are blank. If I move the target cell to D6, the formula shows =A1:A3 while D6 shows #Value. Is there something that I need to enable to have cells D1 through D3 show the values from A1 to A3?
Select D1:D3.
Enter the formula =A1:A3 and confirm the formula by pressing Ctrl+Shift+Enter.
This will convert the formula to an array formula. You'll see { } around the formula: {=A1:A3}
However, you should not type the { } yourself.
Remark: in Excel in Microsoft 365 and Office 2021, pressing Ctrl+Shift+Enter isn't necessary anymore - all formulas are automatically array formulas.
Thanks, this worked. Is there a way to have this work automatically? My version is 2019.
In Excel 2019, formulas are not array formulas by default. You have to select the entire range to apply the formula to, and you have to press Ctrl+Shift+Enter.
