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
Library
Everything you need to level up your coding skills.
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
XML
Converting XML to Excel With Python and Typed Cells
Write an XML file into an xlsx workbook with openpyxl so numbers are numbers, booleans are booleans and identifiers stay text, and read it back to check.
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
ExcelGuide
Reading and Writing Excel Files With openpyxl in Python
A working guide to openpyxl: the workbook model, reading values and formulas, writing and editing files, large-file modes, styles, charts and errors.
19 min read
XMLGuide
Converting XML to JSON CSV YAML and Excel Without Data Loss
One XML file converted to JSON, CSV, YAML and Excel with one parse and four writers, what each target loses, and how to prove nothing went missing.
20 min read
Excel
Why an Exported Excel Date Shows as a Number and the Fix
An exported date shows as 45292 because Excel stores dates as numbers and the cell has no date format. See the cause, the code fix and a file repair.
7 min read
Excel
Making Excel Exports Open Cleanly Every Time on Any PC
Stop repair prompts, garbled accents and wrong columns when exports open in Excel: legal characters, sheet names, time zones, NaN, BOM and separators.
7 min read
Excel
Emailing a Scheduled Excel Report From a Database Job
Build a job that queries a database, writes an Excel file and emails it on a schedule with smtplib and cron, with safe credentials and failure alerts.
8 min read
Get new articles by email
We send one email when a new article is published. No spam.