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.
- Calculate a Dashboard Completion Rate Without Averaging PercentagesCalculate a task-weighted SQL completion rate by dividing summed completed tasks by summed total tasks, keeping NULL for teams with no tasks.SQL, query, updated
- Sum Only Visible Task Counts After Filtering in ExcelUse Excel's SUBTOTAL function with 109 to sum numeric task counts in visible rows, leaving out filtered and manually hidden rows.Excel, formula, added
- Count Open Tasks by Owner with Google Sheets QUERYCount open tasks by owner with Google Sheets QUERY, leave out blank owners, and check task IDs, duplicates, and source range limits.Google Sheets, formula, added
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.
- Look Up a Project Owner by ID in Excel Without Approximate MatchesUse XLOOKUP for an exact project ID match, return its owner, show a missing-ID message, and troubleshoot common lookup errors.Excel, formula, added
- Find Projects with No Tasks Using a SQL LEFT JOINUse SQL LEFT JOIN and IS NULL to find projects with no tasks, check the sample results, fix null checks, and filter for projects with no active tasks.SQL, query, updated
- Keep the Latest Status Row per Task with SQL ROW_NUMBERRank each task's events by timestamp and a tie-breaker, then use DuckDB's ROW_NUMBER() and QUALIFY to keep its newest status row.SQL, query, updated
- Unpivot a Small Monthly Table in Google Sheets Without Add-onsTurn a small project-by-month grid into project, month, and hours rows with a Google Sheets formula that reflects edits to the source.Google Sheets, formula, added
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.
- Count Workdays and Set Due Dates in Google Sheets with HolidaysCount working days and set due dates in Google Sheets with WORKDAY.INTL, NETWORKDAYS.INTL, a holiday list and the weekend your team actually keeps.Google Sheets, formula, added
- Fill Missing Days with Zero in a SQL Task-Completion ReportUse DuckDB to report task completions for each date in a range, including dates with no matching tasks and a zero count.SQL, query, updated
- Build a Dynamic FX Calculator in Google Sheets with GOOGLEFINANCEBuild a Google Sheets currency calculator that follows dropdowns, pulls live or dated GOOGLEFINANCE rates, and falls back past weekends.Google Sheets, formula, added
- Highlight Overdue Tasks in an Excel Tracker with Conditional FormattingShade whole task rows in Excel when the due date has passed and the status is not Done, and add a helper column that labels Overdue and Due today.Excel, formula, added
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.