An expense spreadsheet becomes more useful when it can answer two different questions: what did the business spend, and why does the account balance look the way it does? An expense total cannot answer the second question on its own.
This worked setup separates purchase detail from account movements. It is a starting point for a simple business, not a complete accounting system or a tax-deduction calculator. All names and amounts below are fictional, in USD. Add appropriate controls for your accounting method, entity and jurisdiction.
Use three tabs with different jobs
Expenses: one row for each purchase or separately classified part of a purchase. Account activity: all money entering or leaving each account, including transfers, owner funding and card repayments. Reconciliation: opening balances, closing balances and explained differences for each statement period.
Give records stable IDs so the same purchase can be linked across tabs without being counted twice. Keep one master expense table with date filters rather than copying the same entries into monthly sheets. Add a separate invoice and payment register when needed; an invoice issued and its later payment are not interchangeable records.
Build an expense table that preserves the evidence
| Field | What to record |
|---|---|
| Record ID and date | A unique reference and the transaction date; keep a separate posting date if useful. |
| Vendor and description | Who supplied the item and what you bought. |
| Payment account | The bank, card or cash account used. |
| Currency and gross amount | The full purchase amount, with currency clearly stated. |
| Business amount and category | The supported business portion and a consistent bookkeeping category. |
| Document and purpose | A receipt or invoice link, plus a short business-purpose note. |
| Review and matching status | Separate tax-review questions from statement-match status. |
Do not label a purchase deductible merely because it has a receipt. Mixed-use costs, equipment and other special cases may need additional review. A business-use amount is a bookkeeping allocation, not a tax conclusion. Keep the facts and calculation behind it. The IRS recordkeeping overview describes the need for records that support reported business activity.
Work through a purchase, a refund and a card repayment
| Record | Expense-table entry | Account-activity entry |
|---|---|---|
| E-001: supplies bought on card | Supplies $120; link receipt R-001 | Card balance owed increases $120. |
| E-002: partial supplies refund | Supplies −$20; link E-001 and refund record | Card balance owed decreases $20. |
| T-001: bank pays the card | No second supplies expense | Bank decreases $100; card balance owed decreases $100. |
The net supplies amount is $100: $120 less the $20 refund. The bank payment is also $100, but it settles the card balance rather than buying supplies again. Link both sides of T-001. If you sum purchases and card repayments together as expenses, this example becomes an incorrect $200.
For a single $90 receipt containing $60 of business supplies and $30 of personal items, preserve the $90 source total and the allocation. In the expense view, include only the supported $60 business portion. The account activity must still explain the whole $90 payment. How the personal portion is classified depends on the business structure; ask your bookkeeper rather than hiding it in supplies.
Reconcile the account, not the expense total
Here is a separate bank-account example. Opening balance is $1,000. Customer receipts add $800, owner funding adds $200, a card repayment uses $100, and a bank fee uses $10. The recorded closing balance is $1,890: $1,000 + $800 + $200 − $100 − $10. Owner funding is not a sale, and the card repayment is not a new expense.
Suppose the bank statement also shows $1,890 and every recorded item has cleared. The balance agrees. If instead your books include a further $50 payment that has not cleared, your book balance is $1,840. The $1,890 statement less that identified $50 outstanding payment also equals $1,840. Retain its date and reference and check that it clears later; do not invent an adjustment to force agreement.
Reconcile each bank and card account separately, using consistent signs. For a card, a useful convention is opening amount owed plus purchases and fees, less refunds and payments, equals closing amount owed. The IRS Publication 583 discusses bank reconciliation and timing differences. Expense totals alone cannot establish that an account reconciles.
Add a few spreadsheet controls before relying on totals
Use category dropdowns, real date values and numeric amount columns. Protect formula cells from accidental editing, keep a backup, and test any formula after adding rows. A missing receipt, an unresolved classification and an unmatched statement line should have different review labels.
Test the workbook with the examples above. Confirm that the refund reduces supplies, the card repayment stays out of expense totals, and each account balances independently. Check duplicate IDs and compare summary totals with the underlying rows. Document category changes and apply them consistently to comparable entries.
There is no universal transaction-count limit for spreadsheets. Consider changing tools or getting bookkeeping help when corrections, reconciliations or business complexity become difficult to manage. For broader routines, see our weekly and month-end guide; for the handoff, see preparing expense records for tax review.
