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
Tag
spreadsheets
45 posts
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
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
Excel
Dynamic Array Functions FILTER SORT and UNIQUE Explained
Learn the Excel dynamic array functions FILTER, SORT, SORTBY and UNIQUE: syntax, spill behavior, combining them, errors and what scripts see.
6 min read
Excel
XLOOKUP Examples That Replace VLOOKUP Pain for Good
Six XLOOKUP examples that fix VLOOKUP problems: lookups to the left, missing keys, two-way lookups, multiple columns, wildcards and nearest matches.
6 min read
ExcelGuide
Excel Formulas Data People Use Every Week Explained
The Excel formulas data people use every week: lookups, conditional sums, text and date cleanup, arrays, errors and LET, with their SQL equivalents.
19 min read
Excel
Differences Between Sheets and Excel Formulas to Know
How Google Sheets and Excel formulas differ: array handling, lookup and text functions, dates, locale, errors and what breaks when a file moves between them.
7 min read
Excel
Sheets IMPORTDATA and Its Limits for Live Data Feeds
What Sheets IMPORTDATA can and cannot do as a live feed from a CSV URL: format, refresh, auth, size, parsing quirks and a way to measure it for your data.
7 min read
Excel
Publishing Database Results to a Sheet Automatically
Publish database results to a Google Sheet on a schedule: replace a range safely, mark it generated, stamp the update time and alert on failure.
8 min read
Excel
Query Function in Sheets Versus SQL and What Maps to What
Compare the Google Sheets QUERY function with SQL: select, where, group by, order by, label and limit side by side, what QUERY lacks, and when to use which.
7 min read