When we think of older Excel versions, we often envision rows and columns that remain static unless we manually update them. But imagine if your data could behave like a traffic signal, adjusting in real-time to changing conditions. The dynamic array feature in Excel 365 turns this vision into reality. Think of dynamic arrays like managing a sports league where the number of teams and players can change at any time. Traditional Excel is like managing a league where you have to manually add or remove teams and players every season. But with dynamic arrays, your league adjusts itself – the teams, rankings, and even player stats automatically update based on changes you make!

This is a powerful new feature introduced in Excel that allows formulas to return multiple values to a range of cells automatically. This means that you no longer need to drag formulas down rows or across columns. The formula does the work for you, automatically expanding or contracting based on the dataset size.  In this blog, we’ll explore the magic of dynamic arrays, how they can transform your workflow, and even how ChatGPT can step in to help you master the art of writing Excel formulas for generating Dynamic Arrays. Find the downloadable copy of the examples right here: Dynamic Array Examples.xlsx. Please note that these functionalities are only available in Excel 365. 

1. Introduction to Dynamic Arrays

As mentioned, dynamic arrays allow formulas in Excel 365 to return multiple values at once, automatically filling adjacent cells. This is called “spilling,” and it allows you to manipulate data in new ways, from sorting and filtering to creating unique lists and sequences, without the need for helper columns or manual adjustments.

Dynamic arrays are particularly helpful in automating repetitive tasks, reducing manual input, and making spreadsheets easier to manage. They’ve become a fundamental part of how modern Excel operates, enabling users to build dynamic reports and dashboards that update in real-time.

Benefits:

• Automatically adjust to data changes.
• Eliminate manual adjustments like dragging formulas.
• Create scalable and error-resistant spreadsheets.

In the next section, we shall dive into why dynamic arrays are vastly better than traditional methods, showcasing practical examples for each advantage.

2. Key Features of Dynamic Arrays With Examples 

1. Instant Multi-Value Output

Before dynamic arrays, Excel formulas typically returned only one value. If you wanted a formula to return multiple values, you had to use array formulas, which were difficult to implement and understand.

Traditional Method:

Returning multiple values required entering an array formula by pressing Ctrl+Shift+Enter,  by dragging the formula down, or by clicking on the + icon in the formula cell. However, this approach does not compensate for the addition of new rows, i.e, if new data was added to the original set of records/table, you would have to manually add the formula in the corresponding columns for these new rows – making the whole process cumbersome. Additionally, in case of relative referencing, the range of cells may shift when you drag the formula down and this may cause unnecessary complications leading to incorrect results.

Example:

Assume you are a high school teacher and you are tracking student grades across different Student IDs, and this time, you want to find which students are eligible for a distinction.

Here’s what the data looks like in an Excel 2010 worksheet: 

Student Data in Excel 2010

To calculate this using lower excel versions, you can use the approach with IF function and copy the formula down the column.

Formula for Cumulative Grades (Without Dynamic Arrays):

=IF(C6:C15>9, “DISTINCTION”, “PASSED”)

Result: Watch this short video below:

As you can see here, the formula only returns the value in one cell. To apply the formula across multiple rows, you must drag the formula down to the desired range. 

Also, if you notice carefully, the formula gets modified when you drag the formula across the cells – the range of data changes! This can of course be avoided by using absolute referencing (i.e. $C$6:$C$15) but this may not be fruitful if you work with data that constantly grows. 

Dynamic Arrays Method:

With dynamic arrays, you can easily create multi-value outputs in one shot! Let’s do this in the latest MS Excel 365 version

Fun fact: A “Dynamic Array” is sometimes referred to as a “Spilled Array” due to its “Spill” feature – when a formula generates multiple results, those results automatically overflow into adjacent rows or columns. If the output would conflict with existing data in the spreadsheet, a #SPILL! error will appear instead.

Let’s look at how to get the same result for CGPA calculation with dynamic arrays.

Formula for Distinction filter (With Dynamic Arrays):

=IF(C6:C15>9, “DISTINCTION”, “PASSED”)

This is the same formula as the one used in the lower excel versions. But take a look at the result and notice the difference!

Result: Watch this video below:

As you can see here, the formula returns multiple values in one cell without any manual intervention. Additionally, the range of cells doesn’t shift either as you can see that the cells starting from D7 go inactive as they are now part of a dynamic array result  – meaning no unnecessary relative referencing! 

2. Dynamic Adjustment to Changing Data Sizes

Dynamic arrays adjust automatically to changes in data size. As you add or remove rows, the formula output updates in real-time without any additional work on your part.

Example:

