Although they can take a little time to master, once you understand them, they can save you vast amounts of time, especially as Office 365 has now made them more straightforward to use.
In this article, you will learn about Excel’s dynamic array formulas and how to use them with the help of simple examples.
What Is An Array?
What precisely is an array? In Excel terms, an array is a collection of values, whether they are in rows, columns, or both.
An array formula allows us to do multiple calculations on one or more values that are stored in our array.
These formulas update automatically when the referenced cells change. And that is why they are called dynamic arrays.
Dynamic arrays perform automatic calculations, and they return values to one cell or multiple cells based on our formula.
To do this, we need to input the formula, usually in just one cell.
Note: This is a guest post by Ben Richardson. Ben is an ex-banker and venture capitalist. He now runs Acuity Training, one of the UK’s leading providers of Excel training courses.
Short History Of Dynamic Arrays In Excel
In previous versions of Excel, dynamic arrays were complex to set up.
You would create an array using the keystroke combination CTRL + SHIFT + ENTER, thus often referred as CSE formulas.
With the introduction of dynamic arrays formulas in September 2018, Microsoft introduced (in Office 365) a more accessible approach to creating them.
When we create such formulas, they are automatically populated or spilled into the corresponding adjacent cells. If Excel does not identify them for some reason, we can still use CTRL + SHIFT + ENTER.
Suppose you write a dynamic formula in Excel. In that case, it can now recognize if this formula can return multiple values or not.
Using Dynamic Arrays In An Example
Suppose that we have salaries for an average company for four years:
We can now tell Excel that the first two numbers (B2:C2) are a part of an array. To do this, we will place the following formula in cell B5:
=B2:C2
Before you press ENTER look at the formula bar.
Suppose you see that your formula is enclosed in curly brackets. In that case, it is a sign that Excel identifies it as an array formula.
Once you hit ENTER, the array will be “spilled” horizontally.
This means that Excel effectively duplicates the formula for each cell in the array.
A quick way of knowing that this is now an array is that we have the cells formatted with blue borders:
You can see that there is nothing scary about the arrays, right?
Now, suppose that we have the number of employees in the company for every year as well:
Our goal now is to find out the average salary for every year. You could go along and input the formula cell B4:
=B2/B3
Then drag this formula to the end of our table.
You would have the following results:
To avoid so many steps in a process, we can use a Dynamic Array.
To do this, we will enter the formula in cell G2:
=B2:E2/B3:E3
Excel will automatically recognize the pattern and will do the calculations for us:
You can see that cells H2:J2 now have a blue outline, meaning that the formula spilled across multiple cells.
Of course, if we change any value in our original table, the resulting numbers in the array will also change.
You may still need to correct the formatting of the results.
Excel automatically uses the General formatting style for numbers.
The example above worked on multiple cells.
We can also use dynamic arrays with single-cell array formulas.
For example, suppose you want to know the difference between $1,000 and the average salary. In that case, we could use the following formula:
=MIN(1000-B4:E4)
We would get the number $37.57 for the result:
Before dynamic array formulas, the usual process would have been to subtract every number in the fourth row by 1,000 and then find the minimum value of these values with the MIN formula, a complicated task.
Dynamic Array Functions
Let’s see some common de
With the development of dynamic arrays and their full integration into Excel with Office 365, Microsoft has recently added dynamic array functions to Excel.
These functions are:
UNIQUE – we can use it to extract unique values from our range.
FILTER – used to filter our data based on any criteria we specify.
SORT – sorts our range by the desired column.
SORTBY – sorts our range by another array or range.
RANDARRAY – used to present an array of random numbers.
SEQUENCE – used to present a list of sequential numbers.
Conclusion
Next time you are laboriously copying a formula across a table in Excel, pause and check whether you get the same results with a dynamic array formula.
Dynamic arrays can take a little getting used to, but you’ll never go back once you do — they are big timesavers and worth the time to master.