Array formulas in Excel: from one cell to a complete list

I have six order rows. I want to calculate their values, select one sales channel and create a list of channels to check. I could copy formulas down, apply a filter manually and type out the names. I could also write a few rules that return the results directly. These are the kinds of tasks where I find array formulas useful.

Before shortening a formula or nesting more functions inside it, I want to understand the shape of the data. How many values go into the calculation? How many should come out? Am I expecting one number, a column or an entire table? A little thought here saves me from staring at an impressive formula that answers a completely different question.

I will use one synthetic dataset throughout. None of the numbers describe an actual business. I have deliberately kept the dataset small enough for you to calculate every value and inspect every returned row. A mistake can exist in a thousand records too. It simply has more places to hide.

First, I check the Excel version and formula language

These examples require Excel with dynamic array support. Microsoft’s FILTER documentation lists Microsoft 365, Excel 2021 and Excel 2024. Dividing the world into “365” and “all older versions” is therefore misleading. I also check the recipient’s version and the availability of the particular functions used in the workbook.

I use English function names and commas between arguments. Regional settings can change the separator. Polish versions of the examples use names such as FILTRUJ and UNIKATOWE, usually with semicolons. If Excel rejects a formula immediately, I check its language and separators first. Randomly changing decimal punctuation in the source data can add a second problem without fixing the first.

The central functions here are FILTER, UNIQUE and SORT. I also use SUM, LEN, SUMPRODUCT and ROWS. I avoid manually typed array constants in braces because their row and column separators introduce another localization detail. A visible cell range lets us concentrate on the calculation and its output without starting a separate argument with punctuation.

When I inherit an old workbook, I check its behavior before replacing formulas. Being able to open a file in a newer Excel version does not establish that every calculation should be rewritten. I want a working copy, known control results and a clear idea of who will maintain the report after I hand it over.

What I mean by an array

An array is an ordered arrangement of values. It can be one column, one row or a rectangle containing several rows and columns. C2:C7 supplies six values arranged vertically. A2:D7 supplies six rows and four columns. A formula can process that arrangement without requiring me to enter a separate calculation manually in every output cell.

I also distinguish a function from a formula. A function has a name and arguments, such as SUM. A formula can combine functions, operators and references. Multiplying two ranges is an array calculation even though it contains no named function at all. Understanding the whole expression is more useful than memorizing a list of supposedly special functions.

An intermediate result and a final result can have different shapes. I might calculate six order values and then add them together. The intermediate result contains six numbers, while the final result contains one. A formula that does not spill visibly across the worksheet can still have processed an array along the way.

I think of the data as moving through a sequence of steps. At each step, I ask how many rows I have and what one item represents. That habit becomes useful again in SQL, joins and reporting models. The syntax changes, but mixing levels of detail accidentally remains a reliable way to produce a convincing wrong answer.

Preparing six orders

Enter the following data into A1:D7. The first row contains headers and the six data rows begin in row two. Keep the channel names as shown so that the English and Polish exercises use identical input values. Columns C and D contain numbers. Price is a fictional value per unit, without tax, discounts or currency conversion.

The zero quantity is deliberate. Zero is a specific value, and its presence alone does not establish that a record is wrong. In real data I would investigate its meaning. A cancelled line, a reservation and an entry mistake require different treatment. For this exercise, I simply define that row as having an order value of zero.

Leave space available in columns F:O. You can work through the examples sequentially, replacing an earlier formula in its starting cell. Do not place the next example inside an existing spill range. Excel will not interpret that as a polite request to rearrange the neighboring report for you.

One calculation, six values or one total

In F2, I enter the following formula and press ordinary Enter:

=C2:C7*D2:D7

The result is a vertical list containing 100, 60, 80, 100, 0 and 120. Excel multiplies corresponding positions: C2 by D2, C3 by D3, and so on. This is why the order of the data matters. Sorting only the price column would associate quantities with the wrong prices, even though the formula itself would still look perfectly reasonable.

