How to Use Dynamic Formulas in Excel

Excel is a powerful tool for working with data, but sometimes you may want to create formulas that can handle multiple values at the same time, without having to copy and paste them into different cells. This is where dynamic formulas come in handy.

Dynamic formulas are a new feature in Excel 365 and Excel 2021 that allow you to enter a formula in a single cell and have it return an array of values that automatically spill into neighboring cells. This makes it easier to work with complex calculations, such as filtering, sorting, extracting unique values, generating sequences, and more.

In this article, we will explain the concept of dynamic formulas, show you some examples of how to use them, and discuss their advantages and limitations.

What are dynamic formulas?

Dynamic formulas are formulas that can return an array of values of variable size, depending on the input data and the function used. An array is a collection of values that are arranged in rows and columns. For example, the following table is an array of 3 rows and 4 columns:

1	2	3	4
5	6	7	8
9	10	11	12

In previous versions of Excel, you had to enter an array formula in each cell where you wanted a result to appear, and press Ctrl + Shift + Enter to confirm it. This was called a CSE (Ctrl + Shift + Enter) array formula. For example, if you wanted to multiply two arrays of numbers, you had to select the range where you wanted the results to appear, enter the formula, and press Ctrl + Shift + Enter:

In Excel 365 and Excel 2021, you can enter a dynamic formula in just one cell, press Enter, and have it return an array of values that automatically spill into adjacent cells. This is called spilling. The range of cells that contains the spilled values is called the spill range. For example, if you enter the same formula as above in one cell, you get the following result:

As you can see, dynamic formulas make it much easier to work with arrays in Excel. You don’t have to select the output range beforehand, or press any special keys to confirm the formula. You just type the formula in one cell and let Excel do the rest.

How to create dynamic formulas?

To create a dynamic formula, you need to use a function that can return an array of values. Excel has introduced several new functions that are designed for this purpose. These functions are called dynamic array functions, and they include:

FILTER: Filters data and returns matching records
RANDARRAY: Generates an array of random numbers
SEQUENCE: Generates an array of sequential numbers
SORT: Sorts an array by one or more columns
SORTBY: Sorts an array by another array or expression
UNIQUE: Extracts unique values from an array
XLOOKUP: Looks up a value in an array and returns a corresponding value from another array
XMATCH: Looks up a value in an array and returns its relative position

You can also use some existing functions that can return arrays, such as INDEX, TRANSPOSE, FREQUENCY, etc.

To create a dynamic formula, you simply enter the function in one cell and press Enter. For example, if you want to extract unique values from a list of cities in column B, you can enter the following formula in cell E5:

=UNIQUE(B5:B15)

This will return an array of unique city names that will spill into cells E5:E9:

You can also combine multiple functions in one formula to perform more complex calculations. For example, if you want to sort the unique city names alphabetically, you can use the SORT function with the UNIQUE function:

=SORT(UNIQUE(B5:B15))

This will return the same array of city names, but sorted in ascending order:

![SORT function]

You can also use references or expressions as arguments for dynamic functions. For example, if you want to generate a random number between 1 and 10 for each city name, you can use the RANDARRAY function with the COUNTA function:

=RANDARRAY(COUNTA(UNIQUE(B5:B15)),1,1,10)

This will return an array of random numbers between 1 and 10 that matches the size of the unique city names:

![RANDARRAY function]

How to refer to spilled arrays?

One of the benefits of dynamic formulas is that they update automatically when the input data changes. For example, if you add or remove a city name from column B, the UNIQUE function will adjust its output accordingly.

However, this also means that the size and location of the spill range may change as well. Therefore, you need a way to refer to the spilled array without using a fixed cell reference. For example, if you want to sum the random numbers generated by the RANDARRAY function, you cannot use a simple SUM function with a range reference like this:

=SUM(F5:F9)

This will work only as long as the spill range is F5:F9. If the spill range changes, the SUM function will not capture the new values.

To solve this problem, Excel has introduced a new syntax for referring to spilled arrays. You can use the # (hash) symbol after the cell reference that contains the dynamic formula. For example, to sum the random numbers generated by the RANDARRAY function in cell F5, you can use this formula:

