Excel
Pivoting Rows Into Columns in PostgreSQL With FILTER
Turn rows into columns in PostgreSQL with conditional aggregates and FILTER, or with crosstab from tablefunc, and generate the column list when it changes.
6 min read
Topic
Excel
Where the business keeps its data. Reading workbooks, taming dates and formulas, and moving sheets to and from a database.
Excel
Writing the Same Excel Pivot Table as a GROUP BY Query
Map each part of an Excel pivot table to SQL: rows to GROUP BY, values to aggregates, columns to CASE, filters to WHERE and totals to ROLLUP.
6 min read
Excel
Grouping Dates by Month or Quarter in a Pivot Table
Group dates in a pivot table by month, quarter or year, fix the Cannot group that selection error, handle fiscal quarters, and do the same grouping in SQL.
6 min read
Excel
Making a Pivot Table From Data Larger Than Excel Allows
A sheet holds 1,048,576 rows, but a pivot can read more. Use the Data Model, pre-aggregate in SQL or DuckDB, or sample, and know where each approach stops.
6 min read
Excel
Building a Pivot Table From a Database Query in Excel
Connect Excel to a database, load a SQL query as a connection and build a pivot table on it that refreshes. Covers the Data Model, parameters and traps.
7 min read
ExcelGuide
Pivot Tables and Their SQL Equivalents for Excel Users
For people who know Excel pivot tables and need the SQL behind them: GROUP BY, conditional aggregates, ROLLUP, window functions, unpivoting and checks.
19 min read
Excel
Why SQL Beats Formulas Once the Data Gets Large and Slow
Why spreadsheet formulas slow down as data grows and where SQL pulls ahead: lookup cost, recalculation, memory, repeatability and auditability.
7 min read
Excel
Date Functions in Excel Versus SQL Date Functions Compared
Translate Excel date functions to SQL: YEAR, EOMONTH, EDATE, DATEDIF, NETWORKDAYS, WEEKDAY, WEEKNUM and TEXT, with worked results and week numbering traps.
7 min read
Excel
Text Functions in Excel Versus SQL String Functions
Map Excel text functions to SQL string functions: LEFT, MID, LEN, TRIM, SUBSTITUTE, FIND, TEXTJOIN, PROPER and TEXT, with the edge cases for spaces and nulls.
7 min read
Excel
Removing Duplicates in Excel Versus SELECT DISTINCT
Reproduce Excel's Remove Duplicates in SQL: how DISTINCT and ROW_NUMBER choose the row to keep, how case and spaces matter, and how to delete duplicates.
7 min read
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
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
Get new articles by email
We send one email when a new article is published. No spam.