Teaching analytics with modern Excel
Dynamic array functions represent one of the most exciting new features in Excel. Although generally as straightforward in syntax to use as other Excel functions, their underlying mechanism for …
Continue Reading about How to understand the # spill operator in Excel →
Excel tables offer several benefits for data storage and manipulation, one of the most significant being the ability to use structured references to programmatically select different elements of a …
Continue Reading about How to select two noncontiguous columns in an Excel table →
UNIQUE() function in ExcelThe UNIQUE() function in Excel offers a quick and versatile method to eliminate duplicates from a range or table, though it occasionally produces unexpected results. Consider the following example: I …
Continue Reading about How to remove 0’s from the results of the UNIQUE() function in Excel →
I asked ChatGPT: if the 7 wonders of the modern world were modern Excel features, which would they be and why? Here's what I got back: 1. Great Wall of China - Power Query The Great Wall is …
Continue Reading about What are the seven wonders of Modern Excel? →
XLOOKUP()I've often called VLOOKUP() the "duct tape of Excel" for its ability to combine data from two sources. It's a phrase I even included in my book Advancing into Analytics, and I had a graphic …
Continue Reading about How to write modern lookup functions in Excel with XLOOKUP() →
Traditionally, filtering a dataset by multiple AND/OR conditions involved use of the Advanced Filters feature. While menu-driven, Advanced Filters can be difficult to manage and applies filters …
Continue Reading about How to filter a dataset by one or more criteria using Excel formulas →
