Array Formulas in Excel with Examples

Array Formulas in Excel

Array formulas allow you to perform multiple calculations on a set of values (an array) and return either a single result or multiple results. They are incredibly powerful for advanced data analysis.

---

Types of Array Formulas

1. Single-cell Array Formula: Returns a single value from an array of values.
2. Multi-cell Array Formula: Returns multiple values into a range of cells.

---

Key Features

• In Excel 365 and later, array formulas “spill” automatically into neighboring cells.
• In earlier versions, array formulas require Ctrl + Shift + Enter instead of just Enter.

---

Examples of Array Formulas

1. Single-cell Array Formula: Sum of Squares

You want to calculate the sum of squares for a range of numbers.

Data
2
3
4

Formula:

=SUM(A1:A3^2)

Explanation:

• A1:A3^2 squares each value in the range (2², 3², 4² = 4, 9, 16).
• SUM adds the squared values: 4 + 9 + 16 = 29.

---

2. Multi-cell Array Formula: Multiplication Table

You want to create a multiplication table for numbers 1 to 5.

1	2	3	4	5

Steps:

1. Enter the numbers 1 to 5 in Row 1 and Column A.
2. In B2, enter the formula: =A2:A6 * B1:F1
3. Press Enter (Excel 365 and later) or Ctrl + Shift + Enter (earlier versions).
4. The multiplication table will “spill” into adjacent cells.

Result:

	1	2	3	4	5
1	1	2	3	4	5
2	2	4	6	8	10
3	3	6	9	12	15
4	4	8	12	16	20
5	5	10	15	20	25

---

3. Conditional Calculation

You want to calculate the total sales for a specific product.

Product	Sales
A	100
B	200
A	300
C	400

Formula:

=SUM(IF(A1:A4="A", B1:B4))

Explanation:

• The formula checks if A1:A4 equals “A”.
• If true, it sums the corresponding values in B1:B4 (100 + 300 = 400).

Important:

• In Excel 365, press Enter.
• In earlier versions, press Ctrl + Shift + Enter.

---

4. Counting Unique Values

You want to count how many unique products exist in a range.

Product
A
B
A
C

Formula:

=SUM(1/COUNTIF(A1:A4, A1:A4))

Explanation:

• COUNTIF(A1:A4, A1:A4) counts occurrences of each value.
• 1/COUNTIF(...) gives a fraction for each occurrence.
• SUM adds these fractions to count unique values.

Result: 3 (Products A, B, C are unique).

---

5. Transposing Data

You want to switch rows into columns or vice versa.

Before
1
2
3

Formula:

=TRANSPOSE(A1:A3)

Result: | 1 | 2 | 3 |

---

Key Takeaways

• Array formulas simplify complex calculations.
• Use them for tasks like conditional sums, unique value counts, and matrix operations.
• In Excel 365, dynamic arrays spill automatically. In older versions, remember Ctrl + Shift + Enter.

Would you like an example file for these array formulas?