Forum Discussion

Joshua_Norlin's avatar
Joshua_Norlin
Copper Contributor
Jun 15, 2024

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?

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 15, 2024

    Joshua_Norlin 

    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.

    • Joshua_Norlin's avatar
      Joshua_Norlin
      Copper Contributor
      Jun 16, 2024

      Thanks, this worked. Is there a way to have this work automatically? My version is 2019.

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jun 16, 2024

        Joshua_Norlin 

        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.