Guides / Topic

Spreadsheets & Data

The formulas and queries behind everyday reports, and the quiet mistakes that give a believable number that is wrong.

11 guides. Last updated . What's new

What spreadsheets & data covers

Every small company has reports: open tasks by owner, completion rates, due dates, revenue in two currencies. They are built in Excel, Google Sheets or a SQL query against the database behind an app, usually by someone who learned the tool on the job.

The dangerous mistakes are not typos. They are logic errors that produce a plausible answer: an average of percentages, a lookup that returns the nearest match instead of the exact one, a total that includes rows hidden by a filter, a chart that skips days with no activity. Nobody questions the number because it looks fine.

Where it usually goes wrong

Averaging percentages
Two projects at 100% and 0% average to 50%, even if one has one task and the other has nine. The real rate comes from summing the parts first.
Approximate lookups
A lookup left in its default mode can return a neighbouring row's value with no error at all.
Totals that ignore filters
SUM keeps counting rows you have filtered out of view. SUBTOTAL does not.
Missing days and stale rows
A daily report with no row for quiet days draws a misleading line, and a status table with history counts each task more than once unless you keep only the latest row.

How these guides help

Each guide takes one of these traps and gives you the formula or query that avoids it, with a small sample table where you can see the wrong answer and the right one side by side. Some have a live sheet on the page: change the numbers and watch the result move.

Every guide ends with a note that says exactly how it was checked. The SQL queries were run against their sample data in DuckDB, MySQL and PostgreSQL. Some formulas were evaluated in HyperFormula, an independent spreadsheet engine, and others were worked through by hand; where a formula has not been run in Excel or Google Sheets itself, the note says so.

The 11 guides

Totals and rates that add up

Counts and percentages that hold when the groups are different sizes or the sheet is filtered.

Lookups, joins and reshaping

Match records exactly, find what is missing, keep the newest row, and turn a wide table into one you can pivot.

Dates, gaps and currencies

Working days with your own weekend and holidays, reports with no missing days, and exchange rates that follow the date in the row.

Questions

Excel or Google Sheets?

Each guide names the product it was written for. Many functions work in both, but not all of them, and some behave differently, so check the title and the list of what the guide needs before you copy a formula across.

Which SQL database do the queries run on?

Every SQL guide is run on its sample data in DuckDB, MySQL 8.4 and PostgreSQL 17 each time the site is rebuilt. Most queries are standard SQL and run unchanged on all three. Where a database needs different syntax, such as a date series in MySQL or QUALIFY in DuckDB, the guide shows each version.

Why not use an add-on?

An add-on is one more thing with access to your data and one more bill. Most of these jobs take one formula or one query, and once you understand it you can adapt it to the next report.

Esc