Excel’s BYCOL() and BYROW() functions evaluate data across columns and rows, returning an array result set allowing you to bypass a lot of work.

Dynamic array functions transform how you work with multiple values simultaneously. They are ideal for automating repetitive tasks, analyzing datasets, and creating dynamic outputs.

In general, handling array functions like we’ve seen here, using JavaScript’s built-in functions like map() and filter(), is an excellent application of the power of functional programming.

The RANDARRAY function returns an array of random numbers whereby you can specify the number of rows and columns to fill, minimum and maximum values, and whether to return whole numbers or decimal ...

Let’s do an example where we will use the UNIQUE function to create a list of unique customers, as we did above, but let’s filter the list based on whether the customer is active. In cell E2, again ...

Dynamic array functions are supported by 365, Excel for the Web, Excel 2021, Excel for iPad and iPhone, Excel for Android tablets and phones. There’s no demonstration file; you won’t need one.

Using Array Functions with Dynamic Arrays You can use dynamic arrays with the other existing array functions (DIM, HBOUND, LBOUND) as long as the array has been created with MAKEARRAY or REDIM. If ...