Free Tool
Excel Formulas for Bookkeepers — the Cheat Sheet.
The formulas bookkeeping actually uses, each with a real bookkeeping example. Click any formula to copy it. Every one works in both Excel and Google Sheets, and most reference the layout of our free Companion Workbook so you can try them immediately.
The one concept that unlocks everything: a reference like
E5 shifts when you copy the formula;
$E$5 stays locked. Row totals want the first kind, references to a fixed cell (like a tax rate) want the second. That's Module 2 of our
Excel path.
Math & totals
| Formula | What it does | Bookkeeping example |
|---|
=SUM(E5:E304) | Adds a range. | Total income column on the Income tab. |
=SUMIF(D5:D404,"Rent",F5:F404) | Adds amounts where one condition matches. | Total everything categorized Rent. |
=SUMIFS(F5:F404,D5:D404,"Rent",A5:A404,">="&DATE(2026,1,1),A5:A404,"<"&DATE(2026,2,1)) | Adds amounts where several conditions all match. | January rent only — this exact pattern powers the workbook's Dashboard. |
=COUNTIF(D5:D404,"Office supplies") | Counts matching rows. | How many office-supply purchases this year? |
=ROUND(B7*0.0825,2) | Rounds to set decimals. | Sales tax at 8.25%, rounded to the cent — never let pennies drift. |
Logic & error handling
| Formula | What it does | Bookkeeping example |
|---|
=IF(F5>500,"Review","OK") | Returns one value or another based on a test. | Flag any expense over $500 for a second look. |
=IF(AND(D5="Meals (business)",F5>100),"Check receipt","") | Tests multiple conditions together. | Flag large meal expenses that need documentation. |
=IFERROR(B7/B8,0) | Replaces an error with a fallback value. | Avoid ugly #DIV/0! when a month has no expenses yet. |
Lookups
| Formula | What it does | Bookkeeping example |
|---|
=XLOOKUP(B2,Vendors!A:A,Vendors!C:C,"Not found") | Finds a value in one column, returns the matching value from another. | Pull a vendor's default category from a vendor list. |
=VLOOKUP(B2,Vendors!A:C,3,FALSE) | The older lookup you'll meet in inherited spreadsheets. | Same job as XLOOKUP; the FALSE means exact match — almost always what bookkeeping wants. |
Dates
| Formula | What it does | Bookkeeping example |
|---|
=TODAY() | Today's date, always current. | Anchor for aging calculations. |
=TODAY()-C5 | Dates subtract like numbers. | Days since invoice date — the heart of an A/R aging. |
=EOMONTH(A5,0) | Last day of a date's month. | Bucket any transaction into its month for reporting. |
=TEXT(A5,"mmm yyyy") | Formats a date (or number) as text. | Turn 01/15/2026 into 'Jan 2026' for clean report labels. |
Text cleanup
| Formula | What it does | Bookkeeping example |
|---|
=TRIM(B5) | Strips stray spaces. | Fix " Acme Landscaping " from a sloppy export — invisible spaces break lookups. |
=PROPER(B5) | Capitalizes Each Word. | Standardize vendor names typed in all caps or all lowercase. |
=B5&" — "&C5 | & joins text together. | Combine vendor and description into one label. |
Five shortcuts worth the muscle memory
Ctrl + Z undo (your best friend) · Ctrl + arrow jump to the edge of your data · Ctrl + Shift + arrow select to the edge · Ctrl + ; insert today's date · F4 (while editing) toggle those $ locks on a reference.
Go from cheat sheet to fluency
This page is the reference; the Excel for Bookkeepers path is the training — 14 modules with hands-on labs in the free workbook. Create a free account (no card required) to follow along as it rolls out.
Formula copied ✓