Excel Formulas

I know you’re probably thinking that everyone knows how to use the SUM function, but I’m willing to bet that most people don’t know all the shortcuts and tricks I’m about to show you. These are SUM function tricks you can use every day, and some also apply to all Excel functions. Watch the Video …

If you’ve worked with dynamic array formulas*, you’ll have noticed that they’re able to spill the results to multiple cells. For example, in the image below we can see the UNIQUE function has spilled the results to cells G3:G6 which are highlighted with a blue border:   With the hash sign in Excel formulas we …

The new Excel LAMBDA function that we looked at last week also enables us to write custom recursive LAMBDA functions. Recursion is simply when a routine calls itself. That said, it can be a bit mind bending, so in this tutorial we’ll look at an example that’s easy to follow. Once you understand the structure …

The new Excel LAMBDA function allows you to define your own custom functions using Excel’s familiar formula language. That means we no longer need JavaScript or VBA programming knowledge to create our own functions. This is a huge step forward and for those familiar with the concept of lambdas it’s worth noting that the LAMBDA …

Navigating workbooks with lots of sheets can be tedious. In this tutorial I’m going to show you how to dynamically list Excel sheet names and add some user-friendly hyperlinks to help users easily navigate the file. It requires an old Excel 4.0 Macro Function called GET.WORKBOOK and this means the file must be saved as …

Using a formula to return a reference to a range of cells allows us to generate a reference on the fly based on the shape of the data or criteria we specify. As our data grows these formula generated references can automatically update to include new data. This has huge efficiency advantages over hard coding …

With the new Excel STOCKHISTORY function we can finally get historical price data for stocks, bonds, funds, crypto and foreign currency exchange rates into Excel, including open, high, low, close and volume. The STOCKHISTORY function is currently only available on the Beta channel (previously Insider Channel) to Microsoft 365 users (previously Office 365). Plus, it …

The Excel INDEX function is a treasure trove of functionality, but most of us only know one way to use it. In this post I want to expose some lesser known quirks and ways it can be used.  If the reference/array is a single row, you can put the col_num in the row_num argument’s position …