Teaching analytics with 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 →
A common practice in machine learning and predictive analytics is to split a dataset into training and testing subsets. The training subset is used to build the actual model, while the testing is used …
Continue Reading about How to create training and testing data samples in Excel →
Many of my favorite new Excel features like FILTER(), UNIQUE() and SEQUENCE() are so-called "dynamic array" functions. But other than being really great, what does it mean to be a dynamic array …
Continue Reading about How to understand dynamic arrays in Excel →
In the past, getting a count of distinct items in Excel using formulas only was quite cumbersome. With the help of dynamic arrays, we can get it done quickly. Let's look at an example using a list of …
Continue Reading about How to get a count of distinct items in Excel →
SORT() and SORTBY() in Excel?Among the dynamic array functions of Excel are two that sound awfully familiar -- SORT() and SORTBY(). Their titles make it pretty clear what they do... they sort data. But how do they do it …
Continue Reading about What is the difference between SORT() and SORTBY() in Excel? →