Teaching analytics with modern 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 →
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 →
