Exceljet

Quick, clean, and to the point

Alternatives to Dynamic Array Functions

Dynamic Arrays are one of the biggest changes ever to Excel's formula engine. With 6 brand new functions that directly leverage dynamic arrays, they solve solve hard problems in Excel that have vexed even power users for decades. However, Dynamic Arrays are only available in Office 365, they are not available in Excel 2016 or 2019, and won't work in any older version.

For those not using Office 365, this page provides some alternative formulas that work in older versions of Excel. It's important to understand however, that none of the alternatives will spill multiple results onto the worksheet into a spill range like a native dynamic array formula. This behavior is limited to the Office 365 version of Excel.

Remember also that Pivot Tables can be used to solve many of these same challenges.

New dynamic functions

For reference, the 6 new dynamic array functions are listed in the table below. Click a function name for an overview and examples of usage.

Function Purpose
FILTER Filter data and return matching records
RANDARRAY Generate array of random numbers
SEQUENCE Generate array of sequential numbers
SORT Sort range by column
SORTBY Sort range by another range or array
UNIQUE Extract unique values from a list or range

ALTERNATIVES

This section contains alternative formulas that perform some of the same tasks as the functions in the table above. In almost all cases, the formulas are more complex and clunky than an equivalent dynamic array formula. At the same time, these formulas should work in almost any version of Excel.

Note: several of these formulas are traditional array formulas and need to be entered with control + shift + enter. If you are working with them in Dynamic Excel, you don't need to use control + shift + enter, Excel will handle the array operations automatically.

Sorting

Dynamic Excel provides two new functions to handle sorting with formulas: SORT and SORTBY. These functions make it easy to sort data by one or more columns, and even sort data by a custom list. However, if you are using an older version of Excel, you still have some options for sorting with a formula:

Filtering and extracting

Filtering and extracting data with formulas in Excel has always been a challenging problem. Dynamic Excel provides a special function, just for this purpose: the FILTER function. if you are using an older version of Excel, there are various ways to approach the problem.

Unique values

Dynamic Excel provides a dedicated function for working with unique values: the UNIQUE function. In other versions of Excel, you'll need to cobble together solutions based on several other functions. 

For counting unique values:

For extracting unique values, and other tasks:

Remember: Pivot Tables also provide good tools for listing and counting unique values.

Sequential values

One of the new Dynamic Array functions is SEQUENCE, specifically designed to generate numeric sequences.  With controls for the start and step value, and the ability to output results in rows, columns, or both, SEQUENCE is a flexible tool for generating sequential dates, times, and all manner of numbers. If you are using an older version of Excel, there are other ways to create numeric sequences, but they are not as convenient. Commonly, you'll see solutions that use the ROW function together with INDIRECT. Here are some examples:

Random values

The RANDARRAY function is also new in Dynamic Excel. RANDARRAY can generate random decimal or integer values in columns, rows, or two-dimensional arrays. The beauty of RANDARRAY is that you can ask for more than one random number at the same time. This is a huge benefit when building formulas that need to perform random sorts, or random item selection.

In older versions of Excel, you will typically use either the RAND function (decimal values) or the RANDBETWEEN function (integers) do perform the same tasks. 

Author 
Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.