Let’s go for a realistic business scenario that involves employee performance metrics across multiple KPIs (Key Performance Indicators) to understand how dynamic arrays work for changing data. Here we have the KPI info for 2 teams over the past 6 months. As a manager, you would like to assess which of the two teams are performing better and must be retained. Let’s say you would like to explain this to higher management – the best way to go about it would be to describe this pictorially through a chart! Take a look at the upcoming video below to see how you can do this!

KPI chart for Team A and Team B over 6 months

The above data must be converted to a dynamic table first. In Excel 365, tables are considered dynamic arrays because they automatically adjust their size and range as data is added, removed, or updated. This makes them particularly powerful for handling dynamic datasets. These tables use structured references instead of traditional cell references (e.g., A1). This means you can refer to specific columns by name, making your formulas clearer and easier to manage. Structured references are dynamic by nature. If your table grows or shrinks, these references automatically reflect the current size of the table. 

Steps to follow:

• Select the entire data range with the headers and go to Insert > Table > OK.
• Then select this table and go to Insert > Charts > Clustered Column. 

Now if you add new data to the table, the associated chart gets updated too.

Result:

3. Simplified Referencing with the # Character

One of the powerful features of dynamic arrays in Excel is the ability to easily reference the entire result of a dynamic array using the # character. This eliminates the need to adjust cell references manually whenever the size of the array changes. Instead of specifying a fixed range like “=$C5:$D100”, you can simply use “=$C5#” to reference the entire dynamic array. This feature significantly simplifies formula management and enhances spreadsheet flexibility.

Example: 

Let’s say you have a dataset recording the daily high temperatures for a week in various cities, as shown below and would like to perform some Weather Data Analysis:

USA Weather Data

Step 1: Create a Dynamic Array

After creating a dynamic table by selecting the desired range and going to Insert > Table > OK, to summarise the unique cities and their average high temperatures over the recorded days, follow these steps:

1. In cell G6, enter the following formula to extract unique city names:

=UNIQUE(Table1[City])

Where Table1[City] = B6:B13

This creates a dynamic array result in G6.

2. In cell H6, enter the following formula to calculate the average high temperature for each unique city:

