Microsoft Excel: A dynamic new way to SORT data arrays

Q. Is it possible to sort a column in Excel using formulas rather than the Data tab’s Sort tool, so the sort process is performed automatically as I update my data?

A. Excel has announced a new array-based function called SORT, which can be used to sort data in one or more columns without involving a manual sort process or a complicated macro process.

SORT is one of several new functions that take advantage of Excel’s new Dynamic Arrays functionality, which was recently released as a beta feature to some Office Insiders (see the February 2019 Tech Q&A topic “Microsoft Excel: Upgrade to the Latest Excel Features” for information about becoming an Office Insider). As an enhancement to Excel’s calculation engine, the Dynamic Arrays functionality enables a single formula to produce results that expand into other cells, as demonstrated in the SORT example below. Microsoft calls this process “spilling” and refers to all Excel formulas producing multiple results as “spilled array” formulas.

Earlier Excel editions provide similar array functionality but require the user to select the entire output range and then enter the array formula by pressing Ctrl+Shift+Enter. The new Dynamic Arrays functionality is easier because the user need only press the Enter key, the output range need not be selected, and changes to the source data are automatically reflected without having to re-press the Ctrl+Alt+Enter key combination each time the source data change.

Here’s a simple example explaining how the new SORT array function works. Columns A and B in the screenshot below (cells A3:B17) contain unsorted sales data by state. To sort these data using a formula rather than the Data tab’s Sort tool, I position my cursor in cell D3 and enter the formula =SORT(A3:B17) to produce the sorted array results, shown below in cells D3:E17. The advantage of this approach is that as the data in columns A and B change in the future, the results in columns D and E will be updated automatically.

The formula =SORT(A3:B17) uses the default “sort by” and “sort order” settings; thus, the list is sorted in alphabetical order. The syntax for the new SORT function is =SORT(array, [sort_index], [sort_order], [by_column]). The first argument identifies the array to be sorted. All the other arguments are optional. The second argument determines which column the array will be sorted by. The default for [sort_index] is 1, which is why the SORT formula in the example above results in the array being sorted by the first column. The third argument determines whether the sort is ascending (enter a 1) or descending (enter a -1). The default is ascending. So if you wanted to sort by “Sales” to produce a list of sales by state ranked from highest to lowest, you would want to sort by the second column in ascending order, resulting in the formula =SORT(A3:B17, 2,-1).

The fourth argument, [by_column], determines whether the formula sorts rows of data or columns of data. As it’s far more common to sort rows of data, as in the example at left, the default on the fourth argument is FALSE.

Here are a few more comments about Dynamic Arrays functions:

1. Coming soon to Office 365: Dynamic Arrays functions are expected to be released to all Office Insiders and Office 365 subscribers this year, perhaps even before this issue of the JofA hits mailboxes. This feature has not been included in the purchase edition of Excel 2019, and it is not expected to be.

2. Anticipating spillover results: When you enter a dynamic array formula, Excel will dynamically spill the results downward and to the right as necessary. Therefore, you should ensure that there are enough blank cells located downward and to the right to accommodate the results. If text or data are already residing in the spilled array output range, Excel will return a #SPILL! error indicating the tool has been blocked.

3. Incorporating Tables: When using the SORT array formula, you may find it beneficial to first convert the source data into an Excel Table (Using the Insert tab’s Table menu option), an example of which is pictured below. Notice how the array formula in cell D26 references Table2, which is pictured to the left in columns A and B. The advantage of this approach is that it enables the SORT results to update automatically when rows or columns are added to the bottom of Table2.

(Note: Array formulas won’t work in Excel Table cells; all array-based formulas must be positioned in the Excel worksheet cell away from any Excel Tables.)

4. Array behavior: Once you create an array formula and then select a cell (or cells) within the spilled array area, Excel will highlight the spilled array range with a border, as pictured around cells D26:E31 in the screenshot above. The border disappears when you select a cell outside of the spill area. Additionally, only the first cell in the spilled array area can be edited. When you select another cell in the spilled array area, that formula becomes visible in the formula bar as gray text (referred to as ghosted text), but it can’t be changed. To update the formula, you must edit the top-left-most cell in the array range (cell D26). Then Excel will automatically update all spilled array results when you press the Enter key, and the spilled array area will resize as necessary.

Read “More Dynamic Arrays functions” to discover more about other Dynamic Arrays functions that are being added to Excel.

About the author

J. Carlton Collins, CPA, (carlton@asaresearch.com) is a technology consultant, a conference presenter, and a JofA contributing editor.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.