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
Category: Blog
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 regular RAND function calculates a single random 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
Sorting ListBoxes with VBA
I received a question from a reader recently, who wanted to sort a Listbox using VBA. As this seemed like a common problem which many users could face, I decided to dedicate a bit of time to create a more reusable solution. In this post, we will look at two different types of Listboxes: UserForm Read More
Create your own reusable Excel templates
Whenever we open a new session of Excel, we are presented with a variety of templates to use. There is plenty in there to look at, such as back to school planners, gantt charts and expense calculators, to name just a few. Depending on the version of Excel you’ve got, you may have some other Read More
Using an Excel Table within a Data Validation list
Excel Tables expand automatically whenever new data is added to the bottom. This feature alone makes Tables one of the most powerful tools within the Excel users toolkit. A Table can be used as the source data for a chart and within a named range, both of which benefit from the auto-expand feature. Data Validation Read More