Formula guides you can edit.
Short, practical explanations of the formulas people actually use at work. Every example is a live sheet on the page: change the formula and watch the cells recalculate. They work the same in Excel and Google Sheets unless a guide says otherwise.
Basics
- BasicsAbsolute and relative referencesWhat $A$1, A$1 and $A1 mean, why formulas break when you fill them down, and how to fix them. Live examples for Excel and Google Sheets.4 sections · live examples →
- BasicsSpreadsheet formula basicsHow to write your first formulas in Excel or Google Sheets: the = sign, cell references, + - * / ^, the order of operations and brackets. Live examples.3 sections · live examples →
- BasicsSUM, AVERAGE, COUNT, MIN and MAXThe five functions behind most reports: SUM, AVERAGE, COUNT vs COUNTA, MIN and MAX, with ranges and the rules for text and blanks. Live, editable examples.3 sections · live examples →
Lookups
- LookupsVLOOKUP vs XLOOKUPHow VLOOKUP and XLOOKUP differ, when each one breaks, and which to use in Excel and Google Sheets. Live examples you can edit in your browser.5 sections · live examples →
- LookupsINDEX MATCH, explainedHow INDEX and MATCH work alone and together, how to look up to the left, and how to do two-way lookups. Live, editable examples for Excel and Google Sheets.4 sections · live examples →
- LookupsTwo-way lookups and multiple criteriaLook up a value by row and column (INDEX MATCH MATCH, nested XLOOKUP), by several conditions at once, and by brackets with approximate match. Live examples.3 sections · live examples →
Totals and reports
- Totals and reportsSUMIFS: totals with conditionsHow SUMIF and SUMIFS add up only the rows that match: text, numbers, dates, criteria from cells and wildcards. Editable examples for Excel and Google Sheets.5 sections · live examples →
- Totals and reportsPercentage change, percent of total and running totalsThe formulas for percentage change (=new/old-1), percent of total, month-on-month growth and running totals in Excel and Google Sheets. Live examples.4 sections · live examples →
- Totals and reportsSUMPRODUCT and summary gridsHow SUMPRODUCT multiplies ranges and adds the results, how to use it for conditional totals in any version, and how to build summary grids with SUMIFS.4 sections · live examples →
Logic and errors
- Logic and errorsIF with AND and ORHow to combine IF with AND, OR and nested IFs (or IFS) for multiple conditions in Excel and Google Sheets, with live examples you can edit.4 sections · live examples →
- Logic and errorsIFERROR and IFNAHow IFERROR and IFNA catch formula errors, when to use IF instead, and why hiding errors can hide mistakes. Editable examples for Excel and Google Sheets.5 sections · live examples →
- Logic and errors#N/A, #VALUE!, #REF! and other formula errorsWhat each spreadsheet error means, what usually causes it, and how to fix it, with live examples you can edit. Works for Excel and Google Sheets.5 sections · live examples →
- Logic and errorsFind and remove duplicatesFlag duplicates with COUNTIF, mark repeats after the first, count distinct values and validate entries with ISNUMBER and MATCH. Excel and Google Sheets.4 sections · live examples →
- Logic and errorsNested IF vs IFS vs SWITCHWhen to use nested IF, IFS or SWITCH for three or more outcomes, how to add a catch-all so nothing returns #N/A, and the order conditions must go in.3 sections · live examples →
Text and dates
- Text and datesThe TEXT functionHow TEXT formats numbers, money, percentages and dates inside formulas, with the format codes you'll actually use. Live examples for Excel and Google Sheets.4 sections · live examples →
- Text and datesClean up messy textFix messy imported text: extra spaces, non-breaking spaces, line breaks, odd capitals, names to split and numbers stored as text. Live, editable examples.4 sections · live examples →
- Text and datesDATEDIF and date differencesCalculate age, tenure and time between dates with DATEDIF, YEARFRAC, EDATE, NETWORKDAYS and plain subtraction, plus working hours from times. Live examples.5 sections · live examples →
Dynamic arrays
- Dynamic arraysThe FILTER functionHow FILTER returns every matching row as a spilled list in Excel 365 and Google Sheets: one condition, AND and OR conditions, and a fallback when nothing matches.4 sections · live examples →
- Dynamic arraysUNIQUE, SORT and spill rangesDistinct lists with UNIQUE, sorted results with SORT and SORTBY, lists from SEQUENCE, the A2# spill reference and how to fix #SPILL!. Live examples.4 sections · live examples →
- Dynamic arraysThe LET functionHow LET names intermediate results inside one formula, making long formulas shorter, clearer and faster. Excel 2021, Microsoft 365 and Google Sheets.3 sections · live examples →
- Dynamic arraysLAMBDA, MAP and BYROWWrite your own spreadsheet functions with LAMBDA, call them, name them, and apply them with MAP, BYROW and REDUCE. Microsoft 365 and Google Sheets.4 sections · live examples →
Analysis and money
- Analysis and moneyMEDIAN, PERCENTILE, STDEV and CORRELTypical values, spread and relationships: MEDIAN vs AVERAGE, MODE, PERCENTILE and QUARTILE, RANK.EQ, STDEV.S vs STDEV.P and CORREL. Live examples.4 sections · live examples →
- Analysis and moneyPMT: loan payments (and FV, NPV, IRR)Calculate a monthly loan payment with PMT, total interest, months to repay (NPER), savings growth (FV), and project value with NPV and IRR. Live examples.5 sections · live examples →