In the final part of this series, we look at a few advanced dynamic array formula techniques. We won’t be covering the individual functions in detail but considering how we can combine them to solve some tricky problems. Many of these techniques have been covered briefly as examples in previous posts, but now we’ll dig deeper. … Read more
So far, in this series, we’ve looked at how dynamic arrays work and the new functions that are available to us. Most of our examples have focused on calculations that occur on the worksheet. But we’ve not yet considered how dynamic arrays work with other Excel features, such as charts, data validation, conditional formatting, etc. So, we’re … Read more
The RANDARRAY function makes use of the changes made to Excel’s calculation engine. These changes enable a single formula to spill results into multiple cells. The regular RAND function calculates a single random number, which is greater than or equal to zero and less than 1. The RANDARRAY function calculates a random number in the same way, … Read more
The SEQUENCE function is one of the new dynamic array functions that Microsoft released as part of introducing the dynamic arrays. This function makes use of changes to Excel’s calculation engine, which enables a single formula to display (or “spill” if using the new terminology) results in multiple cells. In the case of SEQUENCE, it … 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 the FILTER function in Excel was released, there was no easy way to achieve this with formulas. When Microsoft announced the changes to Excel’s calculation engine, they also … Read more
The SORTBY function was announced by Microsoft in September 2018 and is one of Excel’s new dynamic array functions. SORTBY makes use of the changes made to the calculation engine, enabling a single formula to spill calculations into multiple cells. At the time of writing, the SORTBY function is only available in Excel 365, Excel … Read more
The SORT function in Excel, is one of Excel’s best new features. It’s one of a group of functions that make use of Excel’s new dynamic array calculation engine, enabling Excel to spill results into multiple cells from a single formula. At the time of writing, the SORT function is only available for Microsoft 365, Excel … 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 remove duplicates from the ribbon, using PivotTables and with complex formulas, but that is now a thing of the past. When Microsoft announced changes to Excel’s calculation engine in September 2018, they also … Read more
Excel has changed… like seriously, changed. Every time we used Excel in the past, we accepted a simple operating rule; one formula one cell. Even with advanced formulas, it was still necessary to have a cell for each. But this has changed; Excel now allows a single formula to fill multiple cells. This is possible because Microsoft has changed … Read more
I shared how to get data from OneDrive or SharePoint into Power Query in a previous post. Many people noted that the method shown in that post works with OneDrive for Business but doesn’t work with OneDrive Personal. So, in this post, I will demonstrate the technique needed to connect Power Query to files in … Read more