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
Category: Formulas
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
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
