Modern Excel finally made sense to me after I learned how arrays and ranges are different

Almost everyone who's entered data into an Excel spreadsheet has a rough idea of what a range is. It's one of those concepts you don’t need to study, since you pick it up naturally as you use the software. Arrays, on the other hand, tend to be a little harder to wrap your head around.

While these two can often look similar on a worksheet, Excel stores and handles them differently. Understanding that distinction can help you avoid common Excel errors such as #VALUE! and #SPILL! and make it easier to perform multiple calculations more efficiently.

Arrays vs. ranges

They may contain the same data, but they're not the same thing

A range is a collection of one or more values physically located in cells on a worksheet grid. Because ranges exist on the sheet itself, you reference them using cell addresses, such as C3 or C1.

Ranges in Excel can contain values you've entered manually, but they can also take the form of a special type of range known as a spilled range. When a dynamic array formula returns multiple values, Excel automatically spills those results onto the worksheet, creating a spilled range. Unlike a standard range, where you can edit each cell independently, a spilled range can only be edited from its top-left cell. You can, however, reference the entire spilled range by adding the # symbol to the top-left cell's address. For example, if a spilled range starts in N2, you can reference all of its values using N2#.

An array, meanwhile, is a list or table of data that Excel stores in memory rather than as values tied to specific cell addresses. During calculation, Excel keeps arrays in RAM, which allows it to pass data between functions without writing intermediate results back to the worksheet. Take this formula for example:

=COUNT(UNIQUE(A1))

The UNIQUE function first produces an in-memory array, and COUNT then evaluates that array. The worksheet only displays the final result, while the intermediate list of unique values remains hidden in Excel’s memory.

An array only becomes visible on the worksheet when it spills into cells. At that point, the output takes the form of a range. For example, entering =SEQUENCE(10) in a cell creates an array of numbers from 1 to 10 in memory, which then spills into the cells below. Once spilled, those values become a spilled range. Before spilling, or even when the array is simply passed between functions, it remains an array in memory. In practice, while Excel processes data through functions such as LET, UNIQUE, and other dynamic array formulas, it continues to treat that data as an array rather than a worksheet range.

You can also create array constants directly within Excel formulas by enclosing values in curly braces. This allows you to supply multiple values as a single array without referencing cells. For example, you can define numeric arrays such as {1, 2, 3, 4, 5} or {6, 2, 10, 9, 3}, and text arrays such as {"a", "a"}, provided the function supports array inputs.

For example, while some conditional functions are rigid about requiring cell ranges, SUMPRODUCT accepts both worksheet ranges and hard-coded arrays. In the formula below, Excel evaluates the values in A1:A5 against the criterion

=SUMPRODUCT((A1:A5

You can also observe how Excel converts ranges into arrays during calculations. If you select a range reference such as A1 in the formula bar and press F9, Excel evaluates the reference and replaces it with a hard-coded array enclosed in curly braces. This makes it possible to see the exact values Excel is processing in memory at that moment.

Why the difference matters in your formulas

Knowing where Excel stores data helps you choose the right approach

Certain Excel functions, including SUMIF, COUNTIF, and RANK, are programmed to work with physical ranges. If you pass one of these functions a memory-based array instead of a range reference, Excel will return a #VALUE! error.

For example, SUMIF can't process the array created by SEQUENCE in the following formula:

=SUMIF(SEQUENCE(10), ">5")

However, if you enter =SEQUENCE(10) in cell A1, Excel spills the results into A1. You can then use this formula:

=SUMIF(A1#, ">5")

This version works because A1# refers to a physical range on the worksheet rather than an in-memory array.

Modern functions such as FILTER, UNIQUE, SORT, and LET work differently. They can pass arrays directly between functions without storing intermediate results on the worksheet. This makes complex formulas more efficient because Excel can perform the calculations entirely in memory until it's ready to return a final result.

Consider this formula:

=LET(HighSales, FILTER(B2:B100, I2:I100 > 9000), UniqueCountries, UNIQUE(HighSales), SORT(UniqueCountries))

The LET function creates variables that exist only within the formula. First, FILTER examines column I and returns the countries in column B associated with sales greater than 9,000. Excel stores that list as an array in memory and assigns it to HighSales. Next, UNIQUE removes duplicate entries from that array and stores the result as UniqueCountries. Finally, SORT arranges the list alphabetically and spills the finished result onto the worksheet.

If I did this the old-school way, I'd need multiple helper columns to store each intermediate step. With dynamic arrays, Excel creates those temporary arrays in memory, passes them from FILTER to UNIQUE to SORT, and writes only the final result to the worksheet. The formula stays self-contained, and the workbook remains cleaner and easier to manage.

The distinction between arrays and ranges also matters because some Excel features still don't support dynamic arrays. Data Validation in Excel, for example, requires a physical range. You can't place an array formula directly in the validation source box. Instead, you must enter the formula in a worksheet cell, let it spill, and then reference the spilled range using the # operator. If your formula begins in P1, for example, you'd use =P1# as the validation source.

Excel tables have a similar limitation. Because tables are designed around independent rows and columns, spilled array formulas aren't supported within the table structure. Any dynamic array formula must be placed outside the table.

Finally, dynamic arrays need room to expand. If a formula is going to spill across twenty cells, those cells must be empty. If existing data blocks the spill range, Excel returns a #SPILL! error. Traditional ranges don't have this requirement because they occupy only the cells you've explicitly defined. As a result, you only need to worry about creating space when a formula is designed to spill.

Modern Excel works better when you know where your data lives

A range has a home on your worksheet grid, while an array lives in Excel's memory until you give it somewhere to land. Understanding that distinction makes modern Excel much easier to work with. You can build more resilient, automated spreadsheets and nest functions like LET, FILTER, and UNIQUE without cluttering your worksheet with intermediate calculations.

At the same time, it's important to remember that not every Excel feature treats arrays and ranges the same way. Some functions and tools still expect a physical range, while others are designed to work with in-memory arrays. Once you understand when Excel needs one versus the other, you'll be able to avoid common errors and take full advantage of what modern Excel has to offer.