Formulas with arrays in Excel: a complete guide and practical examples

The formulas with arrays in Excel They are one of those tools that seem like "black magic" the first time you see them, but when you master them, they allow you to do in a single formula what previously required dozens or hundreds of help cells.

Although they may initially command a little respect, the matrices and matrix formulas They are one of Excel's most powerful resources for data analysis, modeling, finance, engineering, or simply for making your spreadsheets much cleaner and faster.

What is an array and what is an array formula in Excel?

In Excel, a matrix is ​​simply a collection of values that are treated as a set: they can be in a single row, in a single column, or form a block of several rows and columns.

For example, a very typical matrix might contain the months of the year written one below the other or one next to the other, and Excel would work with that group as if it were a single data object.

An matrix formula It is a formula that, instead of operating on a single value, works simultaneously with a complete array of elements: it can perform several calculations at once and return a single result or an array of results.

The key idea is that an array formula makes Excel process many items in bulkinternally evaluating all values ​​and, if necessary, also returning several results at once in different cells.

For example, imagine you have the number of units sold in column B and the price of each unit in column C. With an array formula like =SUM(B2:B11*C2:C11)Excel multiplies each row (units by price) and then sums all those products, without the need for intermediate columns.

How to enter and recognize a classic matrix formula

Traditional array formulas in Excel are entered using a special key combination: Ctrl + Shift + Enter (Ctrl + Shift + Enter). Simply pressing Enter is not enough.

When you write an array formula and confirm with that combination, Excel displays the formula in the formula bar surrounded by keys { }These keys are not typed by hand: Excel adds them automatically when it detects that it is an array formula.

If you try to type the curly braces yourself, Excel will not treat them as a array formulabut as a normal formula, so it is mandatory to use the key combination for it to work.

Each time you edit an array formula, the curly braces temporarily disappear: you'll have to press [the appropriate key] again. Ctrl + Shift + Enter Upon completion of the editing, the formula will cease to be matrix-based and will only be calculated on the first element of the range.

And one important detail: if you forget to use the combination and just press Enter, the formula will behave as a standard formulaTaking only the first value from each range can lead to erroneous results without you realizing it.

Types of matrix formulas: one result or multiple results

We can distinguish two main types of formulas with arrays in Excel: those that return a single value and those that return a set of results spread across several cells.

In the first case, the formula takes an array of data, performs the calculations, and returns a only one result in one cell (for example, a sum, an average, a count, a minimum or a maximum).

In the second type, the formula itself generates a output matrix that occupies two or more cells. In this case, all the results are part of a single array formula that "lives" in several cells at the same time.

Features like SUM, AVERAGE, MAX o MIN (and its variants, MINIFS and MAXIFS) can work with arrays if they are entered as array formulas in a single cell, while others, such as TRANSPOSE, TREND o FREQUENCYThey are designed to return arrays of several cells.

The powerful thing about all this is that a single formula can replace many auxiliary columns, keeping your cleaner, lighter blade and with less risk of copy or update errors.

Advanced examples of classic matrix formulas

Matrix formulas allow us to solve situations that would be cumbersome or simply impossible with standard formulas. Several examples are listed below. examples typical and advanced range-based ones with names like Data, Sales, MyData or YourData.

Sum ranges that contain errors

When a range includes errors such as #ATA normal sum using SUM will fail. With an array formula, you can ignore these errors and sum only the valid values:

=SUM(IF(ISERROR(Data),"",Data))

The function ISSUERROR Detects cells with errors within the Data range and the function SI Create a new array where, instead of errors, you place empty strings. «», and where cells without error retain their original value.

On that clean matrix, the function SUM It calculates the total ignoring empty elements, so you can get the sum even if the original range has defective cells.

Count how many errors there are in a range

If what you need is counting the mistakes Instead of adding them together, you can use a similar variation:

=SUM(IF(ISERROR(Data),1,0))

This formula builds a matrix in which each cell with an error is transformed into a 1 and each cell without error in a 0so that the sum of all those 1s and 0s gives you the total number of errors.

