Dynamic arrays in Excel help handle data without the need for complex formulas or manual adjustments. With the help of dynamic array formulas, you can quickly get the expanded required data in your Excel spreadsheet.

Today in this article, I am sharing how to use dynamic arrays in Excel using 20 functions with proper examples and illustrations.

In the following, you will find an overview of how to use dynamic arrays in Excel.

**Table of Contents**Expand

## Download Practice Workbook

## What Are Dynamic Arrays in Excel?

Dynamic arrays provide you with the result of generating all the output data at once. Unlike traditional Excel functions that return a single result, dynamic arrays can spill their results across adjacent cells. This simplifies complex calculations, accelerates data analysis, and makes managing dynamic datasets more efficient. Traditionally for dynamic calculation, we pressed **CTRL+SHIFT+ENTER**Â to get the dynamic range outputs. But now in **Excel 365**Â you just need to press **ENTER **to get the final result.

In the following image you will see with just a single dynamic array formula we got all the output reducing time and complexity.

## How to Use Dynamic Arrays in Excel: 20 Dynamic Array Functions

Suppose we have a dataset of some **Salesperson**, **Product**, **Region**, and their **Selling Price **in an Excel worksheet. Now we will use dynamic array formulas to get the required data with a single click.

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 provide dynamic arrays to automatically spill their results across multiple cells. Without moving here and there letâ€™s get introduced to these functions.

### 1.Â Using UNIQUE Function

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

- Simply, choose a
**cell**(**G5**), apply the below formula, and hit**ENTER**.

`=UNIQUE(B5:B15)`

- This way we will get all the unique names in the new column without dragging the
**Fill Handle**confirming the dynamic feature in Excel.

### 2.Â Applying FILTER Function

In order to filter data previously we had to type the complex formula and then drag the fill handle to get the result for multiple cells. But now you can insert the range in an array and get the result with a single formula. Isnâ€™ it easy? In this part, we used **the FILTER function**Â to get the filtered data according to the criteria from the table.

- Select a
**cell**(**B18**), write the following formula, and click**ENTER**.

`=FILTER(B5:E15,(C5:C15=H4)*(D5:D15=H5),"no results")`

- As a result, we got the filtered data from the table with a single formula.

### 3.Â Inserting SORT Function

With the proper utilization of dynamic arrays, you can use **the SORT function**Â in Excel to sort the whole dataset according to ascending or descending order.

- Here we will sort the selling prices in descending order. First, choose a
**cell**(**G5**), insert the below formula, and press**ENTER**.

`=SORT(B5:E15,4,-1,FALSE)`

- In conclusion, we have successfully got the sorted selling price in descending order. Simple isnâ€™t it?

### 4.Â Applying SORTBY Function

The **SORTBY **function sorts the contents of a range or array based on the values in a corresponding range or array.

- Choose a
**cell**(**G5**), apply the below formula, and click**ENTER**to get the result.

`=SORTBY(B5:E15,D5:D15,1,E5:E15,-1)`

- Finally, we will get the result by sorting multiple columns from the table. In this part, first, we sorted the region in ascending order and then the selling price in descending order.

### 5.Â Using RANDARRAY Function

If you want you can also collect random values within the provided range and columns with a single click. Simply, use the **RANDARRAY **function to get random numbers for multiple cells.

- Start with choosing a
**cell**(**D5**), write the following formula, and finish by hitting**ENTER**.

`=RANDARRAY(11,1,1000,9000,TRUE)`

- In summary, we will get the random numbers generated for the chosen cells for product ID with a single formula.

### 6.Â Using SEQUENCE Function

Previously, we used to insert sequential numbers and then **Flash Fill**Â to fill cells with sequential numbers. With dynamic array functions like **the SEQUENCE functionÂ **in Excel, we can directly get the sequence numbers for multiple cells. In this section, we created sequence numbers starting from 1 to 11 in the Sl. No. column.

- Choose a
**cell**(**B5**), write the following formula, and hit**ENTER**key from the keyboard.

`=SEQUENCE(11)`

- Within a moment, our sequence numbers will be in our hands.

### 7.Â Inserting TEXTSPLIT Function

For splitting texts to multiple cells you can use the **TEXTSPLIT **function in Excel. In this method, we split the first name from the full name column.

- In the same fashion, choose a
**cell**(**D5**), apply the formula, and click**ENTER**.

`=TEXTSPLIT(B5:B14," ")`

- Thatâ€™s it, you will see the texts are split extracting all the first names from the column.

### 8.Â Applying TOCOL Function

In recent, Excel has introduced the **TOCOL **function which is mainly used to convert multiple data from rows to a single column.

- To combine the data in column G from three rows, we used the following formula.

`=TOCOL(C4:E6)`

