Articles

Ever wonder why some Excel functions stubbornly refuse to spill results across multiple cells, even when you give them a range of data? If you've tried using functions like EOMONTH or EDATE with ranges only to get a frustrating #VALUE! error, you're not alone. This quirky behavior affects dozens of Excel functions, mostly legacy functions from the old Analysis ToolPak, that were designed in a pre-dynamic array world. 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! This one-character addition transforms =EOMONTH(A1:A5,0) from a broken formula into a perfectly functioning spill formula. Some older Excel functions don't spill when you give them a range as input. This article explains why this happens, which functions are affected, and how to fix it. 

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.