What a cash book is
A cash book records every receipt and payment in date order, and after each entry it shows what you should be holding. It works for a shop till, a small business bank account, a club treasurer or your own spending. Unlike a ledger, it needs no double entry: one row per transaction is enough.
Step 1: Set up the columns
Put these headings in row 1, starting at A1:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Date | Description | Category | Money in | Money out | Balance |
Format columns D, E and F as numbers with two decimals (Ctrl+1 → Number). Format column A as a date. The Category column is optional, but it is what lets you see later where the money went.
Step 2: The running balance formula
Your first row is the opening balance. Enter it as money in on row 2 (for example, "Opening balance" with 5,000 in column D), then in F2 type:
=D2-E2Every row after that adds what came in and takes away what went out from the balance above it. In F3 type:
=F2+D3-E3Copy F3 down as far as you need. That one formula is the whole cash book: each balance depends only on the row above and its own row, so the last figure in column F is always your current position.
Step 3: Add a summary
Under the table, or on the right, add a small summary. Assuming your entries run from row 2 to row 200:
- Total money in:
=SUM(D2:D200) - Total money out:
=SUM(E2:E200) - Closing balance:
=SUM(D2:D200)-SUM(E2:E200). This should equal the last balance in column F. If it does not, a balance formula has been overwritten somewhere. - Largest single payment:
=MAX(E2:E200) - Transactions logged:
=COUNT(A2:A200)
To total by category, use SUMIF. For example, everything spent on rent: =SUMIF(C2:C200,"Rent",E2:E200).
Single, double and triple column cash books
Accounting courses teach three formats. The layout above is a single column cash book: one money column pair and one balance.
- Double column: separate Cash and Bank columns on each side, so you add a second pair of in/out columns and a second running balance:
=G2+H3-I3for the bank side. - Triple column: adds a Discount column on each side. Discounts are totalled with SUM but do not change either balance.
For most small businesses the single column format is enough. Keep one cash book per bank account and one for physical cash.
Three mistakes that break the balance
1. Adding new rows below the block
If your summary uses a fixed range like D2:D200 and you type on row 201, the totals miss it. Insert new rows inside the range (right-click a row → Insert) so every range grows by itself.
2. Sorting the table
A running balance only makes sense in date order. If you sort by category or amount, every balance after the first moved row becomes meaningless. Use a filter to view by category instead of sorting.
3. Typing over a balance
Typing a number into column F to make it "match the bank" breaks every balance below it. If the cash book and the bank disagree, find the missing or wrong entry instead. That is what a bank reconciliation is for.
Checking it against the bank
At month end, compare the closing balance with the bank statement. The usual differences are cheques not yet cleared, deposits not yet showing, bank charges and interest you have not recorded. Add the charges and interest to the cash book as rows, then the remaining difference should be timing items only.
Frequently Asked Questions
What is the formula for a running balance in Excel?
Put the opening balance in the first row, then use =previous balance + money in - money out on each row after it, for example =F2+D3-E3, and copy it down.
Can I keep a cash book in Google Sheets?
Yes. The same columns and formulas work in Google Sheets and LibreOffice without changes.
Why does my closing balance not match the total?
Usually a balance cell has been typed over, a row was added below the summed range, or the table was sorted out of date order. Check that every cell in the Balance column still contains the formula.
What is the difference between a cash book and a petty cash book?
A cash book records all receipts and payments for an account. A petty cash book records small cash spending from a float, usually topped back up to a fixed amount.