Excel
Remove Duplicates Keeping the Latest Row Both Ways
Remove duplicate rows but keep the latest per key, in Excel with sort and Remove Duplicates, XLOOKUP or SORTBY, and in SQL with DISTINCT ON or ROW_NUMBER.
7 min read
Library
Everything you need to level up your coding skills.
Excel
Rank Customers by Spend in Excel and SQL With Ties Handled
Rank customers by spend with RANK.EQ and sorted lists in Excel and with RANK, DENSE_RANK and ROW_NUMBER in SQL, including ties, top N and ranks per group.
7 min read
Excel
Running Total of Monthly Sales Both Ways in Excel and SQL
Build a monthly running total in Excel with expanding ranges, SUMIFS and SCAN, and in SQL with a window function, including ties and resets.
7 min read
Excel
Joining Two Sheets on Two Keys in Excel and in SQL
Join two sheets on two keys in Excel with XLOOKUP, SUMIFS or Power Query, and in SQL with a two-column join, handling duplicates, NULLs and missing matches.
6 min read
Excel
Finding the Second Highest Value in Excel and in SQL
Find the second highest value in Excel with LARGE and MAXIFS and in SQL with OFFSET, DENSE_RANK and subqueries, and decide how ties and NULLs should count.
6 min read
ExcelGuide
Worked Examples Moving Between Excel and SQL Side by Side
Worked examples of one task in Excel and SQL: ranking, two-key joins, running totals, deduplication, distinct counts and percentiles, with the differences.
17 min read
CSV
Opening a CSV File That Is Too Large for Excel or Sheets
A worksheet holds 1,048,576 rows. When a CSV has more, filter, aggregate or split it first with DuckDB, split or csvkit, and hand Excel a file that fits.
6 min read
Excel
Array Formulas and Why They Break Imports to Databases
How Excel array formulas and spilled ranges are stored in xlsx files, why loaders see blanks, extra cells or errors, and how to import them correctly.
7 min read
Excel
Date Formulas for Month Ends and Business Days in Excel
Excel date formulas for month starts and ends, adding months, business days with holidays, fiscal years and age, with the edge cases that break them.
7 min read
Excel
LET and LAMBDA for Readable Formulas in Excel Workbooks
Use LET to name values and LAMBDA to define reusable functions so long Excel formulas become readable, faster and easier to review, with portability notes.
6 min read
CSV
Converting Excel to CSV Without Losing Leading Zeros
pandas turned a text cell 02134 into 2134 and in2csv did too; qsv kept it. A number formatted 00000 loses its zeros everywhere unless you rebuild them.
10 min read
Excel
TEXTSPLIT TEXTJOIN and Text Cleanup Formulas That Work
Use TEXTSPLIT, TEXTJOIN, TEXTBEFORE and TEXTAFTER with TRIM, CLEAN and SUBSTITUTE to clean text in Excel, with fallbacks for older versions.
6 min read
Get new articles by email
We send one email when a new article is published. No spam.