- Within a blink of an eye, we will get all the data rearranged in a single column. Simple isnâ€™t it?

### 9.Â Applying TOROW Function

Well, the **TOROW **function works the same as** the TRANSPOSE function**Â in Excel. But for disorganized data, we recommend using the **TOROW **function in Excel.

- From the spreadsheet choose a
**cell**(**F4**), write the formula, and hit**ENTER**.

`=TOROW(B5:B10)`

- Finally, we have the range of data arranged row-wise. Here we organized the Student Names and IDs from column-wise to row-wise.

### 10.Â Applying WRAPCOLS Function

In order to wrap and group data to multiple columns you can try the **WRAPCOLS **function. Here, weâ€™ll divide the name column into columns each column will have 5 names.

- Start with choosing a
**cell**(**G5**), writing the formula, and clicking**ENTER**.

`=WRAPCOLS(B5:B14,5)`

- Within a glimpse, we will get the names grouped from a single column to multiple cells.

### 11.Â Using WRAPROWS Function

Just like the previous method, you can use the **WRAPROWS **function to rearrange rows of data into multiple groups.

- In this section, we made two groups each consisting of 4 names from the Student Name row. For this, choose a
**cell**(**B8**), write the formula, and hit**ENTER**.

`=WRAPROWS(B5:I5,2)`

- In conclusion, we have successfully grouped and rearranged multiple names from the list.

### 12.Â Inserting TAKE Function

In order to rearrange data according to your choice of group of cells you can try the **TAKE **function in Excel.

- Just choose a
**cell**(**G5**), apply the formula, and hit**ENTER**.

`=TAKE(B5:E15,3)`

- Finally, you will see that the function has returned the first three rows as an array.

### 13.Â Inserting DROP Function

Sometimes copying and placing data into multiple locations seems monotonous work. Well, with the help of the **DROP **function, you can directly copy multiple columns and rows with a single formula.

- From the whole range of data, we copied the Region and Selling Price column to a new location. Thus, select a
**cell**(**G5**), type the formula, and hit**ENTER**.

`=DROP(B5:E15,,2)`

- Within a moment, according to the command of the formula, the data will be copied to the chosen location.

### 14.Â Applying EXPAND Function

The **EXPAND **function increases a cell range or array by a given number of rows and columns in an Excel spreadsheet.

- Choose a
**cell**(**G4**), place the following formula, and click**ENTER**.

`=EXPAND(B4:E15,13,5,"*")`

- In conclusion, we will see the data has expanded both row and column-wise. As you can see we used 13 inside the formula as we want to expand the rows to 13th. Similarly, inserted 5 columns starting from the left. And placed
**asterisk (*)**in the last argument to return it in the expanded cells.

### 15.Â Using CHOOSECOLS Function

Sometimes copying non-adjacent columns from a large dataset might seem a tough one. But with proper use of the **CHOOSECOLS **function, you can copy multiple columns easily.

- Here, we will copy the 2nd and 4th columns from the table. Thus, select a
**cell**(**G4**), write the below formula, and press**ENTER**.

`=CHOOSECOLS(B4:E15,{2,4})`

- In summary, we will get the columns placed in a new place in the spreadsheet.

### 16. Applying CHOOSEROWS Function

Are you looking for a solution where you can copy multiple non-adjacent rows with only one formula? Well, you are at the right place where we will use the **CHOOSEROWS **function to copy multiple rows to a new location.

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

`=CHOOSEROWS(B4:E15,{1,4,7,9})`

- Finally, the chosen rows 1, 4, 7, and 9 will be copied to a new place with just a single click. Itâ€™s that simple.

### 17.Â Using XLOOKUP Function

**The XLOOKUP function**Â allows you to search for values in dynamic arrays. Here in this part, we searched for all the values with the provided criteria which is the Salesperson named John Doe.

- Simply, choose a
**cell**(**C18**), write the below formula, and press**ENTER**.

`=XLOOKUP(B18,B5:B15,C5:E15)`

- Thus we will get all the values from the table matching the criteria. Look, there are two John Doe,
**XLOOKUP**will return the first match.

### 18.Â Applying VLOOKUP Function

**The VLOOKUP functionÂ **in Excel is a powerful and widely-used lookup function that stands for vertical lookup. Here, we will use the **VLOOKUP **function with multiple lookup values which are salesperson to collect their productâ€™s selling price.

- For this, choose a
**cell**(**C18**), write the below formula, and click**ENTER**.

`=VLOOKUP(C17:D17,B5:E15,4,FALSE)`

- As a result, we will get both the selling prices for the provided salesperson.

### 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. In this section, we will extract the first 3 regions from the same column.

- For that, choose a
**cell**(**C18**), write the following formula, and hit**ENTER**.

`=HLOOKUP(C17,B4:E15,{2,3,4},FALSE)`