The formula can simplify Removing the third argument of IF, since when the condition is false it returns FALSE, and SUM interprets FALSE as 0:

=SUM(IF(ISERROR(Data),1))

And it is still possible to shorten it further by directly multiplying the Boolean result by 1, taking advantage of the fact that TRUE*1=1 y FALSE*1=0:

=SUM(IF(ISERROR(Data)*1))

Adding values ​​that meet conditions (AND and OR “by hand”)

With array formulas, you can sum only the values ​​that meet certain conditions without always having to use SUMIFS. For example, to sum only the values positives of the Sales range:

=SUM(IF(Sales>0,Sales))

Here's the function SI It generates an array where cells with a value greater than 0 retain their value and the others become FALSE; the function SUM Ignore the FALSE ones and add only the positive numbers.

You can also combine several conditions using multiplication (equivalent to a And logical) or sums (equivalent to a Or logical). For example, to add values ​​greater than 0 and less than or equal to 5:

=SUM((Sales>0)*(Sales<=5)*(Sales))

In this case, logical expressions return TRUE/FALSE arrays, which when multiplied become 1 or 0 and act as filters on Sales values.

If what you need is type O behavior, you can use the sum of logical conditions within SI, as in this formula that adds values ​​less than 5 or greater than 15:

=SUM(IF((Sales<5)+(Sales>15);Sales))

The functions Y y O They return a single TRUE or FALSE, so they are not used directly with multiple arrays; the solution is to emulate them with multiplications and additions as in the previous examples.

Calculate a mean excluding zeros

If you want to get a average without taking zeros into accountYou can combine AVERAGE with an array condition on the Sales range:

=AVERAGE(IF(Sales<>0;Sales))

The resulting SI matrix contains only the non-0 values, which are the ones that are ultimately used AVERAGE to calculate the average.

Counting differences between two ranges

Suppose you have two ranges of the same size and shape, called MyData and YourData, and you want to know in how many cells do they differA matrix formula solves it like this:

=SUM(IF(MyData=YourData,0,1))

The IF function generates an array where each match becomes a 0 and each mismatch a 1. Summing the array gives you the count from different cells.

There is also a more compact version here that directly uses unequal comparison (<>) multiplied by 1:

=SUM(1*(MyData<>YourData))

Once again, the use is made of trick that TRUE equals 1 and FALSE equals 0 when multiplied by a number.

Locate the maximum and its position within a range.

With matrix formulas you can not only find out what the maximum value of a set, but also its exact position on the sheet; you can also use the RANK function to order and locate positions.

To find the row number To find the maximum value within a range of a column named Data, you can use:

=MIN(IF(Data=MAX(Data),ROW(Data),»»))

This formula creates an array where the cells containing the maximum value store their row number, and the rest are left as empty strings. The function MIN Find the smallest number in that matrix, which coincides with the first row where the maximum appears.

If what you want is to get the cell reference For the maximum, you can wrap the previous calculation with ADDRESS and COLUMN:

=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))

Create multi-cell array formulas step by step

In a sample book with a sellers tableVehicle types, units sold, and prices can illustrate how a matrix formula that occupies several cells at once works.

Imagine you have copied a table starting from cell A1 with these fields: Seller, Vehicle Type, Number Sold, Unit Price, and Total Sales, and that in column E you want to calculate the sales for each line using an array formula.

In the range C2:C11 you have the quantities sold and in D2:D11 the prices; the goal is to fill E2:E11 with the product of each row without writing formulas one by one.

To do this as an array formula across multiple cells, first select the entire range E2: E11, type in the formula bar:

=C2:C11*D2:D11

and confirm with Ctrl + Shift + EnterExcel will fill all cells from E2 to E11 at once with the result corresponding to each row.

Single cell array formula on the same example

Starting from that same table, it is possible to obtain the total sales using a single array formula in a single cell, for example B13.

Instead of writing formulas row by row, simply enter the following in B13:

=SUM(C2:C11*D2:D11)

