Your bank statement shows a $970 deposit, but your customer paid a $1,000 invoice. Your processor kept a $30 fee. Recording only $970 as sales would hide part of the sale and the entire fee. A simple Excel cashbook can track both figures and still tie back to the deposit.
This guide to small business bookkeeping in Excel with a monthly checklist shows how to build a transaction table, calculate monthly totals, and reconcile it against the bank statement. The worked example uses a small service business with one bank account, one currency, and a cash-based management view. Keep unpaid invoices and bills visible separately so you can plan upcoming payments.
When Excel is a reasonable starting point
Excel can work when one person records a manageable number of transactions and can review the workbook against supporting records each month. The example follows cash collected and paid. It doesn’t produce a balance sheet or track what customers owe automatically.
If you have payroll, inventory, several bank or card accounts, multiple currencies, or sales tax or VAT to account for, consider software or a bookkeeper before expanding the spreadsheet. Check which accounting method, reporting tools, and retention rules apply where you operate. Separate any taxes collected on behalf of a government from your own sales when applicable. IRS guide to recordkeeping · HMRC guidance on what business records to keep
1. Create the workbook and name the transaction table
Set up three tabs: Transactions, Monthly Summary, and Open Invoices and Bills. On Transactions, paste the following tab-separated headers into cell A1. If they land in one cell, use Data → Text to Columns with Tab as the delimiter.
Date Description Type Category Reference Money In Money Out
Select the header and space for an initial row, choose Insert → Table, and confirm that the table has headers. In the table design settings, name it Txn. Format Date as a date and the two amount columns as currency or numbers. Excel table formulas can refer to columns by name, which makes it easier to check what they include. Microsoft’s Excel table guide · Microsoft’s guide to structured references
| Column | What to record |
|---|---|
| Date | The date assigned under your bookkeeping process. Track bank clearing dates separately if timing differences occur. |
| Description | A short note that explains the cash movement. |
| Type | A consistent label, such as Sale, Expense, or Owner contribution. Add types for other movements you need to separate. |
| Category | The detail you want to review, such as Design services, Software, or Bank fees. |
| Reference | An invoice, receipt, or payout ID you can use to find the evidence. |
| Money In and Money Out | Positive amounts, placed in the appropriate column. |
Avoid treating owner funding, loans, and transfers as sales. Separate adjustments such as refunds from routine expenses so you can review them correctly. If a platform deposits money net of fees, use the payout report to group the gross receipts, fees, and any refunds or adjustments under the same reference. In this simple example, the sale and fee share the payout date and are recorded at settlement. If payments and payouts often cross month-end, track the processor balance separately and confirm which dates your reporting method requires.
On Open Invoices and Bills, track the reference, customer or supplier, due date, and unpaid amount. Update it when money changes hands so you can follow up on overdue items and see what’s coming due.
2. Build a monthly summary with SUMIFS
In Monthly Summary, enter a real Excel date for the first day of the reporting month in B1. Put the following labels in column A, starting at A3, and the formulas next to them in column B. Enter the opening and closing balances from your records as indicated.
| Cell | Label | Formula or input |
|---|---|---|
| B3 | Opening book balance | Enter the previous month’s closing book balance. |
| B4 | Money in | =SUMIFS(Txn[Money In],Txn[Date],">="&$B$1,Txn[Date],"<"&EDATE($B$1,1)) |
| B5 | Money out | =SUMIFS(Txn[Money Out],Txn[Date],">="&$B$1,Txn[Date],"<"&EDATE($B$1,1)) |
| B6 | Closing book balance | =B3+B4-B5 |
| B7 | Closing statement balance | Enter the closing figure from the bank statement. |
| B8 | Book minus statement | =B6-B7 |
The date criteria include the reporting month and exclude the first day of the following month. If the formula returns zero unexpectedly, check that the dates and amounts are stored as numbers and that the table is named Txn. Excel may use semicolons for formula arguments in your region. Microsoft’s guide to SUMIFS
To see just sales receipts for the month, add the Type criterion to the money-in formula:
=SUMIFS(Txn[Money In],Txn[Date],">="&$B$1,Txn[Date],"<"&EDATE($B$1,1),Txn[Type],"Sale")
Total money in and out helps reconcile cash. To calculate operating results, review the types and categories separately. For example, B4 may include owner funding.
3. Reconcile the workbook against the bank statement
Compare both records over the same dates. Match each bank line to a workbook entry or a group of entries with the same reference. If the processor deposited $970, match that deposit to the $1,000 sale and the $30 fee together. If you enter the $970 deposit as an additional sale, you’ll double-count part of the receipt.
Check for missing bank charges, duplicate entries, and payments that haven’t cleared. Record genuine timing differences and follow up in the next period. A zero difference helps confirm the cash totals agree. Matching the individual lines also helps catch errors that happen to offset each other. IRS guidance on reconciling your bank account
Worked reconciliation for a small design studio
The studio begins the month with $500. All movements below cleared in the same month. The amounts are illustrative and in U.S. dollars.
| Entry | Type | Money in | Money out |
|---|---|---|---|
| Client sale, payout P-101 | Sale | $1,000 | — |
| Processing fee, payout P-101 | Expense | — | $30 |
| Software subscription | Expense | — | $60 |
| Owner contribution | Owner contribution | $200 | — |
| Bank charge, found during reconciliation | Expense | — | $10 |
Before adding the bank charge, the workbook shows $1,610, while the bank statement shows $1,600. The owner finds the $10 fee on the statement, records it with a reference, and gets a closing book balance of $1,600. The month’s cash includes the $200 owner contribution, while the sales receipts formula returns $1,000.
Monthly bookkeeping checklist
- Gather the bank statement, payout reports, invoices, and receipts for the month.
- Record and categorize all movements, including fees, refunds, and owner funding.
- Match deposits and payments, grouping net payouts with their underlying entries.
- Compare closing balances, record timing differences, and investigate anything unexplained.
- Review unpaid invoices and bills, then schedule follow-up and upcoming payments.
- Review sales and expenses separately from transfers or financing, and document material changes.
- Keep a dated, backed-up month-end copy and restrict access to the supporting records.
Frequently asked questions
1. Can I do my small business bookkeeping in Excel?
Yes, if your records are simple enough to maintain and your setup meets the requirements that apply to you. Check whether you need compatible filing software or more detailed accounting reports.
2. Should I record an invoice when I send it or when it’s paid?
It depends on your accounting method. In this guide’s cash-based setup, track the unpaid invoice on the open items tab and record the cash receipt when paid under your chosen process. For formal reporting, confirm the applicable rules.
3. How do I record a bank transfer between business accounts?
If you track both accounts, record the withdrawal and deposit as linked transfers and exclude them from operating income and expenses. Reconcile each account separately. The example workbook focuses on a single account.
4. What if the bank and workbook balances still differ?
Start with the last date you know they agreed. Match statement entries and check for missing fees, duplicate deposits, reversed amounts, or transactions that cleared after month-end.
5. When should I move to bookkeeping software?
Consider switching if manually tracking payments, taxes, or balances takes too long, or if errors repeat. Check that you can export your workbook and supporting references for a clean handover.
Conclusion
Start with one transaction table and give each entry a type and a reference. At month-end, match the entries to the bank statement and explain any difference. Then review what customers still owe and what you need to pay next, while the records are fresh.


