Articles

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.

Excel has supported the complex number system for years. Engineers use it to solve problems related to electronics, fluid dynamics, and signal processing. The way Excel's formula engine implements complex numbers is an example of functional programming, a paradigm that Microsoft has invested heavily in recently with the latest updates and functions.

Nearly 50 new functions have been added to Excel! This is not your Dad's Excel anymore – a lot has changed. This article takes a quick tour of the new functions, with links to more detailed information.

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.

In this article, I attempt to explain why you see SUMPRODUCT so often in formulas, and when you can use the SUM function instead. The short version: SUMPRODUCT supports array operations natively, which makes it very useful for solving seemingly unrelated Excel problems. In the current version of Excel, you can use the SUM function instead, but SUMPRODUCT is better for backwards compatibility.

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

In the world of Excel formulas, the term "array formula" is probably responsible for more confusion than just about any other concept. This is because the definition of an array formula has become mixed up with the requirement to enter some array formulas in a special way, with control + shift + enter.

There are eight widely used functions in Excel that use a syntax different from other functions in Excel. This syntax can make these functions more challenging to use, because it is not intuitive. Read on for important information about COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, MINIFS, and MAXIFS.

This article provides examples of public Coronavirus data you can download to Excel with Power Query. Each example has a link, a screenshot to show what the data looks like in Excel after being imported, and an Excel workbook.

A quick example of how to track testing for COVID-19 using Excel and publicly available data. In this project, the data is fetched and "shaped" with Power Query, then dropped back into Excel, where it can be refreshed with a single click.

Dynamic Excel offers 6 brand new functions that solve hard problems in Excel like sorting, filtering, and working with unique values. For those not using Office 365, this page provides some alternative formulas that work in older versions of Excel.

Dynamic Arrays are the biggest change to Excel formulas in years. Maybe the biggest change ever. This is because Dynamic Arrays let you easily work with multiple values at the same time in a formula. This article provides an overview with many links and examples.

Pivot tables are the fastest and easiest way to quickly analyze data in Excel. This article is an introduction to Pivot Tables and their benefits, and a step-by-step guide with sample data.