and confirm with Ctrl + Shift + EnterExcel internally performs the multiplication of each pair of cells Cx*Dx, and then adds all those products to give the total.

How to debug and understand a complex matrix formula

When a formula is long and a bit "cryptic", it is essential to be able to see what each part is calculatingExcel allows you to evaluate fragments of a formula using the F9 key.

The trick is to select a specific portion within the formula bar (for example, only B2:B11*C2:C11), and then press F9 so that Excel temporarily replaces that fragment with the intermediate result.

By doing this in an array formula, you will see the resulting matrix, with all its elements, which greatly helps to understand the behavior of the formula and to locate possible errors.

Once you have inspected the chosen part, you can press I To exit without saving changes, or Ctrl + Z if you have accidentally confirmed and want to undo the evaluation.

Constant arrays in Excel: how to create and use them

In addition to using cell ranges, Excel allows you to work with matrix constants, which are sets of fixed values ​​written directly within a formula and that do not change when the formula is copied or moved.

A matrix constant can contain numbers, texts, logical values ​​(TRUE/FALSE) or errorsbut it cannot include cell references, defined names, dates, functions, or other arrays.

There are horizontal one-dimensional constants (a single row), vertical one-dimensional constants (a single column and two-dimensional (a block of rows and columns), and are distinguished by the separators used between the elements.

In Spanish regional settings, vertical arrays are usually separated by semicolon (;), whereas in horizontal arrays another separator can be used (in some configurations, the backslash) or the comma, depending on the system configuration.

For example, a vertical matrix with the months of the year it could be expressed as:

={"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"}

Assigning a name to an array constant

To make using a large constant more manageable, you can give it a name With Excel's Name Manager, you can reuse it without having to type it again.

The process is simple: you go to the Formulas tab, use the option to Defined name or Assign nameYou write the desired name and, in the "Refers to" box, you directly enter the matrix constant.

For example, you could create a name called Months that point to the constant:

={"January"\"February"\"March"\"April"\"May"\"June"\"July"\"August"\"September"\"October"\"November"\"December"}

Then, simply select as many cells as there are elements in the matrix, and type the name =Months and confirm as an array formula so that all the values ​​distributed across the sheet appear.

If the constant is causing problems, it's a good idea to check. the separators used and that a range appropriate to the size of the matrix has been selected before entering the formula with Ctrl + Shift + Enter.

Examples of using matrix constants

Constants allow you to build powerful formulas in very little space. For example, to add the three highest values ​​in a range You can use a combination with LARGE and a constant that defines the desired orders.

Similarly, it is possible to sum the N smallest values ​​with K.ESIMO.MINOR, simply changing the function but keeping the array of positions you want to sum.

Another typical case is counting how many times an evaluator (for example, Pedro) has given a score with several specific values without having to repeat criteria in COUNTIFS over and over again.

In a range of assessments You could use a formula like this:

=SUMA(CONTAR.SI.CONJUNTO(A2:A28;»Pedro»;C2:C28;{3\4\5}))

Here, the constant {3\4\5} It collects the accepted scores (3, 4 and 5) and makes the formula more compact and easier to maintain, although you could expand it with more values ​​if necessary, always respecting the maximum character limit of a formula.

Dynamic array formulas in Excel 365 and 2021

With modern versions of Excel (Microsoft 365 and Excel 2021) a very important change was introduced: the dynamic matrix formulaswhich eliminate the need to use Ctrl + Shift + Enter in most cases.

These new formulas work natively with ranges and matrices and they have the ability to automatically “overflow” into adjacent cells, occupying as many rows and columns as needed to display all the results.

The main difference is that you simply type the formula into a cell and press Enter normally; Excel fills the output range it needs and marks it with a special border indicating that it's a overflow range.

In addition, new functions have emerged that are specifically designed to work with dynamic arrays, such as FILTER, ORDER, LINEUP, SEQUENCE, SORT BY o RANDOM MATRIX, Among others.

These functions allow you to filter, sort, generate sequential lists or random numbers, and return result sets without needing to define the size of the destination range beforehand.

Key differences between classical and dynamic matrix formulas

Classical matrix formulas require Ctrl + Shift + EnterThey can be somewhat more difficult to read and, in many cases, are limited in their ability to automatically produce results, unlike modern features such as VLOOKUP and XLOOKUP.

Dynamic matrix formulas are written as normal formulasThey are confirmed simply by pressing Enter and spill the results on their own, without selecting previous ranges or using inherited array formulas.

Another important difference is that dynamic functions return matrices explicitly and are expected as such; if an old book used a function that returned an array to several cells, Excel could apply a silent implicit intersection.

With dynamic arrays, Excel marks those old cases with the operator @, which indicates where that implicit intersection was occurring, in order to preserve the previous behavior and avoid unexpected results.

It is also worth noting that dynamic matrix formulas are only available in Excel 365 and Excel 2021In earlier versions they do not work and may appear as legacy array formulas if those workbooks are opened on computers without dynamic array support.

Setting up and using dynamic array formulas

To use a dynamic formula, simply choose the starting cellType the formula with a function like FILTER or SORT and press Enter. Excel will automatically scatter the results down and to the right.

It's important to make sure there is free space around from the starting cell, because if the cells where the array should be dumped already contain data, Excel will display an overflow error (#OVERFLOW or similar).

Once the formula is created, the overflow range takes effect. like a blockIf you modify the formula in the main cell, all results are updated; if you want to delete everything, simply remove the formula from that cell.

You can also refer to the overflow range from other formulas using the overflow operator (for example, =SUM(F2#)), so that if it grows or shrinks in size, the formulas that use it will adapt automatically.

In environments of programminglibraries such as Aspose.Cells They allow you to set and recalculate dynamic array formulas by code, using specific methods to assign them to a cell and refresh them before performing the general formula calculation.

Advanced applications of matrices: linear algebra and finance

Working with arrays in Excel is not limited to summing ranges or filtering values: it can also be used for problems involving linear algebra and optimization, such as matrix inversion, solving systems of equations, or building investment portfolios.

A square matrix A can be inverted in Excel using the function MINVERSA, which requires a matrix (or dynamic, depending on the version) formula over a range of the same size as the original matrix.

To obtain the inverse of a 3×3 matrix located in B3:D5, you would select an empty 3×3 block, write =MINVERSE(B3:D5) and you would confirm it as a matrix formula, thus obtaining matrix A^-1.

If you multiply the original matrix by its inverse using MMULT over an appropriate range you will get a identity matrix, analogous to multiplying a number by its inverse, which always gives 1.

Similarly, you can set up a system of linear equations in matrix form, with A as the coefficient matrix, K as the vector of unknowns, and P as the vector of independent terms, and solve it using the relation K = A^-1 P using MINVERSA and MMULT.

In finance, this matrix approach is applied, for example, to problems of portfolio optimization, where the covariance matrix σ is used_ij between securities, the expected returns μ_j and profitability constraints to find the lowest risk asset mix for a specific profitability target.

Starting from the Lagrange function and the derivation of the first-order conditions, we again arrive at a matrix system of the type A·X = P, whose solution X = A^-1·P gives us the optimal weights of each asset in the portfolio.

In an example with three stocks A, B, and C, with given covariances and expected returns, an investment vector can be determined such that the portfolio obtains a 4% expected return with the minimum varianceresulting in a combination that leverages diversification to reduce risk compared to investing in a single security.

All of this is implemented using the same basic tools: MINVERSA, MMULT and matrix formulas, reinforcing the idea that Excel can be a very competent platform for numerical analysis when the use of matrices is mastered.

After covering everything from the fundamentals of classical matrix formulas, through matrix constants and dynamic matrices, to advanced uses in linear algebra and finance, it becomes quite clear that Learn to handle matrices effectively in Excel It's an investment that allows you to work cleaner, faster, and with solutions that are often beyond the reach of conventional formulas.