Array Formulas in Excel: CSE and Dynamic Arrays Explained

Array formulas are one of the most powerful and most misunderstood features of Excel. For years they looked like magic: curly braces, a strange key combination and errors nobody could explain. With Excel 365, everything changed: working with arrays became intuitive, and many formulas got much shorter.

Below I explain what an array formula is and how it works, how old and new Excel versions differ, show practical examples and the typical errors, so you can use arrays in everyday work.

What is an array formula

An array formula works not with a single value, but with a set of values at once. Such a formula can:

take an array as input, for example a range of several cells;
create an array inside the formula;
return an array as the result.

In older Excel versions (up to Excel 2019), array formulas required a special way of entering: Ctrl+Shift+Enter. That’s why they are also called CSE formulas. Excel wrapped such formulas in curly braces { } to show that it’s an array.

Excel 365 and Excel 2021 introduced dynamic arrays. Now you just press Enter, and if the formula returns an array, Excel “spills” it into the neighboring cells by itself. New functions like SEQUENCE, FILTER, UNIQUE and SORT are built around this.

Array formula examples

Example 1. A classic array formula (old Excel). It counts the values greater than 100 in a range:

{=SUM(IF(A1:A10>100,1,0))}

To make it work, you had to press Ctrl+Shift+Enter. The IF function returns an array of ones and zeros, and SUM adds them up. In Excel 365, the same works with a plain Enter, and it can be written shorter:

=SUM((A1:A10>100)*1)

Example 2. A formula that returns an array of values (new Excel):

=SEQUENCE(5)

The result is the numbers 1 to 5 in a column. The formula occupies not one cell, but five at once: that’s a dynamic array.

Example 3. Extracting the unique values from a column:

=UNIQUE(A2:A100)

Array vs. range: what’s the difference

An array is the internal representation of a set of values that Excel uses in formulas. Its order and length are fixed. Excel treats an array as a whole: you can’t change a part of it, insert a row in the middle or sort it.

A range is just an address of cells on a sheet. You can sort it, extend it, insert rows and columns. An array formula can refer to a range, but it works with it differently: as a sequence of values, not as a set of addresses.

Typical error: you can’t change part of an array

If you try to delete or edit a cell that belongs to the output of an old CSE array formula, Excel shows an error:

You can’t change part of an array.

To fix it, select the whole area filled by the formula and delete it entirely, or change the formula in the top cell. With dynamic arrays, you edit only the first cell: the rest of the spill range is grayed out in the formula bar.

How to tell that a formula is an array formula

it was entered with Ctrl+Shift+Enter and shows curly braces: an array formula in old Excel;
the formula spills down or to the right, and a blue border appears around the result: a dynamic array in new Excel;
you get an error when editing a single cell of the result: also a sign of an array.

Array dimensions

Both arrays and ranges in Excel can be one-dimensional (a vector), two-dimensional (a table) and three-dimensional (several layers, like the same range on several sheets). One-dimensional arrays, in turn, can be vertical or horizontal. Three-dimensional arrays are rarely used in practice.

Types of arrays

By the type of data in them:

numeric arrays (numbers);
text arrays (strings);
Boolean arrays (TRUE/FALSE);
mixed arrays.

By how their elements are defined:

array constants, typed right in the formula;
calculated arrays, the result of operations on ranges.

How to create arrays

There are many ways to create an array in Excel. Here they are with examples. All the expressions below are array formulas, even if they contain no functions.

Array constant typed manually

You can type one- and two-dimensional array constants by hand and output them to the sheet without any other operations. In Excel with US regional settings, elements in a row are separated by commas, and rows are separated by semicolons. A two-dimensional array is typed row by row:

={1,2,3;4,5,6}

This creates a table of 2 rows and 3 columns. In other regional settings, the separators may differ: for example, in many European locales, elements are separated by semicolons or backslashes. That’s the most common reason why a formula copied from a foreign website doesn’t work.

Named array constant

If you often use the same set of values in formulas, save it in the workbook as a named array: Formulas → Define Name, and type the constant in the Refers to box, for example ={0,1,2,3,4,5,6,7,8,9}. An array of digits like this helps to find numbers in text cells. After that, you refer to it by name instead of typing it every time, and Excel suggests the name as you type.

With functions

