This is the archive of CPA Now blogs posted on the PICPA website through April 30, 2025. Want more recent blogs?

Read current blogs

By Thomas G. Stephens Jr., CPA, CITP, CGMA

One of the best enhancements Microsoft has added to Excel in years is its new dynamic array feature. With dynamic arrays, we now have the capability of creating more powerful formulas. That is, dynamic arrays free us from the traditional “one-cell, one-formula” paradigm. Instead, a single formula can now place results into many cells. Further, Microsoft added several new functions that return results into these dynamic arrays.

Array Formulas

Array formulas are not new to Excel. With traditional array formulas, we could create a formula – typically one with several functions nested in it – and then use CTRL+SHIFT+ENTER (CSE) to set the formula into a cell. While these types of formulas were useful – and remain so – their complexity is overwhelming for most Excel users. Additionally, Excel offers a few functions – SUMPRODUCT, for example – that operate much like a CSE array formula yet do not require the CSE keyboard sequence. Dynamic arrays do not eliminate legacy arrays; they simply provide another powerful tool to work with in Excel.

The Concept of a Dynamic Array

Before dynamic arrays, we had to enter formulas into every cell where we wanted calculation results to display. Dynamic arrays change that by allowing us to create a single formula and have the results of that single formula appear in as many cells as necessary, given the volume of data under consideration. Consider the example provided in Figure 1. The formula shown in the formula bar was entered only into cell D2, but the formula dynamically copied itself to create a list of the unique values in the range.

Figure 1 - Example of a Dynamic Array

Figure 1 illustrates how dynamic arrays can populate multiple “result” cells, even though the formula itself is entered into a single cell. Importantly, if the source range in the example (B2:B8) had been first converted to a table, then as the volume of data in the table changed, so too would the volume of results in the dynamic array.

Functions that Capitalize on Dynamic Arrays

Microsoft released six new functions that capitalize on the dynamic array model. Each function is listed below, with an emphasis on the FILTER and SORT functions because they are likely to be used most often.

FILTER – The FILTER function is capable of filtering data in a table or a range via a formula. The syntax is relatively simple:

=FILTER(array (table or range), include (a Boolean array for which items to include))

An optional third argument – [if_empty] – specifies the value to display if the filter returns nothing. Figure 2 shows how you can use the FILTER function to filter data without disturbing the original array.

Figure 2 - Using FILTER to Create a Dynamic Array

RANDARRAY – The RANDARRAY function returns an array of random numbers. Here is the syntax for RANDARRAY:

=RANDARRAY([rows],[columns],[min],[max],[whole_number]), where …

• [rows] is an optional argument for the number of rows to be returned.
• [columns] is an optional argument for the number of columns to be returned.
• [min] is an optional argument for the smallest number to be returned.
• [max] is an optional argument for the largest number to be returned.
• [whole_number] is an optional argument for Excel to return TRUE for a whole number and FALSE for a decimal number.

RANDARRAY would be the ideal function to return a row and column of random numbers and serves as a useful alternative to the RAND and RANDBETWEEN functions.

SEQUENCE – You can use the SEQUENCE function to generate a list of sequential numbers displayed in an array. The syntax of the SEQUENCE function is relatively simple:

=SEQUENCE(rows, [columns], [start], [step]), where …

• [rows] is a required entry for the number of rows in the new array.
• [columns] is an optional argument for the number of columns in the new array.
• [start], is an optional argument for the first number in the sequence.
• [step], is an optional argument for the amount to increment each number in the array.

SORT – You can use SORT to sort a table or range of data based on the values in a specific column. The syntax for the SORT function is as follows:

=SORT(array (table or range), Sort_index (column),

Sort_order (1 for ascending, -1 for descending))

An optional fourth argument – [by col] – allows you to choose the sort direction. By default, the sort direction is by row. If you need to sort by column, you can change that to TRUE.

Thus, the formula =SORT(Data,5,-1) sorts the table named Data, based on the values in the fifth column and the sort would be in descending order. This is illustrated in Figure 3.

Figure 3 - Using SORT to Create a Dynamic Array

SORTBY – The new SORTBY function sorts a range or an array based on the values in a corresponding range or array. Here is the syntax for SORTBY:

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…), where …

• [array] is a required argument for the array or range to sort.
• [by_array1..2..3..etc.] is the range or array to sort on.
• [sort_order1..2..3..etc.] is the order to use for sorting, where 1 is ascending (default) and -1 is descending.

UNIQUE – The UNIQUE function identifies and returns a list of all unique values in a list or range. The syntax for the UNIQUE function is as follows:

=UNIQUE(array,[by_col],[exactly_once]), where …

• [array] is a required argument from which to return the unique rows or columns.
• [by_col] is an optional argument indicating how to compare. An entry of TRUE will compare columns against each other and return the unique columns, and an entry of FALSE (or omitted) will compare rows against each other and return the unique rows.
• [exactly_once] is an optional, logical argument that will return rows or columns that occur exactly once in the range or array. TRUE will return all distinct rows or columns that occur exactly once, and FALSE will return all distinct rows or columns from the range or array.

Now the Bad News …

Microsoft started rolling out dynamic array functionality to Office 365 subscribers on version 1912 of Excel or newer. If you are an Office 365 subscriber, when you receive an update that contains this feature depends upon your update channel. Most Office 365 subscribers, however, will receive access to dynamic array functionality in 2020. There do not appear to be any plans for users of Excel on an existing perpetual license – such as Excel 2019 or 2016 – to receive access to dynamic arrays.

Conclusion

Dynamic arrays and related dynamic array functions are among the most powerful features ever added to Excel. With their strength and capability to handle large volumes of data easily, they will no doubt prove to be one of the best additions to Excel in years. If you are an Office 365 subscriber, be on the lookout for dynamic arrays. Unfortunately, if you use a perpetual license of Excel, it is unlikely you will gain access to this feature in your current version.

Here is a video that more thoroughly describes and demonstrates dynamic arrays.

Thomas G. Stephens Jr., CPA, CGMA, CITP, is one of the shareholders in K2 Enterprises, affiliating with the firm in 2003 and joining as a shareholder in 2007. At K2, Stephens focuses on creating and delivering content, and is responsible for many of the firm's management and marketing functions. You may reach him at tommy@k2e.com. Learn more about K2 Enterprises at www.k2e.com.

Sign up for weekly professional and technical updates in PICPA's blogs, podcasts, and discussion board topics by completing this form.