20 tutorials, newest first.
Numbers stored as text break SUM, lookups and sorting. Spot them in seconds, then convert with Convert to Number, Paste Special, Text to Columns, VALUE/NUMBERVALUE or Power Query, plus the hard cases.
TRIM leaves spaces behind? Find out exactly which character it is with one formula, then remove non-breaking spaces (CHAR 160), line breaks, tabs and zero-width characters in Excel or Google Sheets.
ARRAYFORMULA, MAP/LAMBDA or fill down in Google Sheets? A decision table and side-by-side formulas for row math, AND/OR per row, row totals, running totals and per-row SUMIFS.
COUNTIF, COUNTIFS or SUMPRODUCT? A decision table plus tested examples for AND, OR, case-sensitive, row-by-row and month counts, and the edge cases that give wrong totals.
FILTER or QUERY in Google Sheets? A decision table, 9 side-by-side formulas, and the traps (mixed data types, case, column letters, empty results) that decide which one to use.
Calculate hours worked in Excel when a shift crosses midnight. Copy the MOD formula, subtract breaks, total over 24 hours, split night hours and overtime. Free timesheet.
Calculate age from a date of birth in Google Sheets with DATEDIF: whole years, years-months-days, age on a set date, next birthday, plus leap-day and month-end edge cases.
Excel dates left-aligned, not sorting, or giving #VALUE!? Detect dates stored as text and convert them with DATEVALUE, Text to Columns, Paste Special or DATE+MID.
SUMIFS returns 0 when you give one column two criteria. Sum North OR West with an array constant, a criteria list, FILTER or SUMPRODUCT, without double counting.
Getting #SPILL! "Spill range in table" in an Excel table? Fix the missing @ first, then return one value per row, move the formula outside, or convert to a range.
Your time total resets after 24 hours? Format the SUM as a duration ([h]:mm) in Google Sheets, convert to decimal hours, handle overnight shifts and text times.
Build a dependent drop-down in Google Sheets that works on every row and keeps working when you insert rows or add items. One helper formula, no named ranges.
Pull email addresses out of messy text in Google Sheets with REGEXEXTRACT. Covers whole columns, several emails in one cell, names, domains and validation.
Highlight values that appear in both columns in Google Sheets with a COUNTIF custom formula. Covers one column, both columns, same row, missing values, case and spaces.
Count checked checkboxes in Google Sheets with COUNTIF, count per row or column with BYROW/BYCOL, count by owner or date with COUNTIFS, and show a % progress bar.
Fix the Google Sheets QUERY error "Unable to parse query string for Function QUERY parameter 2": quotes, cell references, dates, clause order, GROUP BY, Col1 and IMPORTRANGE.
VLOOKUP returning the wrong value in Excel or Google Sheets? Fix the missing FALSE argument first, then check duplicates, column numbers, relative ranges and wildcards.
Why Google Sheets shows #REF! "Array result was not expanded because it would overwrite data", how to find the blocking cell, and 6 fixes with copy-ready formulas.
Why IMPORTRANGE shows #REF! "You need to connect these sheets" in Google Sheets, how to click Allow access, and what to do when the button never appears.
XLOOKUP shows #N/A even though the value is there? Find the cause fast: spaces, text numbers, ranges, match_mode and more, with copy-paste fixes.