How to Use Dynamic Arrays in Excel (20 Useful Functions)

Dynamic array functions are a set of powerful formula tools introduced with the release of Excel 365 and Excel 2021 for Windows and Mac. These functions are like shortcuts that can automatically fill several cells with results without the need for complex formulas or manual adjustments.

We’ll use the sample dataset below to show how these functions work.

Download Practice Workbook

Function 1 – Using UNIQUE Function

The UNIQUE Function in Excel is used to get the unique values from a list of tables. We used the UNIQUE function to collect all the unique names providing the dynamic range.

• Choose a cell (G5), apply the below formula, and hit ENTER.

• We will get all the unique names in the new column without dragging the Fill Handle confirming the dynamic feature in Excel.

Function 2 – Applying FILTER Function

• Select a cell (B18), enter the following formula, and hit ENTER.

• We’ll get the filtered data from the table.

Function 3 – Inserting SORT Function

• Choose a cell (G5), insert the below formula, and press ENTER.

• It will sort selling price in descending order.

Function 4 – Applying SORTBY Function

• Choose a cell (G5), apply the below formula, and hit ENTER to get the result.

• We sorted the region in ascending order and the selling price in descending order.

Function 5 – Using RANDARRAY Function

• Choose a cell (D5), add the following formula, and hit ENTER.

• It generates random numbers for the chosen cells for product ID.

Function 6 – Using SEQUENCE Function

• Choose a cell (B5), add the following formula, and hit ENTER.

• The cells are filled with sequential numbers.

Function 7 – Inserting TEXTSPLIT Function

• Choose a cell (D5), add the formula, and hit ENTER.

• The texts are split extracting all the first names from the column.

Function 8 – Applying TOCOL Function

• To combine the data in column G from three rows, use the following formula.

• The data will get rearranged in a single column.

Function 9 – Applying TOROW Function

• Choose a cell (F4), add the formula, and hit ENTER.

• The range of data gets arranged row-wise. We organized the Student Names and IDs from column-wise to row-wise.

Function 10 – Applying WRAPCOLS Function

• Choose a cell (G5), enter the formula, and hit ENTER.

• The names will get grouped from a single column to multiple cells.

Function 11 – Using WRAPROWS Function

• Choose a cell (B8), add the formula, and hit ENTER.

• It will group and rearrange multiple names from the list.

Function 12 – Inserting TAKE Function

• Choose a cell (G5), apply the formula, and hit ENTER.

• It will return the first three rows as an array.

Function 13 – Inserting DROP Function

• From the whole range of data, we copied the Region and Selling Price column to a new location. Select a cell (G5), add the formula, and hit ENTER.

• The data will be copied to the chosen location.

Function 14 – Applying EXPAND Function

• Choose a cell (G4), enter formula, and click ENTER.

• The data will expand both row and column-wise. We used 13 inside the formula as we wanted to expand the rows to 13th, inserted 5 columns starting from the left, and placed asterisk (*) in the last argument to return it in the expanded cells.

Function 15 – Using CHOOSECOLS Function

• We will copy the 2nd and 4th columns from the table.
• Select a cell (G4), add the below formula, and press ENTER.

• The columns are placed in a new place in the spreadsheet.

Function 16 – Applying CHOOSEROWS Function

• Choose a cell (G4), apply the formula from below, and press ENTER.

• The chosen rows 1, 4, 7, and 9 will be copied to a new place.

Function 17 – Using XLOOKUP Function

The XLOOKUP function allows you to search for values in dynamic arrays. We searched for all the values with the provided criteria which is the Salesperson named John Doe.

• Choose a cell (C18), add the below formula, and press ENTER.

• We will get all the values from the table matching the criteria.

Function 18 – Applying VLOOKUP Function

The VLOOKUP function in Excel is a powerful and widely-used lookup function that stands for vertical lookup. We will use the VLOOKUP function with multiple lookup values which are salesperson to collect their product’s selling price.

• Choose a cell (C18), add the formula, and hit ENTER.

• We will get both the selling prices for the selected salesperson.

Function 19 – Inserting HLOOKUP Function

The HLOOKUP function known as the horizontal lookup function searches for a value in the top row of a table and returns a result from the specified row within the same column. We will extract the first 3 regions from the same column.

• Choose a cell (C18), add the following formula, and hit ENTER.

• It gives the first 3 region names from the column.

Function 20 – Using XMATCH Function

The XMATCH function returns the relative position of a specific value from a dynamic range. We will use the XMATCH function to search for the position of the row where the Salesperson named Emily Brown exists.

• Select a cell (H5), apply the below formula, and click ENTER.

• You will get the position of the cell in the worksheet.