Category: Dynamic Arrays

Excel is about to change… like seriously, change.  Every time you used Excel in the past, you have accepted a simple operating rule; one formula one cell.  Even with array formulas, it was necessary to have a cell for each formula.  But this is about to change; soon Excel will allow a single formula to Read More

SORT is one of Excel’s new dynamic array functions, which were announced by Microsoft on 24 September 2018.  SORT makes use of changes made to the calculation engine, enabling Excel formulas to return (or “spill”, to use the correct terminology) results into multiple cells. As at the time of writing, Microsoft has only made this Read More

I can’t even begin to count the number of times I have created a unique list in Excel.  I have performed it manually using the remove duplicates from the Ribbon and also using complex formulas, but that is now a thing of the past.  When Microsoft announced changes to Excel’s calculation engine on the 24 Read More

Filtering is a common, everyday action for most Excel users.  Whether using AutoFilter or a Table, it is a convenient way to view a subset of data quickly.  Until 24 September 2018 there was no way to achieve this with formulas.  On that day, Microsoft announced some new functions which make use of the new Read More

On 24 September 2018, Microsoft announced some new functions for Excel.  These new functions make use of changes made to Excel’s calculation engine, which enable a single formula to return (or “Spill” using the correct terminology) results into multiple cells.  RANDARRAY is one of these new functions. The RAND function calculates a single random number Read More

The SORTBY function was announced by Microsoft on 24 September 2018 and is one of Excel’s new dynamic array functions.  SORTBY makes use of the changes made to the calculation engine, enabling Excel to return (or “Spill” to use the correct terminology) the results of a single formula into multiple cells. At the time of Read More

SEQUENCE is one of the new functions which Microsoft announced on 24 September 2018. These new functions make use of changes made to Excel’s calculation engine, which enable a single formula to display (or “spill” if using the new terminology) results in multiple cells.  In the case of SEQUENCE, it will generate a list of Read More