Free cash book template for Excel (with categories and a balance check)
A cash book is the simplest set of books there is: every amount that comes into or goes out of one bank account or cash box, in date order, with a running balance and a category for each line. Sole traders, clubs, charities and small businesses keep one instead of full accounting software, and bookkeepers use one to rebuild a year that was never recorded. This free Excel template is an analysed cash book: receipts and payments, a category drop-down, totals for each category, and a box that checks the closing balance against the bank statement or a cash count. Below: what's in the file, the columns, a worked month that agrees to the cent, how to keep it, and the single, two-column and petty cash formats.
Short answer: download the cash book template (.xlsx). Enter the opening balance, then write each receipt and payment as a positive number in Receipts or Payments with a category; the Balance column runs by itself and the summary totals each category. At month end, enter the bank statement's ending balance (or the cash you counted): the Difference should be 0.00 and the status reads Agrees. The Example sheet is the same made-up account as our sample bank statement: receipts 29,597.43, payments 28,575.27, closing balance 13,440.52, the statement's ending balance.
Download the free cash book template (.xlsx)
Three sheets: a blank Cash book with 300 rows and 19 starter categories, a filled-in Example month and How to use. It uses only SUM, SUMIF, ROUND, IF, AND and TEXT, so it works in Excel, Google Sheets (File, Import), LibreOffice and Numbers. No sign-up, no email, no macros. It prints in landscape on letter or A4 with the column headings repeated on every page.
1. What's in the template
- Header: the account or cash box (for a bank account, the last 4 digits only), the opening balance and the date you start from.
- The cash book: Date, Ref, Details, Category, Receipts, Payments and a running Balance, with the headings frozen so they stay in view as you scroll. Yellow cells are inputs; the Balance column is a formula and stays blank on empty rows.
- Totals box (to the right): total receipts, total payments, the closing balance (opening + receipts − payments), an input for the bank statement balance or the cash you counted, the difference and a status line.
- Category summary: a yellow list of 19 starter categories (Sales, Wages & payroll, Rent, Software, Bank fees and so on) with receipts and payments totalled for each by SUMIF. The Category column's drop-down reads this list, so renaming a category changes the drop-down too. Anything typed in Category that isn't in the list lands on a Not in the list above line, so the category totals always add up to the grand totals.
Amounts are positive numbers in either Receipts or Payments, so there are no minus signs to get wrong, and each line has one category, which is what makes the cash book "analysed": at month end you can read income and spending by type straight off the summary.
2. The columns
| Column | What goes in it |
|---|---|
| Date | The day the money came in or went out. For a bank account, the statement date is fine; for cash, the receipt date. |
| Ref | A receipt or check number, or a short code: DEP, ACH, DEBIT, XFER, WIRE, FEE, INT. It's what lets you find the paperwork later. |
| Details | Who paid you or who you paid, and what for (invoice number, month of rent). |
| Category | Pick from the drop-down. Use the same names as your chart of accounts or tax form lines if you have them. |
| Receipts (in) | Money in: sales, customer payments, deposits, interest, transfers in, money the owner puts in. Positive number. |
| Payments (out) | Money out: bills, wages, purchases, card payments, bank fees, transfers out, owner drawings. Positive number. |
| Balance | Calculated: opening balance + all receipts − all payments up to that row. |
3. Worked example
The Example sheet is September 2026 for the fictional business checking account ending 0427 at the made-up Example Community Bank, the same account as our sample bank statement PDF. It opens on September 1 at 12,418.36, the statement's beginning balance, and has 31 entries, one for each line on the statement, each with a category.
| Category | Receipts | Payments |
|---|---|---|
| Sales (Stripe payouts, customer checks, Zelle, a wire) | 29,594.22 | |
| Interest | 3.21 | |
| Wages & payroll (two Gusto runs) | 12,818.95 | |
| Card payments (Amex) | 3,214.56 | |
| Contractors & services (cleaning, contractor) | 2,825.00 | |
| Transfers (to savings) | 2,500.00 | |
| Rent | 1,850.00 | |
| Insurance | 1,432.10 | |
| Payroll taxes | 1,288.41 | |
| Sales tax | 1,167.29 | |
| Office & supplies | 548.15 | |
| Utilities | 412.66 | |
| Software (QuickBooks, Google Workspace, Adobe) | 251.99 | |
| Phone & internet | 189.34 | |
| Fuel & travel | 61.82 | |
| Bank fees | 15.00 | |
| Total | 29,597.43 | 28,575.27 |
Closing balance: 12,418.36 + 29,597.43 − 28,575.27 = 13,440.52. The statement's ending balance is also 13,440.52, so the difference is 0.00 and the status reads Agrees. The 15.00 service fee and 3.21 interest only appear on the statement; they're in the cash book because the month isn't finished until they are.
Two of those lines aren't income or expenses: the 2,500.00 transfer to savings just moves money between the business's own accounts, and the 3,214.56 Amex payment pays off a card whose purchases are the real expenses. Keep them in their own categories so they don't inflate spending; the credit card statement template lists what the card was used for.
4. How to keep it, step by step
- One sheet per account. Copy the Cash book sheet for each bank account and each cash box (right-click the tab, Move or Copy, Create a copy). Mixing accounts in one sheet makes the balance meaningless.
- Start from a balance you can prove: the ending balance on the last bank statement, or the cash you counted, and its date.
- Set your categories first. Rename the starter list to match your chart of accounts or your tax return's expense lines, so the summary gives you the numbers you'll need at year end.
- Write each receipt and payment in date order, with a reference you can trace to a receipt, invoice, check stub or statement line.
- At month end, compare. Enter the statement's ending balance (bank) or the cash you counted (cash box). At 0.00 the month is done; write the closing balance down as next month's starting point.
- Keep the paperwork. The cash book is a summary; the receipts, invoices and statements behind it are the evidence. Keep them as long as your tax authority asks (in the US the IRS explains how long to keep records).
Checks change the picture. If you write checks, the bank's balance runs ahead of yours until they're cashed, and the difference won't be 0.00 at month end. For an account with checks, keep the check register template instead: it has a Cleared column and compares only the cleared items with the statement.
5. Single, two-column and petty cash books
| Format | Columns | Use it when |
|---|---|---|
| Single-column cash book | Receipts and payments for one account | One account, no need to split by type. This template without the Category column. |
| Analysed cash book (this template) | Receipts and payments plus a category for each line, totalled by category | You want income and expenses by type without accounting software: sole traders, clubs, small charities, rental property. |
| Two-column cash book | Cash and Bank columns on both the receipts and payments sides | Textbook format for a business with both cash and a bank account. Here, keep one sheet for the cash box and one for the bank; a transfer between them is a payment on one sheet and a receipt on the other. |
| Three-column cash book | Cash, Bank and Discount columns | Discounts allowed and received are recorded alongside payments. Rare in small businesses today; record a discount as its own category line. |
| Petty cash book | Small cash payments, topped up to a fixed float | An office cash box. Use this template with a fixed opening float and a cash count as the check figure; top-ups are receipts. Or use the petty cash log template, which adds a denomination count, vouchers and a top-up summary. |
More than one account? The bookkeeping template keeps every bank, card and cash line in one list, checks each account against its statement and totals income and expenses by month, with a suggested Schedule C line for each category.
Need double entry, a balance sheet or loan and card accounts? The general ledger template posts the same month as journal entries with a trial balance, and the cash flow statement template explains a period's change in cash by operating, investing and financing activity. Planning ahead? The cash flow forecast template turns the cash book's monthly category totals into a 12-month or 13-week forecast.
In traditional double-entry layouts, receipts sit on the left (debit) side and payments on the right (credit) side of the page. A spreadsheet puts them in two columns on the same row instead; the totals and the balance are the same.
6. When the closing balance doesn't agree
The difference shows how far the cash book is from the bank's figure or the cash in the box. Use its size:
- It equals one line on the statement: that line is missing here (often a fee, interest or an automatic payment), or is in the cash book twice.
- It's twice an amount: that amount is in the wrong column, a payment entered as a receipt or the other way round.
- It divides by 9: look for transposed digits, such as 145.80 typed as 154.80.
- It's the total of checks you've written recently: they haven't been cashed yet. That's timing, not an error; see the check register template above.
- It's the same every month: the opening balance was wrong. Start again from a statement's ending balance.
Our guide on how to find a reconciliation discrepancy works through the causes in order, and the free bank statement balance checker points to the line that's probably duplicated, missing or has its sign flipped.
7. From a cash book to QuickBooks or Xero
A cash book works well until there are too many lines to type or someone else needs the books. Moving on is easier than it sounds:
- Don't retype the bank account. The bank's own CSV export, or a statement PDF converted to rows, gives you the same lines. Paste them into the cash book and add only the categories.
- Your categories become accounts. If the category names match your chart of accounts, the summary is your monthly profit and loss by cash; the profit and loss template puts those totals into the standard format with gross profit and margins. No chart yet? The chart of accounts template has 50 numbered starter accounts with QuickBooks and Xero import files.
- Importing later: QuickBooks Online and Xero import bank lines from CSV, not from a cash book with a summary box. Our QuickBooks Online CSV template and Xero CSV template show the exact columns; catching up a year at once is covered in catch-up bookkeeping from bank statements.
Bank statement only as a PDF? Get the rows instead of typing them
Utilly's converter reads a bank statement PDF, downloaded or scanned, into Excel, CSV, QuickBooks or Xero files and checks the rows against the statement's beginning and ending balance. The preview is free with no sign-up. Try it on the sample bank statement first: it's the same account as the Example sheet, and every download of the sample is free because the data is fictional.
Want the statement itself as a spreadsheet? The sample bank statement Excel file lays out the same month with a running balance, and the bank reconciliation template is the formal month-end workpaper when your books and the bank need to be proved side by side.
Method: the layout is the standard analysed cash book (receipts and payments with analysis by category) in spreadsheet form, with a closing-balance check added. The example business, payees and amounts are fictional. Template built and checked November 1, 2026: the Example sheet recalculates in LibreOffice to receipts of 29,597.43, payments of 28,575.27, a closing balance of 13,440.52 and a 0.00 difference.
FAQ
What is a cash book?
A record of every amount received and paid from one bank account or cash box, in date order, with a running balance. An analysed cash book adds a category to each line, so you can total income and expenses by type. For many sole traders, clubs and small charities it is the whole set of books.
Is the cash book template free?
Yes. Download the .xlsx with no sign-up or email address. It has a blank 300-row cash book with 19 starter categories, a filled-in example month and instructions, and it works in Excel, Google Sheets (File, Import), LibreOffice and Numbers.
What's the difference between a cash book and a check register?
Both list money in and out with a running balance. A check register adds a Cleared column because checks take days to be cashed, so it compares only cleared items with the bank. A cash book adds categories and assumes the money has moved, which suits a cash box or an account without checks.
How do I make a two-column cash book in Excel?
A two-column cash book has Cash and Bank columns on both the receipts and payments sides. In a spreadsheet it's simpler to keep one sheet for the cash box and one for the bank account; a deposit of cash into the bank is a payment on the cash sheet and a receipt on the bank sheet.
Why doesn't my cash book agree with the bank statement?
Usually a fee, interest or automatic payment on the statement that isn't in the cash book, an amount typed differently, or an item in the wrong column. A difference that divides by 9 suggests transposed digits. Checks written but not yet cashed also cause a difference; use a check register for that account.
Can I use it in Google Sheets?
Yes. In Google Sheets choose File, Import, Upload and pick the .xlsx, or open it from Google Drive. The formulas are plain SUM, SUMIF, ROUND, IF, AND and TEXT, and the category drop-down comes across as a data validation list.
Related guides
- Free small business bookkeeping template for Excel (income, expenses and a bank check)
- Free cash flow forecast template for Excel (12-month and 13-week)
- Free petty cash log template for Excel (with cash count and top-up)
- Free check register template for Excel (with a cleared-balance check)
- Free bank reconciliation template for Excel (with a worked example)
- Free sample bank statement in Excel (with a blank template)
Related tools
- Bank reconciliation checker: compare a bank statement to your books and list missing, duplicate and mismatched transactions.
- Receipt to CSV: turn PDF or photo receipts and invoices into CSV, Excel, or QuickBooks and Xero bill imports.
