Dynamic Array Functions And Formulas - Excel 365 Features

Excel Dynamic Array Formulas are a new and revolutionary office 365 update. It allows users to work with multiple values at the same time in formulas in an easy way. These dynamic Array formulas can remove duplicates, sort, filter, establish sequences and do lots more.
Excel dynamic array update is not available with Excel 2016 and Excel 2019 but you get it with the Microsoft 365 subscriptions supported by Windows and Mac both.

The main advantage of Dynamic Array formulas over the normal formulas is, normal formulas return the solutions in a single cell whereas Dynamic Array formulas result takes a range of cells i.e. spills which gives the dynamic effect. Now, any formula returns an array of values automatically spills into neighboring cells, without having to press Ctrl + Shift + Enter (CSE) or do any other moves. CSE formulas are complex ones, and we usually have to guess how many cells we need to copy them to.

Example:

Now understand the scenario with an example. Suppose you need to multiply two groups of numbers to calculate different percentages.

Scenario 1 – In normal version, you get the result of the formula for the first cell only unless you place it in multiple cells and press Ctrl + Shift + Enter to make it an array formula.

Scenario 2 – In this dynamic array formula, you need to type in one cell and press Enter key and you will get the whole rage filled with the results at once.

Note : Populating multiple cells with a single formula is known as “ Spilling” and the populated range of cells is known as “Spill range”.

Excel dynamic array functions

6 new functions are introduced with the new array update. Now with the use of these functions users can solve problems in an easy manner that are hard to solve with the traditional formulas. They support the dynamic output means the result update automatically whenever there is any change in the source data.

Here are the lists of functions –

FILTER	Filter a range of data based on criteria
RANDARRAY	Returns an array of random numbers
SEQUENCE	Returns a list of sequential numbers in an array
SORT	Sort range by specified column
SORTBY	Sort range by another range or array
UNIQUE	Extract unique values from a list or range

FILTER -

This function will allow users to filter a range of data based on different criteria. Without changing the original data, it extracts the filtered record into the spill range.

FILTER(array, include, [if_empty])

Here in the example Filter returned the desired result and if does not match the specified criteria then show “No Results”.

RANDARRAY –

It returns an array of random numbers. You just need to mention number of rows and column to fill, minimum and maximum values and what need to return, whole numbers or decimal values.

RANDARRAY([rows], [columns], [min], [max], [whole_number])

Here row is “6” to get result in 6 rows
Column is “4” to get result in 4 columns.
Min is 1, this is the minimum value to get
Max is 100, this is the maximum value to get
Whole_number is TRUE because we need integers.

SEQUENCE -

Function allows you to generate a list of sequential numbers in an array. Eg:1, 2, 3, 4. Here the result spilled into specified number of rows and columns automatically.

SEQUENCE(rows, [columns], [start], [step])

Here we have created 5 rows tall by 3 columns wide.

SORT -

Function sorts the contents of a range or array either in ascending or descending order. Depending on the shape of the source array results spills to the next cells vertically or horizontally.

SORT(array, [sort_index], [sort_order], [by_col])

Here Array is A2:B8
sort_index is column or row to sort by 2
sort_order is 1 that is ascending order

SORTBY -

Function Sort range by another range or array (Sorting Conditions).

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

Here Array is A2:A10
by_array1 is B2:B10 – sort by value
sort_order1 -1 that is descending order.

UNIQUE-

Function returns list of unique values from a range of cells. Just you need to select the range and hit enter. This is faster And simpler solution.

UNIQUE(array, [by_col], [exactly_once])

There are additionally two more up-to-date excel functions-

1. XLOOKUP -

it is more powerful successor of HLOOKUP, VLOOKUP and LOOKUP that return multiple values by looking up both in columns and rows.

2. XMATCH –

it is another useful successor of the MATCH function that returns a relative position of the specified item by performing vertical and horizontal lookups.

Conclusion:-

In the future, maybe more features will be added. But when you are using the existing one, remember to use the right syntax to get the desired result.