If you use Excel daily, you've surely struggled with endless formulas, cross-references, and lists that become too short as soon as you add a new row. With the arrival of dynamic matrices and modern matrix formulasAll that mess is simplified: a single formula can generate many results, adjust itself to the size of the data, and recalculate without you having to touch anything else.
The Formulas with arrays in Excel and dynamic functions They've completely changed the way we analyze data: now you can filter, sort, create sequences, detect unique values, or generate random numbers in entire blocks of cells, without macros or complicated keyboard shortcuts. Let's take a closer look at what they are, how classic and dynamic arrays differ, what new functions are available to you, and how to really leverage them in your daily work.
What is a formula with arrays in Excel (classic model)?
In its traditional form, a matrix formula is a special formula that works with sets of values Instead of just one cell. Instead of operating on A1 or B2 in isolation, it can process entire ranges and return a single result or multiple results, depending on how you've set it up.
These formulas are characterized by the fact that They operate with internal data matricesArray formulas take multiple input values, perform block calculations (sums, comparisons, conditions, etc.), and return an output that can also be a set of values. Traditionally, Excel displayed these formulas with curly braces { } in the formula bar, indicating that it was an array formula.
To enter a formula of this type in older versions, simply pressing Enter was not enough. It was necessary Confirm the formula with the combination Ctrl + Shift + EnterBy doing so, Excel understood that it should treat the expression as an array and automatically enclosed the formula in curly braces. If you typed the braces yourself, it didn't work; they had to appear automatically when you used that key combination.
Classical matrix formulas have always been very useful for complex calculations in multiple cellsAdvanced mathematical operations, conditional filters without "special" functions, mass comparisons between ranges, and information summaries without the need for auxiliary columns. However, they also have their downsides: they are more difficult to understand and debug, and if overused in large workbooks, they can slow down performance of the sheet.
A typical example of a traditional matrix formula would be {=SUM(A1:A5*B1:B5)}where the ranges A1:A5 and B1:B5 are multiplied element by element, and then all the products are summed. Another classic example would be {=MAX(A1:A5+B1:B5)}, in which the two columns are added together and the maximum value of the result is taken.
What is a formula with dynamic arrays in Excel?
With the most recent versions (Excel for Microsoft 365 and Excel 2021), Microsoft introduced the dynamic matrix formulaswhich are the natural evolution of classic arrays. The big difference is that these formulas allow the result to be a set of cells and, instead of forcing you to select the output range and use Ctrl + Shift + Enter, they overflow automatically towards the adjacent cells.
When we talk about a dynamic matrix, we are referring to any formula that can return a variable size range And Excel is able to automatically populate (or "spread") the spreadsheet without you having to do anything else. These formulas are written in a single cell and, depending on the calculation, fill all the necessary cells with the complete result.
Dynamic arrays rely on new functions designed to work with this overflow behavior: FILTER, SORT, UNIQUE, SEQUENCE, SORT BY and RANDOM ARRAYamong others. All of them can return multiple values ​​at once and are designed precisely to handle matrices naturally, without special key combinations.
A key difference compared to traditional matrix formulas is that You no longer need Ctrl + Shift + EnterYou type the formula normally, press Enter, and Excel takes care of everything: it calculates the output size and spills it into as many rows and columns as needed, visually marking the overflow range when you select the original cell.
Another important advantage is that these formulas they adjust their size automaticallyIf the source data changes (you add or remove rows, modify criteria, etc.), the spilled range grows or shrinks automatically, without you having to drag formulas or redefine ranges.
What does the overflow (spill) of a formula mean?
Overflow is the behavior by which A formula generates multiple results, and Excel places them in neighboring cells.Imagine that in cell F2 you write a formula like =SORT(D2:D11,1,-1) to sort a list of 10 items in descending order: the result doesn't just stay in F2, but It spills from F2 to F11occupying the entire range it needs.
In this context, formulas that can return a changing number of elements are known as dynamic matrix formulasAnd when the result is already expanding correctly across several cells, we usually talk about overflowing matrix formulaThe formula itself only exists in the top left cell of the range, but its effect is seen throughout the entire output block.
When you confirm a formula of this type, Excel calculates the size of the output range and distributes the results across all cells within that area. If you select any cell in the overflow range, the application highlights the entire block with a special border, so you can clearly see that this data depends on a single array formula. The border disappears when you select a cell outside the range.
There is an important detail: You can only edit the first cell of the spilled rangeIf you select another cell within the overflow, you'll see the formula in the formula bar, but it will be grayed out, like ghost text, and you won't be able to edit it from there. To change it, you'll have to go to the top-left cell, edit the formula, and press Enter; Excel will automatically update all the cells in the overflow.
Regarding Excel spreadsheets, you should keep in mind that They do not allow overflowing array formulas within the table itself.Dynamic formulas should be placed in the normal grid, outside of structured tables, since these are designed to manage data by rows and columns, not for a formula to expand freely.
#SPILL error! and common problems
A key limitation of dynamic matrices is that They need free space to upload resultsIf any value, formula, or formatting interferes with any of the cells where Excel should write the spilled range, the formula will return a #SPILL! error. This error indicates a block in the output range.
Visually, Excel usually shows the area where the formula should expand. marked with a dotted border over the occupied cellsThis is a quick way to see exactly which values ​​are preventing the overflow. To fix it, simply delete or move the data that's blocking it; as soon as the range is free, the formula will overflow correctly.
Another important aspect is that, if you reference a dynamic matrix from another formula, It is advisable to use the overflow range operatorFor example, if your original formula is in A2 and spills down, you should write A2# when referencing it, so that Excel understands that it should take the entire generated range, not just the isolated cell A2.
It is also important to remember that the Dynamic arrays between books have limited supportExcel only handles them correctly if both files are open. If the source workbook is closed, formulas that depend on that dynamic array may return a #REF! error when recalculated, since they don't have access to the full range.
Differences between classical and dynamic matrix formulas
Excel retains traditional array formulas (those from Ctrl + Shift + Enter) reasons for compatibility with old booksHowever, the current philosophy is to use the new dynamic matrices whenever possible. The differences between the two models are quite clear.
On the one hand, classical matrix formulas They are entered by pre-selecting the output range and confirming with Ctrl + Shift + Enter. They do not overflow automatically, do not change the output size on their own, and in many cases, Its result is limited to a single value.Although they may work internally with multiple cells, if the output area is too small, they may truncate results or return errors such as #N/A.
At the other extreme, the dynamic matrix formulas You type the formulas into a single cell, press Enter, and Excel spills the output into as many cells as needed. If the input data changes, the output range expands or contracts automatically without you having to edit the size. Furthermore, any new formula that returns multiple results will automatically expand or contract. it overflows automatically, without extra steps.
A historical problem with CSE formulas was the so-called "CSE split"When multiple cells in the same array were interdependent, Excel would attempt to calculate them individually, which could sometimes lead to inconsistent results. Dynamic arrays do not behave this way; when a circular reference or a similar issue exists, Excel flags it as such so you can correct it, instead of trying to "save" the calculation.
Furthermore, the new dynamic matrix formulas they are modified more easilyYou only need to touch the source cell. In contrast, with classic CSE formulas, if you wanted to change something in the array range, you had to select the entire block and edit the whole set. Inserting or deleting rows and columns that cross an active CSE formula range is also prohibited; you must first remove that formula before you can modify the structure.
Implicit intersection and @ operator in the new Excel
Before dynamic arrays existed, when a formula could return multiple cells but was entered into a single cell, Excel silently applied a "implicit intersection"In other words, it reduced the output array to a single value, usually the one that matched the row or column of the cell where the formula was located, without the user being very aware of it.
With the arrival of dynamic arrays, Excel makes this behavior more explicit through the operator @When a function that previously returned an array is opened in modern Excel, it may automatically appear preceded by @ to indicate that only the implicit intersection is being taken, not the entire array. This helps in understanding where an overflow might occur if that operator were removed.
Similarly, if you create a formula with dynamic arrays in the current version and then open it in an older version of Excel, it may be displayed as inherited matrix formula If the @ operator is not used. The idea is that the books remain compatible across versions, but that the new experience makes it clear which formulas actually work with full ranges and which have been "reduced" to a specific intersection.
Key functions of dynamic arrays: FILTER, RANDARRAY, SEQUENCE, SORT, and SORTBY
The real leap in quality comes from the new features that work natively with dynamic arrays. Each one solves problems that previously required very complex formulas or macrosand now they are solved with a fairly readable instruction.
FILTER: extract only what you need
The FILTER function allows obtain a subset of data that meets certain criteria Without needing to apply manual filters, use pivot tables, or program anything. You can tell it to show only customers with a certain score, sales from a specific month, or employees above a certain age, and the result spills over into a range that updates automatically when the original data changes.
A typical way to use it would be something like =FILTRAR(A2:C15,(A2:A15=F4)*(C2:C15=G4),»»)where conditions are combined across multiple columns. The result is a dynamic array of rows that meet the specified criteria. Furthermore, you can combine it with other functions, such as SORTBY, to filtering and sorting happen within a single formula.
RANDARRAY / MATRIZALEAT: bulk random numbers
With RANDARRAY (MATRIZALEAT in Spanish), you can generate a matrix of random numbers all at once Create as many rows and columns as you need. No more copying formulas with RANDL() or RANDBETWEEN() across half a sheet: a single instruction creates the entire block.
This function allows you to choose between whole numbers and decimals, define minimum and maximum limits, and specify exact dimensions (number of rows and columns). It's a very useful tool for simulations, financial models, sampling exercises or even to generate temporary identifiers and test data in a matter of seconds.
SEQUENCE: Automatic lists and calendars
The SEQUENCE function generates series of numbers (or dates, if you combine them with other functions) that expand and contract dynamically. It's ideal for numbering records, building calendars, planning work weeks, or creating sequential lists without manually dragging the padding.
With SEQUENCE you can specify how many rows and columns you want, the starting value, and the increment. For example, you can create a series of dates from Monday to Friday by integrating SEQUENCE with date functions, so that The list is generated all at once. and adapts if you add or remove rows associated with your data.
SORT and SORTBY: data always in order
The SORT function allows you reorder a range by one or more columnsin ascending or descending order, without modifying the original table. The result is displayed in a separate range that will update as soon as the source data changes, so you can always have a mirrored, sorted list.
For its part, SORTBY goes a step further: it allows to sort a set of data according to the values ​​in another column or rangeFor example, you could sort a list of products by sales recorded in another table, or a list of students by the grade obtained in a different field, all of this displayed in a dynamic matrix that is kept up to date.
By combining SORT or SORT BY with FILTER and SEQUENCE, you can achieve advanced-level effects: tables that self-sort and self-filter based on changing criteria, without the need for macros or manual updates.
Practical examples of advanced matrix formulas (classical model)
Although dynamic matrices are the current trend, many classic techniques remain useful, and are best understood by looking at them. concrete examples of advanced matrix formulas that have been used for years.
A typical case is summing a range that contains errors. The SUM function, by itself, fails if the range includes a value like #N/A. To avoid this, you can use a formula like this: =SUM(IF(ISERROR(Data),"",Data))SUM internally creates an array in which errors are replaced with empty strings, leaving valid values ​​intact. SUM then works on this filtered array and obtains the total without encountering errors.
Another practical example is count how many errors there are in a rangeA classic formula would be =SUM(IF(ISERROR(Data),1,0)), which generates an array of 1s and 0s (one for each cell with an error, zero for each correct cell) and then sums those values. It can be simplified by omitting the third argument of IF: =SUM(IF(ISERROR(Data),1)), since when the condition is false, IF returns FALSE, which can then be evaluated as 0. There is even a more compact version: =SUM(IF(ISERROR(Data)*1)), taking advantage of the fact that TRUE*1 is 1 and FALSE*1 is 0.
Matrix formulas are also very powerful for sum values ​​according to conditionsFor example, =SUM(IF(Sales>0,Sales)) sums only the positive values ​​in the Sales range, automatically ignoring those that do not meet the condition. To combine multiple "AND" conditions, you can use something like =SUM((Sales>0)*(Sales<=5)*(Sales)), which sums only the values ​​greater than 0 and less than or equal to 5, provided the cell contains numeric data.
If what is needed is an "OR" type logic, one can use expressions like =SUM(IF((Sales<5)+(Sales>15);Sales))These functions sum the values ​​less than 5 and greater than 15 in a single calculation. Although Excel has AND and OR functions, these combinations of multiplication and summation of conditions are often more useful in array formulas because they generate arrays of results instead of a single TRUE or FALSE.
Another widespread use is calculate an average excluding zerosWith =AVERAGE(IF(Sales<>0;Sales)) an array is created with only the non-zero values ​​and is passed to AVERAGE, which performs the calculation without cells with zero distorting the result.
Array formulas also allow you to compare entire ranges. For example, count how many differences there are between two areas of the same sizeMyData and YourData, with =SUM(IF(MyData=YourData,0,1)). This formula generates an array of 0s and 1s (zero when they match, one when there is a difference) and returns the sum of those differences. It can be simplified by writing =SUM(1*(MyData<>YourData)), which again relies on the equivalence TRUE*1=1 and FALSE*1=0.
It is also possible to locate the position of the maximum value in a rangeA typical formula would be =MIN(IF(Data=MAX(Data),ROW(Data),"")), which constructs an array with the row numbers where the maximum value is found and empty strings in the rest. MIN returns the smallest row, that is, the first occurrence of the maximum. If you also want the cell address, you can use... =ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data)), which combines that row with the column in the Data range.
Overflow range: how it looks on the sheet
When you introduce a function that supports dynamic arrays, such as =UPPER(E7:E19), you will see that the result is automatically spills over the corresponding rangeThe formula is only actually in the first cell where you wrote it, but all cells in the output range show the transformed result (in this case, the text in uppercase).
If you select any cell in the overflow range, you will see that The formula appears dimmed in the formula bar.This indicates that the cell does not contain a formula, but rather a result calculated by the original dynamic array. To change anything, you will always need to edit the source cell; the rest of the range will update automatically when you confirm the change.
Best practices and cross-version compatibility
In modern books, it is advisable to prioritize, whenever possible, the dynamic matrix formulasLegacy CSE formulas still work to avoid breaking old files, but creating them from scratch is no longer recommended, precisely because they are more difficult to maintain and have more restrictions when editing the sheet structure.
If you want to convert an old CSE formula into a dynamic array, you can locate the first cell of the array range, Copy the formula text, delete the entire block Then, paste the formula into the top-left cell and confirm it with a normal Enter press. Before doing this in critical workbooks, it's advisable to review any potential calculation differences and ensure you're not relying on legacy behaviors like silent implicit intersection.
When exchanging files between versions, keep in mind that formulas created in Excel with dynamic arrays may appear as inherited matrix formulas If opened in an earlier version, unless the @ operator was used to limit the output. Similarly, many older functions that returned arrays now use the @ operator in newer versions, making it clear that only an intersection is being taken, and not the entire spilled range.
Finally, if you work with external automation, third-party libraries like Aspose.Cells allow configure and recalculate dynamic array formulas by codeThis involves using specific methods to set the array formula in a cell and refresh its results before saving the workbook. This is relevant when you automatically generate reports and want to take advantage of overflow behavior without using Excel interactively.
Mastering formulas with arrays and, above all, Excel's dynamic array functions transforms the spreadsheet into a much smarter analysis systemThe formulas adapt to the size of your data, the results are sorted and filtered automatically, errors are handled elegantly, and your reports cease to be static, responding automatically to every change you make to the source. Share this tutorial on formulas with arrays in Excel so more users will know how to do it.