=SUM(F5#)

This will return the sum of all the values in the spill range, regardless of its size and location. The # symbol tells Excel to refer to the entire spilled array, not just the cell that contains the formula.

You can also use the # symbol with named ranges or table references. For example, if you name the range B5:B15 as Cities, you can use this formula to extract unique city names:

=UNIQUE(Cities)

This will return an array of unique city names that will spill into adjacent cells. To refer to this spilled array, you can use this syntax:

=UNIQUE(Cities)#

This will refer to the entire spill range of the UNIQUE function.

How to handle implicit intersection?

Another change that comes with dynamic formulas is how Excel handles implicit intersection. Implicit intersection is a feature that allows Excel to return a single value from an array when a single value is expected. For example, if you have a table of sales data like this:

![Sales data]

And you enter this formula in cell G2:

=SUM(B2:F2)

This will return the sum of sales for January in cell G2. However, if you copy this formula down to cells G3:G6, Excel will automatically adjust the row references and return the sum of sales for each month:

![SUM function]

This is because Excel implicitly intersects the array returned by the SUM function with the row reference of each cell. In other words, Excel returns a single value from an array based on the relative position of the cell that contains the formula.

However, in Excel 365 and Excel 2021, implicit intersection is no longer supported for dynamic formulas. This is because dynamic formulas are designed to return multiple values, not single values. Therefore, if you enter a dynamic formula in a cell that expects a single value, Excel will return an error.

For example, if you enter this formula in cell G2:

=UNIQUE(B2:F2)

This will return an error message: #SPILL!

![SPILL error]

This is because the UNIQUE function returns an array of unique values from B2:F2, which are 10, 20, 30, and 40. However, cell G2 can only hold one value, not four. Therefore, Excel cannot spill the array into adjacent cells and returns an error.

To avoid this error, you need to explicitly tell Excel which value from the array you want to return. You can do this by using the @ (at) symbol before the dynamic formula. The @ symbol tells Excel to perform implicit intersection and return a single value from an array based on the relative position of the cell that contains the formula.

For example, if you enter this formula in cell G2:

=@UNIQUE(B2:F2)

This will return 10 in cell G2. This is because Excel implicitly intersects the array returned by the UNIQUE function with row 2 and returns the first value from the array.

If you copy this formula down to cells G3:G6, Excel will adjust the row references and return different values from the array:

![UNIQUE function with @ symbol]

As you can see, using the @ symbol allows you to use dynamic formulas in cells that expect single values.

What are the advantages of dynamic formulas?

Dynamic formulas have several advantages over traditional CSE array formulas. Some of them are:

They are easier to create and edit. You don’t have to select multiple cells or press any special keys to enter or edit a dynamic formula. You just type it in one cell and press Enter.
They are more efficient and flexible. You don’t have to worry about specifying or adjusting the output range for a dynamic formula. Excel automatically spills and updates the results based on the input data and available space.
They are more readable and understandable. You can easily see which cells contain dynamic formulas by looking at their blue borders and fill handles. You can also see which cells are part of a spill range by looking at their green borders and fill handles.
They are more compatible and consistent. You can use dynamic formulas with other features in Excel, such as tables, charts, conditional formatting, data validation, etc.

You can use dynamic formulas with other features in Excel, such as tables, charts, conditional formatting, data validation, etc. You can also use them with other functions and operators to create more complex calculations.
They are more future-proof and scalable. You don’t have to worry about changing or expanding your data set and breaking your formulas. Dynamic formulas will automatically adjust and spill to accommodate new or modified data.

What are the limitations of dynamic formulas?

Dynamic formulas also have some limitations that you should be aware of. Some of them are:

They are only available in Excel 365 and Excel 2021. If you share your workbook with someone who uses an older version of Excel, they will not be able to see or edit your dynamic formulas. They will only see the values in the spill range, not the formulas.
They may cause errors or conflicts with existing formulas or data. If you enter a dynamic formula in a cell that overlaps with another formula or data, Excel will return an error message: #SPILL!. You need to clear the cells that are blocking the spill range or move the dynamic formula to another location.
They may not work well with some features or functions that expect a single value or a fixed range. For example, if you use a dynamic formula as an argument for the VLOOKUP function, Excel will return an error message: #VALUE!. You need to use the @ symbol to perform implicit intersection or use the XLOOKUP function instead.

Conclusion

Dynamic formulas are a new and powerful feature in Excel that allow you to create formulas that can return multiple values in a single cell. They make it easier to work with complex calculations, such as filtering, sorting, extracting unique values, generating sequences, and more.

To create a dynamic formula, you need to use a function that can return an array of values, such as FILTER, SORT, UNIQUE, etc. To refer to a spilled array, you need to use the # symbol after the cell reference that contains the dynamic formula. To handle implicit intersection, you need to use the @ symbol before the dynamic formula.

Dynamic formulas have several advantages over traditional CSE array formulas, such as ease of use, efficiency, flexibility, readability, compatibility, and scalability. However, they also have some limitations, such as availability, error handling, and compatibility.

How to Use Dynamic Formulas in Excel