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
Category: Formulas
SORT Function in Excel
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
UNIQUE function in Excel
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
FILTER function in Excel
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
RANDARRAY function in Excel
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
SORTBY function in Excel
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 function in Excel
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
Dynamically select any column in an Excel Table
Data comes in all shapes and sizes, and since Excel is not a database, we face those shapes and sizes on a daily basis. One advantage of Tables in Excel is that they force us to apply some standards to our data structure, such as: There can only be one header row The headers must Read More
List unique or duplicate values with Excel formulas
The Excel team recently announced new dynamic array formulas, which can create unique lists, sort and filter with a simple formula. These new formulas are being rolled out to Office 365 subscribers over the next few months. However, not everybody has the subscription and will upgrade when it is sensible for their business, often combining Read More
Spreadsheet Day 2018
Happy Spreadsheet Day! 17th of October is Spreadsheet Day, the day each year when we celebrate… you guessed it… spreadsheets. VisiCalc, the first spreadsheet application, shipped on 17 October 1979. Originally it was only compatible with the Apple II and cost $99. I don’t know if VisiCalc had formulas and functions. Sometimes I wonder what Read More