Articles

Arrays are now a core part of how Excel formulas work. This article explains what arrays are, how array formulas work, and how dynamic arrays have changed the game. Includes practical examples, tips for inspecting arrays, a guide to the new array functions in Excel, and the workbook.

Excel's DATEDIF function is widely used for calculating the difference between two dates in years, months, or days, but it will silently return buggy results in specific cases. DATEDIF2 is a custom LAMBDA function that replaces DATEDIF with reliable date arithmetic. This article explains the problems with DATEDIF, walks through the DATEDIF2 formula step by step, and shows where the two functions differ.

How to keep random numbers from changing in Excel? Excel's random functions like RAND and RANDARRAY recalculate every time you edit your worksheet, making it impossible to maintain stable random results. This article shows you how to build a seeded random number generator using Excel's LAMBDA function, allowing you to generate reproducible random numbers that only change when you want them to. Perfect for scenarios like randomly assigning students to groups or any situation where you need consistent, repeatable random results.

The 4% retirement rule suggests you can safely withdraw 4% of your portfolio in year one of retirement, then adjust that amount for inflation each year for 30 years without running out of money. But how does this actually play out year by year? This article builds a complete Excel model to find out, exploring three different approaches, from classic row-by-row formulas to modern dynamic arrays to a single formula that generates the entire 30-year schedule. These models are fully functional, so you can play with the inputs and see how things work yourself.

If you've ever tried to create a to-do list, survey, or any kind of checklist in Excel, you've probably wished for a simple checkbox feature. Well, your wish has finally come true. Microsoft has finally introduced a native checkbox feature in Excel. It's easy to use and a great way to make your spreadsheets more interactive.

Ever wonder why some Excel functions stubbornly refuse to spill results into multiple cells, even when you give them a range of data? For example, if you give some functions like EOMONTH or EDATE a range of dates, you'll get back a #VALUE! error. This quirky behavior affects dozens of Excel functions, mostly legacy functions from the old Analysis ToolPak, that were developed decades before dynamic array formulas were introduced. The good news? There's an incredibly simple fix that most Excel users don't know about: just add a plus sign (+) before your range reference. That's it!  Read below for a full explanation, examples of how to fix the problem, and a list of functions that are affected.

Have you ever had Excel tell you two numbers are different when they look the same? This can be quite unsettling. You keep checking and re-checking numbers that look identical, and yet Excel insists they are different. Is Excel broken? Do you need glasses? Don't worry, it's not your fault. You may have run into a floating-point error. There are reasons why this can happen, and simple ways to fix the problem. 

After decades of waiting, Excel finally supports Regular Expressions, aka regex! Learn how three powerful new functions - REGEXTEST, REGEXREPLACE, and REGEXEXTRACT - can transform complex text operations into elegant, maintainable formulas. Whether you're cleaning data, validating inputs, or extracting patterns, these new tools will change how you write advanced formulas in Excel.

For many years, INDEX and MATCH have been the go-to solution for difficult lookup problems in Excel. While more complicated to configure, the two-function combination of INDEX + MATCH is flexible and powerful. But now that XLOOKUP is more widely available, will INDEX and MATCH remain popular? Is there any reason to still use INDEX and MATCH? Read below for a detailed comparison. See INDEX and MATCH Two-Column Lookup Example

For many years, VLOOKUP has reigned supreme as the most widely used lookup function in Excel. But now that XLOOKUP is more widely available, VLOOKUP's reign will likely come to an end. XLOOKUP is a modern replacement for the VLOOKUP function and is more capable in almost every way. Let's look at how these two functions stack up against each other.

This article explains how to concatenate manually with the ampersand operator (&) and with the three Excel functions designed for concatenation: CONCATENATE, CONCAT, and TEXTJOIN.