- As a result, we have successfully run the horizontal lookup and got the first 3 region names from the column.

### 20.Â Using XMATCH Function

The **XMATCH **function returns the relative position of a specific value from a dynamic range. In this section, we are using 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**.

`=XMATCH(G5,B5:B15,0)`

- As a result, you will get the position of the cell in the worksheet.

## Advantages of Dynamic Arrays in Excel

- With dynamic arrays, you can use regular formulas that automatically handle multiple values without requiring special array syntax.
- Functions like
**SORT**,**FILTER**,**UNIQUE**, and**SEQUENCE**enable the extraction of data to easily and quickly solve complicated problems. - Dynamic arrays provide better error handling by returning an error for one element without affecting the other elements in the worksheet.

## Disadvantages of Dynamic Arrays in Excel

- Dynamic arrays are available in
**Excel 365**,**Excel 2021**, and newer versions. If you need to share your workbook with users who have older Excel versions, they wonâ€™t be able to use dynamic array functions. - Dynamic arrays do have some limitations with user-defined functions. Some user-defined functions (custom functions created with
**VBA**) may not work seamlessly with dynamic arrays.

## Difference Between Dynamic Arrays and Traditional Arrays in Excel

- Dynamic array functions use regular formula syntax, just like any other Excel function. On the other hand, traditional array formulas require a special syntax, typically involving array operators. You also need to manually enclose the formula in the desired range using
**CTRL+SHIFT+ENTER**. - Excel introduced new dynamic array functions like
**SORT**,**FILTER**,**UNIQUE**, and**SEQUENCE**, which enable powerful data manipulation and analysis. While traditional array formulas can perform similar tasks, they often require more complex formula structures.

## Compatibility Issues of Dynamic Arrays in Excel

Compatibility issues arise with dynamic arrays in Excel due to their limited availability in older versions of Excel. After opening the file with an older version of Excel you will find errors just like the following image for dynamic arrays.

## What to Do When Dynamic Arrays Formula Is Not Working in Excel

While dynamic arrays in Excel offer numerous advantages, they can also lead to certain errors if not used correctly. Some common errors associated with dynamic arrays are described below.

### 1.Â #SPILL! Error

Most of the time you will find **#SPILL! Error**Â as it happens when the formulaâ€™s output cannot fit into the available space in the adjacent cells.

- Here we choose a
**cell**(**G5**), write the formula and press**ENTER**.

`=B5:B15`

- As a result, you will find
**#SPILL! Error**as there are values inserted previously inside the output column.

- Now, selecting
**cell**(**G10**), click**DELETE**to remove the content from the cell.

- Finally, you will get the output without
**#SPILL! Error**.

### 2.Â #REF! Error

The **#REF! error**Â in dynamic arrays occurs when a formula refers to a cell or range that is invalid or unavailable due to changes in the spreadsheet.

- Simply, use the below formula with the exact range to solve the
**#REF! errorÂ**problem.

`=SUM(E5:E15)`

### 3.Â #NAME? Error

A **#NAME? error**Â occurs when the formula contains a named range and the name range does not exist. Sometimes due to inappropriate references, it might occur too.

- In this part, we select a
**cell**(**G5**), write the below formula, and hit**ENTER**.

`=SUM(E5E15)`

- Thus we will get
**#NAME? Error**as we used an inappropriate range inside the formula.

- Simply, use the below formula which refers to the exact cell range to solve
**#NAME? errorÂ**problem.

`=SUM(E5:E15)`

## Things to Remember

- While using dynamic arrays be cautious about creating circular references with dynamic arrays, as it can lead to incorrect results and a circular reference warning.

## Frequently Asked Questions

**1. How do I handle errors in dynamic array formulas?**

You can use error-handling functions like **IFERROR** or **IFNA **to manage potential errors that might occur with dynamic arrays and display meaningful messages or alternative results.

**2. Can I use dynamic array functions with traditional array formulas?**

It is generally not recommended to combine dynamic array functions with traditional array formulas, as it can lead to conflicts and errors.

**3. How do I avoid circular references with dynamic arrays?**

Avoid referencing a dynamic array formulaâ€™s own spill range or creating formulas that depend on each other in a circular manner to prevent circular reference errors.

## Conclusion

Dynamic arrays in Excel offer significant advantages, simplifying formulas, enabling automatic spill ranges, and providing better error handling. However, you have to be mindful of compatibility with older Excel versions. Overall, using dynamic arrays can lead to more efficient and powerful spreadsheet solutions in **Excel 365**Â and newer versions. Take a tour of the practice workbook and download the file to practice by yourself. Please inform us in the comment section about your experience. We, the **Exceldemy**Â team, are always responsive to your queries. Stay tuned and keep learning.