Guidelines and examples of array formulas

✅ Array Formulas in Excel – Guidelines & Examples

Array formulas are powerful tools in Excel that allow you to perform multiple calculations at once and return either a single result or multiple results.

🔹 Types of Array Formulas

1. Dynamic Arrays (Excel 365, Excel 2019+)
2. Legacy Arrays (older Excel versions — require Ctrl+Shift+Enter)

🧠 Basic Guidelines

➤ How to enter array formulas:

• Modern Excel (Dynamic Arrays): Just press Enter
• Legacy Excel: Press Ctrl + Shift + Enter to confirm. Excel will wrap the formula in {} automatically.

➤ Behavior

• Spill: Dynamic arrays “spill” results into adjacent cells automatically.
• Scalability: Array formulas can handle ranges and arrays of data efficiently.

📌 Examples of Array Formulas

1. 🔢 Sum of Squares

=SUM(A1:A5^2)

• Squares each value in A1:A5 and sums them.
• Works directly in modern Excel. Use Ctrl+Shift+Enter in older Excel.

2. 📊 Return Multiple Results (Spill Array)

=A1:A5 * B1:B5

• Multiplies each pair of values from columns A and B.
• Returns a vertical list of results in modern Excel.

3. 🔍 Filter with Conditions (Dynamic Array)

=FILTER(A2:A10, B2:B10="North")

• Returns only the values in A2:A10 where B2:B10 = “North”.

4. ✅ Count How Many Values Meet Multiple Criteria

=SUM((A2:A10="Apple")*(B2:B10="North"))

• Returns how many rows contain Apple and North.
• Requires Ctrl+Shift+Enter in older Excel.

5. 📈 Max Value by Condition

=MAX(IF(B2:B10="East", C2:C10))

• Gets the maximum value from column C where column B is “East”.
• Ctrl+Shift+Enter in legacy Excel.

6. 🔁 Array Constant Example

={1,2,3} * 2

• Returns {2, 4, 6} (in Excel 365 or newer).

⚠️ Notes

• Avoid using entire columns in array formulas in older Excel; it can slow performance.
• Dynamic array functions include: FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP, LET, LAMBDA.