=AVERAGEIF(Table1[City], G6#, Table1[High Temp])

Where Table1[City] = B6:B13

Table1[High Temp] = D6:D13

Step 2: Results

After entering these formulas, your spreadsheet will look like this:

How It Works?

• The formula in G6 uses UNIQUE() to generate a dynamic array of city names. As more cities are added to the original dataset, this list will automatically update.
• The formula in H6 uses AVERAGEIF() to calculate the average high temperature for each city by referencing the dynamic array in G6 with G6#. If the list of unique cities changes due to new data or removed cities, the average temperature calculations will automatically adjust accordingly.

Benefits of Using the # Character

• No Manual Adjustments: By using G6#, you avoid having to update the range whenever new cities are added. The average high temperatures for each city remain accurate and dynamic.
• Cleaner Formulas: This approach makes your formulas easier to read and manage. You can quickly understand that G6# refers to the entire array of unique city names.
• Dynamic Updates: The spreadsheet adapts automatically as data changes, saving time and reducing errors associated with manual range adjustments.

3. Core Dynamic Array Functions

Here’s a brief overview of the most powerful dynamic array functions that you can start using right away:

• FILTER(): Filters data based on conditions.
• UNIQUE(): Extracts unique values from a dataset.
• SORT(): Sorts data dynamically by columns or rows.
• SORTBY(): Sorts data dynamically by more than one column or rows.
• SEQUENCE(): Generates a list of numbers.
• RANDARRAY(): Produces random numbers in an array format.
• ARRAYTOTEXT(): Converts a range of data (array) to text.

Follow along to see how each of these functions work in depth!

We shall consider this project dataset to test out the above functions:

Employee Data

This is a dynamic table that was created in the same fashion as described in the KEY FEATURES section. 

1. FILTER()

Let’s say you’d like to filter out employees who achieved a completion rate greater than 90% to reward them. You can use the new FILTER() function to achieve that!

Syntax:

FILTER(array, include, [if_empty])

array: The range or array to filter.

include: The condition that must be met.

if_empty (optional): The value to return if no rows meet the condition (e.g., “No employees found”).

Formula:

=FILTER(Table10[Employee Name], Table10[Completion Rate (%)] > 90, “No employees found”)

Where Table10 refers to the Employee Table shown above.

Result:

Result – Filter employees with completion rate greater than 90% 

If you’d like to filter based on multiple conditions, here’s how you can do it:

With AND Expression: 

Suppose you would like to give an interdepartmental rewards as well as an IT manager to the IT employees and keep the completion rate benchmark as 80% or above, here’s how you can modify the filter formula:

=FILTER(Table10[Employee Name], ((Table10[Completion Rate (%)] > 80) * (Table10[Department]=”IT”)), “No employees found”)

The “*” stands for an AND expression. This will filter the rows that will satisfy the condition that the completion rate is above 80% and the Department is IT. Both the conditions must be met.

With OR Expression: 

Suppose you would now want to see all those employees who either have 90% or above completion rate or have a feedback rate above 4.5, here’s how you can modify the filter formula:

=FILTER(Table10[Employee Name], ((Table10[Completion Rate (%)] >90) + (Table10[Feedback Score]>4.5)), “No employees found”)

The “+” stands for an OR expression. This will filter the rows that will satisfy the condition that the completion rate is above 90% or the feedback score is over 4.5. Either one of the conditions must be met.

**

2. UNIQUE()

You can use this function to extract the distinct project names in a sheet.

Syntax:

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

array: The range or array from which to extract unique values (e.g., C2:C10).

by_col (optional): A logical value indicating whether to compare by column (TRUE) or row (FALSE). Default is FALSE.

exactly_once (optional): A logical value indicating whether to return only values that appear exactly once. Default is FALSE.

Formula:

=UNIQUE(Table10[Project Name])

Where Table10 refers to the Employee Table shown above.

Result:

Unique Projects List

**

3. SORT()

Now, we’d like to know the most highly-rated employee and give them awards of recognition.  To do this, employees are sorted by feedback scores in descending order using SORT() function in Excel 365. 

Syntax:

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

array: The range or array to sort (e.g., A2:E10).

sort_index (optional): The column or row to sort by (e.g., 5 for feedback score).

sort_order (optional): 1 for ascending and -1 for descending. Default is 1.

by_col (optional): A logical value indicating whether to sort by column (TRUE) or row (FALSE). Default is FALSE.

Formula:

=SORT(Table10, 5, -1)

Where Table10 refers to the Employee Table shown above.

Result:

Sorted Data – By Feedback Score

Now, if you further want to sort this based on ‘Completion rate (%)’ as well, you could nest the two sorts as shown below. But this approach would get messy if there are multiple columns to sort by – say some 5 columns. It is important to make sure your formulas are easy to comprehend to help you debug any error in the future.

=SORT(SORT(Table10, 5, -1),4,-1)

Sorted Data – By Completion Rate & Feedback Score

So, what’s the way to sort by multiple columns with just one formula? Find out in the next example!

**

4. SORTBY()

Let’s take a look at how to sort the data by feedback score first and then by completion rate.

Syntax:

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

array: The range or array to sort .

by_array1: The range or array to sort by.

sort_order1 (optional): 1 for ascending and -1 for descending. Default is 1.

Formula:

=SORTBY(Table10, Table10[Completion Rate (%)], -1, Table10[Feedback Score], -1)

Where Table10 refers to the Employee Table shown above.

Here both the columns are sorted in a descending order. We wish to give higher preference to Completion rate first and then the individual feedback score, hence the chosen order. 

Result:

Sorted Data – By Completion Rate & Feedback Score

**

5. SEQUENCE()

We can use this function to create a continuous sequence of project numbers corresponding to unique projects. Don’t worry if this seems illogical as of now. The SEQUENCE() function is mostly used as a helper function to other functions like RANDARRAY, SORTBY, etc wherever there is a need for some sequence of numbers required for some calculation. 

Syntax:

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

rows: The number of rows to return (e.g., 6 for six projects).

columns (optional): The number of columns to return (e.g., 1).

start (optional): The first number in the sequence (e.g., 1).

step (optional): The amount to increment each subsequent number (e.g., 1).

Formula:

=SEQUENCE(COUNTA(UNIQUE(Table10[Project Name])), 1, 1, 1)

Where Table10 refers to the Employee Table shown above.

UNIQUE(Table10[Project Name]): Retrieves a list of unique project names from the “Project Name” column in Table10.

COUNTA(…): Counts the number of unique project names found, which will be 6 in this case.

SEQUENCE(…): Generates a vertical array of numbers from 1 to 6 (the count of unique projects), with 1 column and an increment of 1.

Result:

Sequence for all projects

**

6. RANDARRAY()

Say there’s a fun lucky-draw contest at your office and you would like to give some amount of money ranging from $10-$40 to each employee. To make it unbiased, we can use the RANDARRAY() function.

Syntax:

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

rows (optional): The number of rows to return (e.g., 9).

columns (optional): The number of columns to return (e.g., 1).

min (optional): The minimum value (e.g., 0.01).

max (optional): The maximum value (e.g., 0.1).

integer (optional): A logical value specifying whether to return integer values (TRUE or FALSE).

Formula:

Create a list of all employees in a column. In the adjacent column, enter the below formula:

=RANDARRAY(ROWS(Table10[Employee Name]), 1, 10, 40, TRUE)

Where Table10 refers to the Employee Table shown above.

Result:

Random amount assigned during lucky draw contest

As you can see, there is now a certain amount assigned to each of the employees in the company. RANDARRAY() also gives duplicates – same amount of money is given to Alice and Bob, and Eve and Grace. 

**

7. ARRAYTOTEXT()

Syntax:

ARRAYTOTEXT(array, [format])

array: The array or range to convert to text (e.g., A2:D7).

format (optional): A format string to specify how to display the values (e.g., “0” for integers).

Formula:

=ARRAYTOTEXT(Table10[Employee Name])

Where Table10 refers to the Employee Table shown above.

Result:

1D Array of Employee Names converted to Comma-seperated List

**

4. Using ChatGPT for Complex Dynamic Array Formulas

Dynamic arrays are powerful, but sometimes you may need help constructing the perfect formula for a specific scenario. You can use ChatGPT to assist you in building or troubleshooting Excel formulas. Here’s a prompt to get you started:

Prompt: “My company has collected feedback from customers regarding their recent purchases. Each piece of feedback includes a customer ID, the product they purchased, and a satisfaction score (out of 10). 

The company wants to analyse this feedback to identify unique products that received a high satisfaction score (8 and above). 

The data is in a dynamic table called Table11 and has three columns – Customer ID, Product Name, Satisfaction Score. Write me the dynamic array formula to achieve the said task“

ChatGPT will generate the correct formula, simplifying the process for you!

Here’s the formula ChatGPT gave us:

=UNIQUE(FILTER(Table11[Product Name], Table11[Satisfaction Score] >= 8))

5. Real-World Applications of Dynamic Arrays

Dynamic arrays are useful in real-world applications like:

• Live Dashboards: Create dashboards that update in real-time as data changes.
• Automated Reporting: Generate reports dynamically without needing to update formulas manually.
• Data Cleaning: Easily extract unique values or filter out unnecessary data.
• Budget Tracking: Dynamically track expenses and compare them against budget limits.

6. Limitations of Dynamic Arrays

• Sorting Limitations

The results produced by a dynamic array formula cannot be sorted using Excel’s built-in Sort feature. Attempting to do so will generate a “You cannot change part of an array” error. To sort the results, you need to use the SORT function. For example, if you want to sort a list of names that are part of a dynamic array (say, in cell G2) alphabetically, you would use a formula like “=SORT(G2#,1)”. This ensures that your dynamic array can be sorted too. 

• Inability to Delete Values in Spill Range

Once a dynamic array formula produces a spill range, you cannot individually delete or modify any of the values within that range. This restriction is similar to traditional CSE (Ctrl+Shift+Enter) array formulas, where altering any part of the array leads to errors. For instance, if you have a spill range showing filtered results, you can’t just delete one of those results without affecting the entire array formula.

• Conversion to Dynamic Tables Manually 

If you try to create a table from a range that contains a dynamic array / spill formula like we did in many of the examples that we’ve seen so far, Excel will give you a #SPILL! error. This happens because Excel tables are designed to manage structured data with a well-defined set of rows and columns. When you convert a range into a table, Excel expects each cell to hold a single value that can be manipulated, sorted, or filtered independently. But with dynamic formula’s resultant ranges, the values get “spilled” over and not “assigned” to each cell particularly. 

To overcome this error, it is recommended to reference the entire dynamic range with the “#” character as discussed in our KEY FEEATURES section instead of trying to convert it to a table. This will fetch you the desired results. 

7. Best Practices for Using Dynamic Arrays

To get the most out of dynamic arrays, follow these best practices:

• Keep formulas simple: Use built-in dynamic array functions like FILTER() and SORT() instead of complex combinations.
• Avoid helper columns: Rely on dynamic arrays to reduce clutter. Use # to reference them.
• Use Named Ranges: If you frequently refer to specific ranges, consider using named ranges. This can make your formulas cleaner and easier to manage.

8. Conclusion

Dynamic arrays represent a massive leap forward in how Excel handles data, offering improved performance, simplicity, and adaptability. Whether you’re managing large datasets, building dashboards, or creating dynamic reports, dynamic arrays can help you work more efficiently and accurately. By eliminating manual steps, reducing errors, and streamlining complex tasks, they transform how users of all levels work with data in Excel.

If you haven’t started using dynamic arrays yet, now is the perfect time to upgrade your Excel skills and experience the power of dynamic arrays firsthand! Get started with a downloaded copy from here: Dynamic Array Examples.xlsx. Good luck with the Excel adventure!