Summary  
This chapter covers dynamic arrays and spill behavior, explaining how Excel determines spill ranges from exact references and how to use the “#” operator to reference an entire spilled array in subsequent formulas.

General domain of usage  
Spreadsheet calculations

To begin working with dynamic arrays in Excel, you need to understand the structure of your data and how spill ranges operate. Imagine you have a range  in a sheet called **Sales Data** with columns for `Order ID`, `Month`, `Product`, `Category`, `Quantity`, and `Unit Price`.</br>

Instead of calculating revenue for each row with individual formulas, dynamic arrays allow you to enter a single formula that automatically populates results for the entire column. 

A **spill range** in Excel is a dynamic, automatically expanding area that displays multiple results from a single formula. Spill ranges make it easy to perform calculations across entire datasets without repetitive formula entry.


Definition

Assume your **Sales Data** range occupies cells **A2:F25**, with headers in row 1, and that you are working with a **regular range**, not an Excel Table.

To calculate revenue for each sale using a dynamic array formula, enter the following formula in cell **G2**:

```
=E2:E25 * F2:F25
```

When you press **Enter**, Excel calculates the product for each corresponding pair of values in the **Quantity** (column E) and **Unit Price** (column F) ranges and spills the results downward starting from cell .

Excel determines the spill range strictly based on the exact ranges referenced in the formula (`E2:E25` and `F2:F25`). This spill range is **not truly dynamic** when working with regular ranges. If you add a new row below row 25, the formula will **not** automatically include it. To recalculate revenue for new rows, you must manually adjust the formula ranges (for example, extend them to `B3:B26 * C3:C26`) or rewrite the formula.

This behavior differs from **Excel Tables** or advanced dynamic setups.

Assume that the current Revenue value includes tax, and the tax rate is **10%**.

Create a new column called `Net Revenue` (revenue excluding tax) that calculates -10% from `Revenue` column using one spill formula.

The result should be a full column where each value represents `Revenue × (1 + tax rate)`.

Multiply the entire Revenue range by a single tax factor `1,1` so the output spills down automatically.
```
=G2#*1,1
```

For **Order ID 1017**, what is the revenue excluding 10% tax?

Master Excel's dynamic array features through practical, column-based tasks. Learn to leverage spill behavior, core dynamic array functions, and advanced multi-step logic for efficient, modern spreadsheet solutions.

Explore the basics of dynamic arrays, spill behavior, and how these features change formula logic in Excel. All examples use the Sales_Data table.

Apply FILTER, SORT, SORTBY, and UNIQUE to the Transactions table for powerful, flexible data analysis in a single column.

Solve real-world problems by combining multiple dynamic array functions in a single formula using the Employee_Projects table.

Spill Behavior and the # Operator