SUMPRODUCT: The Excel Formula That Does (Almost) Everything
SUMPRODUCT multiplies arrays and adds the result — which makes it a multi-condition counter, a weighted average, a case-sensitive lookup and more, in every…
Read article →Category Archives: Excel
Excel formulas, unusual formula combinations, VBA, Power Query and dashboards — from basics to clever tricks.
Build a Dynamic Excel Chart That Changes with a Dropdown
Build a Dynamic Excel Chart That Changes with a Dropdown
Create one chart that switches between regions, products or years from a dropdown list — using Data Validation, XLOOKUP or INDEX-MATCH, and a small…
Read article →Protect Excel Sheets and Workbooks the Right Way
Protect Excel Sheets and Workbooks the Right Way
Lock formulas while letting people type in input cells, protect the workbook structure, and understand what Excel protection does — and does not —…
Read article →Run-time Error 1004 in Excel VBA: 7 Common Causes and How to Fix Them
Run-time Error 1004 in Excel VBA: 7 Common Causes and How to Fix Them
Run-time error 1004 is Excel VBA’s catch-all error. Here are the 7 causes I see most often — with the exact fix for each.
Read article →VBA Plus / Macros Chapter – 1 (Swap Values – 1)
VBA Plus / Macros Chapter – 1 (Swap Values – 1)
This example teaches you how to swap two values in Excel VBA. You will often need this structure in more complicated programs as we will see later.…
Read article →XLOOKUP vs VLOOKUP vs INDEX-MATCH: Which One Should You Use?
XLOOKUP vs VLOOKUP vs INDEX-MATCH: Which One Should You Use?
A practical comparison of VLOOKUP, XLOOKUP and INDEX-MATCH in Excel — when each one works, when it breaks, and which to use in 2025.
Read article →Unpivot Monthly Columns into a Clean Table with Power Query
Unpivot Monthly Columns into a Clean Table with Power Query
Turn a wide report with Jan–Dec columns into a tidy Month/Value table using Power Query’s Unpivot — so pivot tables, charts and SUMIFS finally…
Read article →XLOOKUP with Two (or More) Criteria — Plus the INDEX-MATCH Version
XLOOKUP with Two (or More) Criteria — Plus the INDEX-MATCH Version
Look up a value using two conditions — product AND region, or employee AND month — with XLOOKUP, INDEX-MATCH, or a helper column. With…
Read article →Using AI to Write Excel Formulas — and How to Check Its Work
Using AI to Write Excel Formulas — and How to Check Its Work
AI assistants can draft Excel formulas and VBA in seconds, but they also make confident mistakes. How to prompt them well and a 5-step…
Read article →Basics of VBA / Macros Chapter – 90 (Userform Part-11)
Basics of VBA / Macros Chapter – 90 (Userform Part-11)
Interactive Userform Below we will look at a program in Excel VBA that creates an interactive Userform. The Userform we are going to create looks as follows:…
Read article →Basics of VBA / Macros Chapter – 89 (Userform Part-10)
Basics of VBA / Macros Chapter – 89 (Userform Part-10)
Userform with Multiple Pages Below we will look at a program in Excel VBA which creates a Userform that contains multiple pages. This userform…
Read article →Basics of VBA / Macros Chapter – 88 (Userform Part-9)
Basics of VBA / Macros Chapter – 88 (Userform Part-9)
Controls Collection When creating Userforms in Excel VBA, you can use the Controls collection to easily loop through controls and set a property of each control to…
Read article →