Excel
Running Totals in Excel Versus Window Functions in SQL
Replace copied running-total formulas with SQL window functions: sums per group, moving averages, differences, ranks, and the tie trap in the default frame.
7 min read
Tag
analysis
92 posts
Excel
INDEX and MATCH Versus a Join on a Key in SQL Compared
Translate INDEX and MATCH into SQL: one-way lookups, two-way table lookups, left-of-key lookups, match types and the join that replaces a lookup grid.
7 min read
Excel
Pivot Tables Versus GROUP BY and CASE in SQL Compared
Rebuild an Excel pivot table in SQL with GROUP BY, conditional aggregation, ROLLUP and window percentages, and see what changes when columns are fixed.
7 min read
Excel
IF and IFS Versus CASE WHEN in SQL Compared Side by Side
Translate IF, nested IF, IFS and SWITCH into SQL CASE WHEN, with AND and OR, NULL handling, branch types and what each returns when nothing matches.
7 min read
Excel
COUNTIF Versus COUNT With a Filter in SQL Compared
Translate COUNTIF, COUNTIFS, COUNTA and COUNTBLANK into SQL with count star, count of a column and FILTER, and handle distinct counts and blanks correctly.
6 min read
Excel
SUMIF and SUMIFS Versus GROUP BY in SQL Compared in Depth
Translate SUMIF and SUMIFS into SQL with WHERE, GROUP BY and FILTER, including criteria, wildcards, dates, OR conditions and the blank and case traps.
7 min read
Excel
VLOOKUP and XLOOKUP Versus SQL JOIN With Worked Examples
Translate VLOOKUP and XLOOKUP into SQL joins: exact and approximate match, first-match behavior, not-found values, duplicates and which side to keep.
7 min read
ExcelGuide
Translating Excel Formulas Into SQL With Worked Examples
Translate Excel formulas into SQL: lookups, conditional sums, IF, text and date functions, running totals and pivots, with a worked dataset and the traps.
18 min read