As many regular readers will know, I’m a big fan of writing text dynamically. This means we don’t need to manually update text or headings in our reports. Instead, the text calculates and updates automatically. So, today I want to share how to Find and Replace multiple words in Excel using the REDUCE and SUBSTITUTE … Read more
In a previous post, we looked at the ideal Excel number formats for accounting and finance. In each example, we formatted the cell values. However, there is another way to use these number formats; we can use them inside the TEXT function to generate custom headings and dynamic text. That’s what we are looking at … Read more
A few weeks back, I wrote a post about how to filter by a list in Power Query. It didn’t take long for somebody to ask if the same is possible in Excel. The answer is, YES! So, that’s what we are looking at in this post: how to use Excel’s FILTER function based on … Read more
For a finance professional in industry, nothing is more annoying than writing the period-end or forecast commentary. (OK, maybe the words “last-minute change to provisions” is more annoying, but the commentary is right up there). You’ve created the reports, found the reasons for the variances, and now you need to write it as a text … Read more
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 time. If … Read more
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