Excel Formulas

Get ready for an exciting update - Microsoft have released new features in the beta version of Excel* with the TRIMRANGE function and trim reference dot operator. They revolutionize the way you reference cells in formulas. Forget about those long formulas involving OFFSET or INDEX to create dynamic ranges — TRIMRANGE makes managing dynamic data …

Various studies report that nearly 9/10 spreadsheets contain errors, with the majority a result of human error relating to formulas. Spreadsheet errors have cost companies millions, and they can put your reputation and career on the line. In this post, I'll guide you through a simple yet powerful Excel formula checklist to help you avoid …

Ever heard of the Excel FILES function? If you haven't, you're not alone. This little-known function can list all file names in a folder and has been part of Excel for many versions. However, it's hidden as a legacy Excel 4 Macro function. In this post I'll reveal how to use the FILES function and …

Parsing text in Excel with functions like FIND, MID, and SUBSTITUTE can be cumbersome and inefficient, especially for complex tasks. These limitations make advanced text manipulation time-consuming and error-prone, not to mention frustrating. The new Excel REGEX functions* revolutionize text manipulation. In this blog post, we'll delve into these new functions, providing practical examples to …

These days, knowing basic Excel won't set you apart. You have to keep up with new features that enable you to improve productivity, or you'll be left behind. There are 10 must know modern Excel functions that simplify common tasks. These functions will not only make your life easier, they'll also make you the go-to …

The GROUPBY and PIVOTBY functions are a significant breakthrough in Excel's toolbox of functions. They let you easily group or aggregate data, a concept that's been around in Excel since the days of PivotTables in 1993 and Power Query since 2010. But what's exciting about GROUPBY and PIVOTBY is that they simplify this process down …

With AI gradually making its way into every aspect of our lives, working in Excel has become much easier. You no longer need to research the web and copy over the data into Excel. Simply prompt the AI in natural language, and it will spill out the results for you within Excel! All thanks to …

The SUMPRODUCT function is one of Excel's most versatile functions. But now that we have SUMIFS, COUNTIFS, AVERAGEIFS etc. it’s easy to think this function is, dare I say, redundant! However, SUMPRODUCT can do things you can’t do with those functions. Table of Contents SUMPRODUCT Function Video SUMPRODUCT Example File Download Introduction to the SUMPRODUCT …

Back in my accounting days I regularly prepared reports that summarized monthly data into quarters. Back then, we used the SUM function with Boolean (TRUE/FALSE) logic, which worked fine, but, there are better ways to do it now that we have dynamic arrays. In this tutorial I’ll cover both options and you can choose the …

Excel BYCOL and BYROW functions fundamentally change the way we write formulas that calculate across columns and down rows. They enable us to write a formula once and have it automatically applied to all rows or columns in the range we specify, improving productivity and accuracy. These functions are currently available to Microsoft 365 users. …

You can now use Python in Excel natively! Python runs securely in the cloud, and we write Python in Excel like a formula. You can load Python libraries to Excel including Pandas, NumPy, Seaborn, Matplotlib and more. No need to install any add-ins and no clunky separate windows for writing the code. Note: Python in …

Excel functions are the backbone of a financial modeler’s day to day work and as such, it’s essential to have a good understanding of the functions that are commonly used to build financial models. Below are the top Excel functions that you must know as a financial modeler grouped by area. Are there any functions …

Excel can now write formulas for you. Simply give it an example or two and it’ll suggest a formula to complete the task. Remember how amazing Flash Fill was when you saw it for the first time? Well, Formula by Example is 10x better because it isn’t a one time and done solution. Being a …

Writing Excel formulas is one of the most important tasks to master in Excel, but it can be a mine field trying to get your head around all the functions available and knowing which one to use for the task at hand. But now with this free AI-aided formula editor you can have it write …

A data analyst gathers, cleans, models, analyses, and interprets data. The gathering and cleaning should be done with Power Query. The modelling and analysis can be done with a combination of PivotTables/Power Pivot and functions. The other day I stumbled upon a post claiming to list the top Excel functions for data analysts. I disagreed …

The Advanced Formula Environment (AFE) is part of the Excel Labs add-in and is still in its early stages of development, but it’s already very useful, particularly if you’re scared to write your own LAMBDA functions, it can write them for you, as you’ll see! You may have seen me use it a few times …

Excel Formula Writing Tips, Tricks and Tools that will change how you work in Excel Formulas are THE MOST IMPORTANT EXCEL SKILL you can master, but when you start nesting functions, things can quickly get out of hand. In this lesson I’m going to cover some little known Excel formula writing tips, tricks and tools …

The new Excel IMAGE function enables you to insert images into cells with a formula. From there you can resize them, sort, filter and more. The IMAGE function supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP file types. The IMAGE function is currently in the beta testing phase so the function arguments may change …

Excel VSTACK and HSTACK functions are just two of a raft of new text manipulation functions available to Microsoft 365 users. I wrote about text splitting functions TEXTSPLIT, TEXTBEFORE and TEXTAFTER a while back. The new VSTACK and HSTACK functions work to combine arrays arranged vertically (VSTACK) or horizontally (HSTACK) into a new single array. …

I often get asked how to identify overlapping dates and times in Excel. Excel doesn’t have an ‘overlap’ function, but with SUMPRODUCT we can identify dates or times that fall within the same range. For example, let’s say you have list of appointments scheduled for the month. If you plot them visually in a calendar …

It’s been a long wait, but we finally have some exciting new Excel text functions that are going to make life so much easier. In this tutorial I’m going to focus on TEXTAFTER, TEXTBEFORE and most exciting, TEXTSPLIT. The first two functions are fairly self-explanatory, and so is TEXTSPLIT to a degree. However, in this …

Most Excel users know the basic functions like SUM, COUNT, AVERAGE etc. Therefore, in this tutorial I’m going to cover the 10 most important intermediate level Excel functions every Excel user should know. Watch the video for an introduction to each function, and then dive deeper by following the links below. Note: there’s technically 17 …

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 …