F2 holds the actual formula. The remaining cells display its spilled result. To change the rule, I edit F2. To correct an individual input record, I change the source data when I have evidence for that correction. I do not try to replace one cell inside the spill with a manually typed answer.

In H10, I can add the products directly:

=SUM(C2:C7*D2:D7)

The answer is 460. Six intermediate values have been reduced to one total. I check it manually: 100 plus 60 plus 80 plus 100 plus zero plus 120. There is also a version using a function that has supported arrays for a long time:

=SUMPRODUCT(C2:C7,D2:D7)

SUMPRODUCT does not require legacy CSE entry. For this dataset, it also returns 460. I do not assume these expressions will behave identically for every kind of damaged input, however. Text, errors and mismatched dimensions deserve separate checks. First I establish which types of values are permitted in each source column.

FILTER returns matching records

In H2, I enter a formula selecting the North channel:

=FILTER(A2:D7,B2:B7="North","No results")

The result occupies three rows and four columns. It includes A101, A103 and A105. The expression B2:B7=“North” produces one logical answer for each source row. FILTER uses that set of answers to choose records from A2:D7. The zero quantity in A105 does not exclude it because the condition refers only to the channel.

The condition’s dimensions must match the relevant dimension of the data. Selecting rows from six records requires six corresponding decisions. B2:B6 leaves out one record. That is a construction error in the formula, rather than a meaningful instruction to discard the last order. Looking at the range boundaries is often the fastest useful check.

The third argument defines what to return when no record matches. Replace North with East and the output becomes “No results”. That message is helpful to a person, but it has a different type and shape from an order table. A downstream calculation that counts records needs to handle this case. A cell containing a message does not represent one matching order.

Microsoft’s FILTER reference covers both empty selections and errors inside the condition. The empty-result argument is not a general mechanism for hiding every problem. If a channel cell contains a spreadsheet error, I want to find and understand it before publishing the output.

Combining conditions deliberately

Now I want North orders with a positive quantity. I replace the formula in H2:

=FILTER(A2:D7,(B2:B7="North")*(C2:C7>0),"No results")

Multiplication combines the conditions within each row. Both must be true, so A101 and A103 remain. A105 is excluded because its quantity is zero. I check the two comparisons separately before combining them. That is easier than hunting for a typo inside a long expression with several levels of parentheses.

For an alternative condition, I can use addition. The next example selects North orders or orders with at least three units:

=FILTER(A2:D7,((B2:B7="North")+(C2:C7>=3))>0,"No results")

I get five records, A101 through A105. A106 meets neither condition. Comparing the sum with zero makes the intention explicit: at least one condition must hold. A record satisfying both conditions would still appear only once, because I am selecting existing rows instead of appending two independently produced lists.

I do not casually replace this expression with OR. A logical function can reduce a collection of answers to a single result, whereas this operation needs a separate decision for every record. Once again, I return to the shape of the calculation. Six source rows need six inclusion decisions, regardless of how short I would like the formula to look.

UNIQUE and SORT create a category list

In M2, I prepare an ordered list of channels:

=SORT(UNIQUE(B2:B7))

The result is North, South and Web. First I remove repeated values from the list of names, then I sort what remains. The sequence is understandable even to someone still learning these functions. UNIQUE returns distinct values by default, and SORT orders the result.

There is an important difference between a distinct name and a name that occurs exactly once. North occurs three times, but it still belongs in the list of distinct channels. If I want only values with a single occurrence, I use the third argument of UNIQUE:

=UNIQUE(B2:B7,FALSE,TRUE)

This returns only Web. The second argument tells Excel to compare rows; the third selects values that occur exactly once. The distinction is useful in data checks, although discovering repeated values does not establish that the data is wrong. Three orders from the same channel are entirely ordinary.

I do not expect UNIQUE to clean category names comprehensively. Extra spaces, alternative labels and incorrectly entered categories need an agreed cleaning rule. Before calling the output a channel dictionary, I compare it with the allowed values and investigate discrepancies. An automatic list can organize incorrect labels with remarkable efficiency too.

