What are Dynamic Array Formulas?

Excel formulas that return multiple values (an array), will now display their results in adjacent cells. Microsoft has called this behaviour spilling, with the formulas being called spilled array formulas.

Excel will automatically create an output range in which to spill results.  The size of the output range will dynamically resize based on changes/additions to source data. It is recommended that you place your source data in an Excel table as they create a dynamic range for your formula – one that automatically grows as you add new data.  You cannot, however, use spilled array formulas within an Excel Table.

Existing array formulas in your worksheets, entered via CTRL+SHIFT+ENTER  are still supported for back compatibility reasons. To convert old array formulas to dynamic array formulas do the following:

  1. Copy the text of the first formula in your legacy array
  2. Delete all the array formulas
  3. Paste the text of the legacy array formula into the cell it was originally in, and press ENTER
  4. The formula will automatically spill into adjacent cells

When using a dynamic array formula where the source data is in another workbook, both workbooks need to be open for the formulas to work.  If the source workbook is closed the formula will return the #REF! error when refreshed.

Advantages of using dynamic array formulas vs legacy array formulas

– You no longer need to use CTRL SHIFT ENTER to create array formulas.

– Only one cell needs to be selected – the formula will automatically spill into adjacent cells

– Can be edited in the top left cell, whereas with the legacy version they have to be deleted and started again.

 

The UNIQUE Function

UNIQUE function arguments

Returns a list of unique values in a list or range.

array (required), the range of cells that you want to extract unique values from.  The range can contain multiple rows and columns – so you can extract unique records as well as unique values!

[by_col] (optional), enter FALSE or 0, if your data is arranged in columns, enter TRUE or 1 if your data is arranged in rows.  Default is FALSE.

[occurs_once] (optional), enter TRUE or 1 to return values that only appear once, enter FALSE or 0 to return a list of all the unique values. Default is FALSE.

———————————————————————————————————

Notes

Because the UNIQUE function has to be able to return more than one result, it spills its results into adjacent cells.

If your data is in an Excel table and you add or amend new values, the UNIQUE function will automatically update and resize the spilled array of results.

The #SPILL! error will occur if the UNIQUE function is prevented from spilling its results into adjacent cells.  Cells that already contain a value cannot be spilled into.

Use the spilled range operator (#) to reference to the entire spilled range. For example =A1# would refer to the entire spilled range whose formula is in cell A1.

 

The SORT Function

SORT function Arguments

Sorts the contents of a range or array

array (required) – the range of cells you want to return as a sorted range

[sort_index] (optional) the numeric position of the column or row you want to sort by – by default this is the first column or row

[sort_order] (optional) enter 1 for ascending order,  or -1 for descending order. 1 is the default.

[by_col] (optional) enter FALSE or 0 to sort vertically, TRUE or 1 to sort horizontally.  FALSE is the default.

———————————————————————————————————

Notes

Because the SORT function has to be able to return more than one result, it spills its results into adjacent cells.  When you select a cell in the spilled array, the formula bar shows the formula but it is greyed out. The only place you can edit the formula is in the cell you originally entered it in.

If your data is in an Excel table and you add or amend new values, the SORT function will automatically update and resize the spilled array of results.

The #SPILL! error will occur if the SORT function is prevented from spilling its results into adjacent cells.  Cells that already contain a value cannot be spilled into.

 

The SORTBY Function

SORTBY function Arguments

Sorts the contents of a range or array based on the values in a corresponding range or array

array (required) – the range of cells you want to return as a sorted range

by_array1 (required) the first column or row you want to sort on

[sort_order1] (optional) the order you would like to sort by_array1 by, use 1 for ascending & -1 for descending. 1 is the default.

by_array2 (required) the second column or row you want to sort on (within the first sort, ie “then by…”)

[sort_order2] (optional) the order you would like to sort by_array2 by, use 1 for ascending & -1 for descending. 1 is the default.

———————————————————————————————————

Notes

Because the SORTBY function has to be able to return more than one result, it spills its results into adjacent cells.  When you select a cell in the spilled array, the formula bar shows the formula but it is greyed out. The only place you can edit the formula is in the cell you originally entered it in.

If your data is in an Excel table and you add or amend new values, the SORT function will automatically update and resize the spilled array of results.

The #SPILL! error will occur if the SORT function is prevented from spilling its results into adjacent cells.  Cells that already contain a value cannot be spilled into.

You can sort by a maximum of 126 columns.

The FILTER Function

FILTER function arguments

Allows you to filter a range of data based on criteria you define.

array (required) the range of cells you want to return as a filtered range

include (required) the criteria you want to use in your filter.  You can use single or multiple criteria.  Use multiplication to specify AND criteria and addition to specify OR criteria.

example of single criteria – Sales[Branch]=J5

example of AND criteria – (Sales[Branch]=J5)*(Sales[Payment Type]=J4)

example of OR criteria – (Sales[Branch]=J15)+(Sales[Branch]=J16)

The boolean array must be the same height or width as the array.

[if_empty] (optional) the value to return if the filter returns nothing.  If you don’t specify an if_empty value the FILTER function returns the #CALC! error.

———————————————————————————————————

Notes

Because the FILTER function has to be able to return more than one result, it spills its results into adjacent cells.  When you select a cell in the spilled array, the formula bar shows the formula but it is greyed out. The only place you can edit the formula is in the cell you originally entered it in.

If your data is in an Excel table and you add or amend new values, the FUNCTION function will automatically update and resize the spilled array of results.

The #SPILL! error will occur if the FILTER function is prevented from spilling its results into adjacent cells.  Cells that already contain a value cannot be spilled into.

 

The SEQUENCE Function

SEQUENCE function arguements

Allows you to generate a list of sequential numbers in an array.

rows (required) – the number of rows to return

[columns] (optional)  – the number of columns to return, defaults to 1

[start] (optional) – the first number to return, defaults to 1

[step] (optional) – the increment to repeat, defaults to 1

—————————————————————————————————————————————–

Notes

Because the SEQUENCE function has to be able to return more than one result, it spills its results into adjacent cells.  When you select a cell in the spilled array, the formula bar shows the formula but it is greyed out. The only place you can edit the formula is in the cell you originally entered it in.

The #SPILL! error will occur if the SEQUENCE function is prevented from spilling its results into adjacent cells.  Cells that already contain a value cannot be spilled into.

 

Posted by Chester Tugwell