Office 365 subscribers now have Excel’s new calculation engine available to them, meaning lots of users suddenly have access to dynamic arrays. This got me thinking about whether VBA user-defined functions (UDFs) can make use of the new functionality too. I’m not an expert in this; I’m just sharing as much as I’ve learned to Read More
Category: Formulas
Top 10 with formulas in Excel
A top 10 list is a common form for displaying information, especially on dashboards and summarized reports. It is easy to create the list when working with sorted data, just cell link to the top 10 items in the list… easy! It’s also relatively simple when using Auto Filter, Tables and Pivot Tables, as it Read More
3 ways to change or insert images based cell values
From stock parts to holiday villas, from employees to logos, there are plenty of reasons to insert and automatically change an image based on a cell. You may think of this as a lookup function that returns a picture, rather than a value. Ultimately, the purpose is to link an image or picture to a Read More
INDEX MATCH MATCH in Excel: 2 dimension lookup
In this post, I want to cover one of the most powerful lookup functions available in Excel, INDEX MATCH MATCH. Actually, to call it a function is poor terminology, as it’s three functions used together within a formula. It allows us to return a result based on a lookup from rows and columns at the same Read More
Dynamic arrays in Excel
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 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 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