Spill references and growing input data

If M2 still contains the formula sorting distinct channels, M2# refers to its entire current spilled result. In O2, I can count the output rows:

=ROWS(M2#)

The result is three. The # operator lets me reference the output without manually writing its final row number. This is useful when the number of categories changes after a data update. The reference still depends on the underlying formula, however, including its assumptions and any error it might return.

A dynamic output does not automatically expand an arbitrary fixed source reference. Adding another order in row eight will not make B2:B7 include B8. In this exercise, you need to update the range. For a maintained report, I would consider an Excel Table with structured references that adjust when rows are added to that Table.

I place the spilling formula outside the Excel Table itself. The inputs can live in a Table, while the output needs space in the normal worksheet grid. Microsoft explains this distinction in its spilling guidance. I also leave room for the result to grow instead of surrounding it tightly with unrelated calculations.

Diagnosing errors and recognizing older formulas

When I see #SPILL!, I inspect the destination. Possible obstructions include a typed value, a formula that looks blank, merged cells or the worksheet boundary. Instead of clearing an entire block immediately, I identify what occupies the space and decide whether the result can be moved safely. A technically effective fix can still destroy someone else’s useful work.

Another set of problems starts in the inputs. A genuinely empty quantity cell can produce zero in a simple multiplication, making the order look successfully calculated. Yet a missing quantity and an intentionally recorded zero mean different things. I check missing values, numeric types and allowed ranges separately. Correct arithmetic cannot establish that the inputs represent the intended business situation.

In older workbooks, I may encounter formulas entered with Ctrl+Shift+Enter, abbreviated CSE. Excel displays braces around those formulas; I do not type the braces manually. That method has different rules for the output area. Microsoft’s comparison with dynamic formulas explains the differences. For the new dynamic-array examples here, ordinary Enter is sufficient.

I also retain a useful idea from the original article: counting characters across a range. This time the input is explicit. All six order identifiers contain four characters:

=SUM(LEN(A2:A7))

The answer is 24. LEN calculates text length and SUM collects the individual results. This can help detect a change in identifier formatting. A total length will not catch every mistake, however. One identifier that is too short and another that is too long can cancel each other out in the total.

An exercise before handing over the report

Before the exercise, I also write a short refresh note. Where did the inputs come from, when were they extracted and which period do they cover? Which columns can be overwritten, and which contain calculations? I keep those details beside the report because a colleague can have a correct formula while analyzing an outdated file. The date someone last opened the workbook does not establish the date of its underlying data.

I distinguish filtering an output from deleting records as well. FILTER builds a new result while leaving the inputs in their original location. That lets me compare the selected group with the whole dataset and revisit excluded rows. If I copy the result into another workbook as values, that automatic dependency disappears. The export becomes a snapshot and needs a clearly stated extraction time.

At handover, I establish whether the recipient needs a live calculation or a fixed snapshot. Both can be useful. Trouble starts when someone expects updates from a static copy. Even an elegant formula cannot repair a misunderstanding about how the report is supposed to be used.

Start by reproducing the control results: total value 460, three North orders, two positive-quantity North orders and three distinct channels. Then remove the formula from F2, type test text into F4 and enter the multiplication in F2 again. Observe the obstruction error. Remove only your test value and check that the expected spill returns.

Next, change the channel in B7 to Web. The overall order value should stay unchanged while the channel distribution changes. Restore the dataset and append a seventh order below the original range. Check which formulas omit it. This is a useful way to distinguish an automatically changing output from a correctly designed source reference.

Finally, ask another person to identify the input cells, the formula’s editable starting cell and the meaning of the empty-result message. If those details are unclear without your commentary, add instructions to the workbook. A readable handover matters particularly when a report is expected to survive longer than one afternoon and its original author is unavailable.

If your next task is retrieving a product description from an identifier, continue with lookup functions in Excel. A familiar question returns there: do I want one value, several matching records or an explicit missing-record message? A well-defined output usually makes choosing a formula easier than memorizing another collection of shortcuts.