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. Columns A and B in the screenshot below (cells A3:B17) contain unsorted sales data by state. Here's a simple example explaining how the new SORT array function works. 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. Microsoft calls this process "spilling" and refers to all Excel formulas producing multiple results as "spilled array" formulas.Įarlier 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. 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. 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). 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.
#HOW TO AUTO SORT IN EXCEL WHEN ADDING UPDATE#
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?Ī.