Excel Dynamic Array Formulas are a huge upgrade to MS Excel. These Dynamic Array formulas can sort, filter, remove duplicates, ascertain sequences, and a lot more. In general, the normal formulas return the solution in a single cell. Whereas in the case of Dynamic Array formulas, the result tends to occupy a range of cells, i.e. spills, thus giving the dynamic array effect.
Below is a list of recently introduced Dynamic Array Formulas in Excel.
Note: These Dynamic arrays are a new feature available in Ms Excel from the version of Microsoft 365 and above. Excel 2016 and Excel 2019 do not offer dynamic array formula support.
- There is no need to press CTRL+SHIFT+Enter for an array formula anymore. Most of the Array formulas spill into the range of cells. Subject to the version of Ms Excel as mentioned above.
- You can use # operator to refer to a range of cells spilled by Dynamic Array functions. For example, if cell C1 has a Dynamic Array function that spills over until C10, you can refer to this dynamic range by using the reference C1#
Firstly let’s understand what does Spill range means. Below is an example to explain the same using the UNIQUE function.
As simple as it sounds, UNIQUE function returns a list of unique values from a list or range of cells. All you need to do is select the range and hit enter. No more Ctrl + Shift + Enter.
Using the UNIQUE formula we get a list of the unique names from the selected range B7:B14. Four unique names are returned. This is an amazing way to remove duplicates and return UNIQUE values. But notice our output is not a single cell, it has occupied more than one cell. This is where the concept of spill range comes into play.
Look closely at the result, the values are in more than one cell and are enclosed within a rectangular box denoting it is an array. This effect where the result spills into multiple cells is called “spill range”. In the above example, the spill range is D7:D10. Notice that the spill range has special border highlighting.
Moreover, there is no need to press Ctrl +Shift + Enter for an array formula, the results will automatically spill into the cells. When there is a change in source data, the result changes accordingly.
In case there exists data in place of spilled range, a #SPILL error will appear. In simple terms, when the resultant area is not empty or consists of any value a #SPILL error is returned. This indicates that the range where the result needs to spill down is not completely blank. Once there is space for the spill range, the formula will automatically spill. Below is an example showing spill error. Since the range D7 to D10 is not completely blank, i.e. a value in D10, a spill error is returned.
To spot the obstructing cell which does not let your formula spill, click on the first cell of the array and then click on the error box that appears. Then click on the option saying select obstructing cells. This will automatically select the cells which do not allow to spill. You can then move or delete those cells and after this, the formula will automatically spill. Below is an image showing the same.
Let’s look at other dynamic array formulas in excel. Reminder these might be subject to availability based on the version of Excel you are using.
The RANDARRAY function returns an array of random numbers. You can specify the number of rows and columns to fill, minimum and maximum values, and whether to return whole numbers or decimal values.
As mentioned above the RANDARRAY function returns random numbers. In our example the formula is =RANDARRAY(4,3,1,6,TRUE). The first argument is 4 which are the number of rows followed by the number of columns entered as 3. After this is the lowest number, in our case 1 and then the highest number i.e. 6 as per the above example. Lastly is an option to return integers or decimals denoted by True or False.
Just like UNIQUE even RANDARRAY results in a spilled range.
By default, if arguments are not entered it defaults to 0, 1 i.e single cell with 0 being the lowest and 1 being the highest. Thus returning a decimal value.
NOTE: The result of RANDARRAY changes every time something is entered in any cell.
The SEQUENCE function allows you to generate a list of sequential numbers in an array. Eg:1, 2, 3, 4.
In the following example, we have created an array that’s 4 rows tall by 5 columns wide using the formula as =SEQUENCE(4,5)
4,5 are the number of rows and columns respectively. You can also specify the start value and the additional value to be added to ascertain the next number in the sequence. By default 1 is added to get the next number. For example, you can ascertain the odd number starting from 1.
In the below image 4,4 represent the number of rows and columns to be occupied. Followed by a starting value which is 1 in our case. Finally, we have the steps or jumps to made either front or back, which is 2 in this example. Meaning start from number 1 and add 2 to get the next value.
The SORT function sorts the contents of a range or array either in A to Z (ascending) or Z to A (descending). In the following example, we have sales and a few details like Region, the Product sold, and the Units. And we wish to sort the Region in alphabetical order A to Z using =SORT(B7:B14)
Similarly, you can SORT for other heads as well. But what if we wish to sort the data based on Region and retrieve the whole table. We have an option of 3 more arguments, which are sort index, sort order, and sort by column or row. The sort index is the number indicating the column or row to sort by, i.e. 1 meaning 1st column. Sort order 1 for ascending and -1 for descending. Lastly by column or not depicted by TRUE or FALSE respectively.
In the above image, we have entered two more arguments other than the range. The first one stands for the indexing, i.e. first column denoted by 1. Followed by another 1 which sorts the data in ascending order.
Furthermore, if you wish to sort based on one or more criteria you can do so by using SORTBY formula.
The SORTBY function sorts the data based on one or more sorting conditions. Below is an example, explaining the sort based on two conditions.
In the example, we sort our data based on two conditions. Firstly by the units in descending order followed by Products in ascending i.e. Alphabetical (A to Z ) order. The formula arguments take in the entire array we wish to sort. Then the first level of sorting which is units in our case done by entering -1. After this it takes in the order of sorting, descending, or ascending. Similarly, you can add more levels of sorting while using SORTBY.
The FILTER function allows you to filter a range of data based on criteria. In the following example we use the formula = FILTER(B7:D14, C7:C14=G6,””) to return all records for Mango, as selected in cell G6, and if there exists no mango, return an empty string (“”).
You can FILTER based on multiple conditions with the help of (*) operator.
PRO TIP: You can reference the spilled range in your dynamic array formula with the help of #. This is known as spill reference. Below is an image showing the use of # while writing your dynamic array formula. Keep in mind the referencing can be done only for spilled ranges.
Spill refs are extremely useful. You can use them as the source range for other dynamic array formulas, as done above to sort the list of unique values. You can make use of the spilled ranges and reference them in regular formulas like sum, count, vlookup, etc. They can also be used in case of named ranges or data validation.
NOTE: To edit the spilled range formula you must stay in the first cell of the array. Moving on to any cell within the spilled range array you will notice the formula is greyed out.
The character @ is also referred to as SINGLE at times.
In the Excel version, Microsoft 365 and above dynamic array formulas eliminate the need for Ctrl + Shift + Enter. Instead, the formula spills automatically. However, for compatibility reasons, you might see the @ symbol inserted in a formula created in an older (pre dynamic array) version of Excel.
This @ symbol is called an implicit intersection operator, and it disables array behaviour. In simple words, it tells Excel you want a single value. The value is returned based on the position value of the cell (row or column ).
This is done to ensure that older formulas continue to return the same (single) result and not spill over cells. In general, functions that return multi-cell ranges or arrays will be automatically prefixed with @ if they were created in an earlier version. This behaviour only occurs in dynamic array versions of Excel.
A common usage of @ can be spotted in Excel Tables.
To sum up, the new dynamic array formulas can be a great advancement.
- Dynamic array formulas in excel do not require the combination of Ctrl + Shift + Enter. A simple Enter is enough.
- The range automatically spills down.
- The spilled range can be referenced using #.
To know more about Array Formulas Click here.
Don’t miss out on the session New Formulas in MS Excel and its use cases. Click here
For more on Excel go through our Articles Section