SPILL

 The SPILL feature in Excel is a powerful functionality that automatically expands the results of a dynamic array formula into multiple cells. This happens when a formula returns multiple values, and Excel "spills" those values into adjacent cells. Below are 10 examples of how the SPILL feature works, including data and formulas:

---

1. Basic SPILL with a Simple Formula

Data:
A1:A5 = {1, 2, 3, 4, 5}

Formula:
=A1:A5 * 2

Result:
The formula spills into B1:B5 with the results: {2, 4, 6, 8, 10}.

---

2. SPILL with SEQUENCE Function

Formula:
=SEQUENCE(5)

Result:
The formula spills into A1:A5 with the results: {1, 2, 3, 4, 5}.

---

3. SPILL with FILTER Function

Data:
A1:A5 = {10, 20, 30, 40, 50}
B1:B5 = {"Apple", "Banana", "Apple", "Orange", "Apple"}

Formula:
=FILTER(A1:A5, B1:B5="Apple")

Result:
The formula spills into C1:C3 with the results: {10, 30, 50}.

---

4. SPILL with SORT Function

Data:
A1:A5 = {50, 20, 40, 10, 30}

Formula:
=SORT(A1:A5)

Result:
The formula spills into B1:B5 with the results: {10, 20, 30, 40, 50}.

---

5. SPILL with UNIQUE Function

Data:
A1:A5 = {"Apple", "Banana", "Apple", "Orange", "Banana"}

Formula:
=UNIQUE(A1:A5)

Result:
The formula spills into B1:B3 with the results: {"Apple", "Banana", "Orange"}.

---

6. SPILL with TRANSPOSE Function

Data:
A1:C1 = {1, 2, 3}

Formula:
=TRANSPOSE(A1:C1)

Result:
The formula spills into A2:A4 with the results: {1; 2; 3}.

---

7. SPILL with RANDARRAY Function

Formula:
=RANDARRAY(3, 2)

Result:
The formula spills into a 3x2 range with random decimal numbers between 0 and 1.

---

8. SPILL with TEXTSPLIT Function

Data:
A1 = "Apple,Banana,Orange"

Formula:
=TEXTSPLIT(A1, ",")

Result:
The formula spills into B1:D1 with the results: {"Apple", "Banana", "Orange"}.

---

9. SPILL with SUM and LET Functions

Data:
A1:A5 = {1, 2, 3, 4, 5}

Formula:
=LET(x, A1:A5, x + SUM(x))

Result:
The formula spills into B1:B5 with the results: {16, 17, 18, 19, 20} (each value in A1:A5 is added to the sum of the range).

---

10. SPILL with Dynamic Array Formula Combining Functions

Data:
A1:A5 = {10, 20, 30, 40, 50}

Formula:
=SORT(FILTER(A1:A5, A1:A5>20), , -1)

Result:
The formula spills into B1:B3 with the results: {50, 40, 30} (filtered and sorted in descending order).

---

Key Notes About SPILL:

1. If a SPILL range is blocked by existing data, Excel will show a #SPILL! error.

2. SPILL works with dynamic array functions like FILTER, SORT, UNIQUE, SEQUENCE, RANDARRAY, etc.

3. You can reference the entire spilled range using the # symbol (e.g., B1# refers to the entire spilled range starting at B1).