The methods above are rare in practice. Most often, arrays are created automatically when functions refer to ranges. The only condition is that the function must not be an aggregating one: SUM or AVERAGE, for example, won’t create an array from a range.

There is an article on this site on how to add text to multiple cells at once, with several methods. Here is one more, with an array formula: =A1:A10&” pcs” returns an array of all ten values with the text appended, and in Excel 365 it spills down by itself.

With math operators

Apply any math operation to a range, and you get an array of the same size: =A1:A5*2 doubles every value, =A1:A5+B1:B5 adds two ranges element by element.

With comparison operators

In the same way, arrays are created when you compare a range with a constant or a cell value: =A1:A5>100 returns an array of TRUE and FALSE values. That’s exactly what Example 1 above uses.

With other arrays

In the two previous examples, arrays came from a range interacting with a constant. It also works the other way round: take one cell and combine it with an array constant using operators or functions. The resulting array has the size of the array constant. For example, =A1*{1,2,3} returns a horizontal array of three values, and =A1*{1;2;3} a vertical one. Note the separators again: commas between columns, semicolons between rows.

With combinations of these methods

As you’ve probably guessed, the possibilities are endless: you can combine all the methods above, performing operations on arrays and ranges.

Outputting an array to the sheet

In old Excel, to output an array correctly, you select a range of the same size and shape, type the formula and press Ctrl+Shift+Enter.

If you select too few cells, only part of the array is shown. If you select too many, the extra cells show the #N/A error. In the example, the resulting array is 5×5, but a 6×6 range was selected before entering the formula.

Dynamic arrays in recent Excel versions

With dynamic arrays in Excel 365, Microsoft made a real revolution that changed the way we work with formulas. Before, you had to use the special Ctrl+Shift+Enter entry mode, which confused beginners and caused errors.

Dynamic arrays removed the need to manage the size of the output range manually. You enter a formula in one cell, and Excel spills the results into the neighboring cells, determining the size of the output range by itself.

Goodbye, Ctrl+Shift+Enter

The switch to dynamic arrays simplified the syntax. You no longer need the magic key combination: any formula that returns an array becomes dynamic automatically. This is especially useful with changing data, because the size of the result adjusts to the source data.

The #SPILL! error replaced many old errors related to overlapping ranges, and it’s easier to debug: it means that something is in the way of the result. Clear the cells below or to the right of the formula, and the array spills.

How Excel functions interact with arrays

As we found out above, many functions create arrays when applied to a range. They normally work with a single cell, and most Excel functions are like that.

However, some functions are called aggregating: given a range or an array, they return a single value. The most popular one is SUM, but there are many others:

math: PRODUCT, SUMPRODUCT, SUMIF, SUMIFS;
statistical: AVERAGE, MIN, MAX, COUNT, COUNTA, COUNTIF;
logical: AND and OR.

SUMPRODUCT deserves a special mention: it works with arrays natively, without Ctrl+Shift+Enter, even in old Excel. That’s why so many “array” formulas on the web use it.

Array formulas: practical examples

Now that the basic concepts are covered, let’s move on to practice. A few real-life tasks solved with arrays:

Count the characters in a whole range:

=SUMPRODUCT(LEN(A1:A10))

Sum the 3 largest values:

=SUM(LARGE(A1:A10,{1,2,3}))

Count unique values in a column, the classic way and in Excel 365:

=SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100))

=COUNTA(UNIQUE(A2:A100))

More tasks on this site that are solved with array formulas:

Common mistakes

The formula returns a single value instead of an array, or a wrong result. In Excel 2019 and earlier, it was entered with Enter instead of Ctrl+Shift+Enter.
The formula returns #SPILL!. Something is in the way of the result: non-empty cells, merged cells, or the formula is inside an Excel table, where dynamic arrays can’t spill.
The array constant returns an error. The separators don’t match your regional settings: check commas and semicolons inside the braces.
The workbook got slow. Array formulas over whole columns (A:A) calculate a million rows. Limit the ranges to the actual data.

Summary

I hope you found what you were looking for on this page and now understand how array formulas work. In Excel 365, most of them are just regular formulas that spill, and in older versions remember Ctrl+Shift+Enter. Arrays are a great tool, but every such formula still has to be built, checked and filled in. For typical text tasks, a ready-made procedure is faster.

You may also be interested in other Excel questions and answers and the Excel functions reference.