Zapwise › Guides › Spreadsheet formula shortcuts
Spreadsheet formula shortcuts we tested
12 copy-paste formulas for everyday work spreadsheets: lookups, totals by category, overdue tasks, clean-up and dates. Each one gave the right result on sample data when we checked it.
How we checked these
We entered each formula in LibreOffice Calc 25.2 on Oct 29, 2026, ran it on made-up sample data, and confirmed the result by hand. We have not run them in Microsoft Excel or Google Sheets themselves yet. The notes under each formula say which versions support it.
Change the cell ranges and sheet names to match your own sheet.
Find a value by ID (XLOOKUP)
=XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")
Looks for A2 in the Products sheet's column A and returns the matching value from column C. Shows "Not found" instead of an error. Excel 365/2021/2024 and Google Sheets.
Old-school lookup (older Excel)
=IFERROR(INDEX(Products!C:C, MATCH(A2, Products!A:A, 0)), "Not found")
Same job as XLOOKUP, works in every Excel version and Google Sheets.
Does this value exist in a list?
=IF(COUNTIF(List!A:A, A2)>0, "Yes", "No")
Handy for checking whether a customer, SKU or email already exists.
Sum by category
=SUMIFS(D:D, B:B, "Marketing")
Adds column D only where column B is "Marketing". Replace the text with a cell reference like H2 to make it reusable.
Count overdue tasks
=COUNTIFS(E:E, "<"&TODAY(), F:F, "<>Done")
Due dates in E, status in F. Counts tasks past due that aren't marked Done.
% complete
=COUNTIF(F2:F200, "Done") / COUNTA(A2:A200)
Format the cell as a percentage. Column A holds task names, F holds status.
Show only matching rows
=FILTER(A2:F200, B2:B200="Open")
Returns every row where column B is "Open". Updates automatically. Excel 365/2021/2024 and Google Sheets.
Remove extra spaces
=TRIM(A2)
Removes leading, trailing and double spaces, a top cause of lookups failing.
Join cells with a separator
=TEXTJOIN(", ", TRUE, A2:D2)Joins A2 to D2 with commas and skips blanks.
Working days between two dates
=NETWORKDAYS(B2, C2)
Counts Monday to Friday. Add a holiday range as a 3rd argument.
Hide errors politely
=IFERROR(B2/C2, "")
Shows blank instead of #DIV/0! when C2 is empty.
Percent change
=(C2 - B2) / B2
New minus old, divided by old. Format as %. Wrap in IFERROR if old can be 0.
Need a formula that isn't here?
Describe your columns to an AI app with this prompt, then test the answer on a few rows you can check by hand:
I use [EXCEL / GOOGLE SHEETS]. My data: column A = [WHAT], B = [WHAT], C = [WHAT], rows 2 to [N]. Write a formula for [GOAL] and explain it in plain English.
All 25 recipes in one PDF
The AI Spreadsheet Kit for Excel & Google Sheets has 25 formula recipes, 25 AI prompts for spreadsheets and an error decoder.