Extract multiple matches into separate columns.Dynamic Excel provides a special function, just for this purpose: the FILTER function. if you are using an older version of Excel, there are various ways to approach the problem. However, if you are using an older version of Excel, you still have some options for sorting with a formula:įiltering and extracting data with formulas in Excel has always been a challenging problem. These functions make it easy to sort data by one or more columns, and even sort data by a custom list. Sortingĭynamic Excel provides two new functions to handle sorting with formulas: SORT and SORTBY. If you are working with them in Dynamic Excel, you don't need to use control + shift + enter, Excel will handle the array operations automatically. Note: several of these formulas are traditional array formulas and need to be entered with control + shift + enter. At the same time, these formulas should work in almost any version of Excel. In almost all cases, the formulas are more complex and clunky than an equivalent dynamic array formula.
This section contains alternative formulas that perform some of the same tasks as the functions in the table above. FunctionĮxtract unique values from a list or range
Click a function name for an overview and examples of usage. New dynamic functionsįor reference, the 6 new dynamic array functions are listed in the table below. Remember also that Pivot Tables can be used to solve many of these same challenges. This behavior is limited to the Office 365 version of Excel.
It's important to understand however, that none of the alternatives will spill multiple results onto the worksheet into a spill range like a native dynamic array formula. However, Dynamic Arrays are only available in Office 365, they are not available in Excel 2016 or 2019, and won't work in any older version.įor those not using Office 365, this page provides some alternative formulas that work in older versions of Excel. With 6 brand new functions that directly leverage dynamic arrays, they solve solve hard problems in Excel that have vexed even power users for decades. Dynamic Arrays are one of the biggest changes ever to Excel's formula engine.