Excel Bookkeeping for Small Businesses: An Original Starter Layout

Excel can help a small business organize transactions, but a neat spreadsheet is not automatically a complete accounting system. Start with a defined purpose and controls. This original layout is a simple cash-movement register for learning and organization, not a ready-made double-entry ledger or downloadable workbook.

Canadian tax returns, goods and services tax (GST), harmonized sales tax (HST), payroll, unpaid invoices and financial statements may need more records and calculations than this register provides. Cash-basis reporting follows money received and paid. Accrual reporting follows income earned and expenses incurred, even if unpaid. A cash list does not let you choose cash-basis tax reporting when accrual reporting is required.

1. Define the columns before entering data

Use one transaction per row, one entity and one currency per register, with consistent types. Store dates as real spreadsheet dates, amounts as numbers and references as text when leading zeroes matter. Do not place several receipts or multiple currencies in one cell.

Original starter column map

Columns	Purpose
A: Date; B: Reference	Transaction date and unique traceable reference
C: Person or business; D: Category	Who you dealt with and what the transaction was for; keep category names consistent
E: Movement type	Receipt, payment, transfer or owner movement as appropriate
F: Inflow; G: Outflow	Separate numeric cash amounts
H: Running balance	Controlled formula, not a manually guessed total
I: Evidence; J: Review	Document reference and review status

Give categories stable meanings. A loan receipt is not a sale, and a transfer is not a business expense. Keep unusual items flagged until their treatment is reviewed. This cash register intentionally does not solve every tax category.

2. Build and verify a running balance

Put the opening bank balance in L2. For the first transaction row, an English-function example in H2 is =$L$2+SUM($F$2:F2)-SUM($G$2:G2). Copying down extends the cumulative range while keeping the opening amount fixed.

With opening cash of $2,000, a $600 receipt and a later $150 payment, the successive balances should be $2,600 and $2,450. These are control examples, not business profit. Interest, loans, owner movements and sales all affect cash differently from income.

Protect formula cells from accidental overwriting where appropriate. Check the first and last populated rows after pasting or inserting data. A formula that works in the first ten rows can stop counting later transactions if someone copies an incomplete range.

3. Use a bounded SUMIFS example carefully

For an illustrative category summary, set L3 to the first real date of the selected month and L4 to the first real date of the following month. To sum inflows labelled Sales in rows 2 through 500, an English-function example is:

=SUMIFS($F$2:$F$500,$D$2:$D$500,"Sales",$A$2:$A$500,">="&$L$3,$A$2:$A$500,"<"&$L$4)

The formula leaves out the first day of the next month. Every range must cover the same number of rows. Row 500 is included; row 501 is not. Before adding more data, extend every related range or use a properly maintained Excel table. Never leave transactions out just to fit the formula.

Function names, separators, decimal formats and date interpretation depend on the Excel language and regional settings. French Excel commonly uses SOMME.SI.ENS and semicolons. Verify the formula in the actual installation; changing the interface language does not justify guessing ambiguous pasted dates.

4. Test data quality before trusting totals

Use fictional practice rows before entering business data. Check a normal receipt, payment, refund, transfer and duplicate reference. Try a value stored as text and a date outside the selected month; verify the expected inclusion instead of assuming a visible number is counted.

• Retain invoice/reference leading zeroes.
• Do not turn blanks, zeroes and missing evidence into the same state.
• Skip wholly empty pasted rows, but investigate partial records.
• Keep an untouched original of an authorized import.
• Review category spelling, accents and accidental trailing spaces.

Sorting a partial selection can detach dates from amounts and evidence. Sort the complete dataset and verify record identities afterward. Do not rely on row position as the only identifier.

5. Reconcile and understand what is missing

Compare the register with complete bank statements and investigate differences. Maintain backups, access control and an appropriate change process. Limit sharing of personal or client details; a publicly accessible spreadsheet link is not a secure bookkeeping workflow.

A cash register alone does not track all customer invoices still unpaid, amounts owed to suppliers, expenses incurred but not yet billed, costs paid in advance, long-term assets or tax balances. A professional-looking chart cannot make it a complete, reliable set of financial statements. Consider an accounting system when transaction volume, several users, a need to track changes or complex work makes the register unsuitable.

This article provides a starter layout and illustrative formulas, not a downloadable workbook. Before using it for business records, check the formulas in your Excel version and confirm the reporting requirements with your bookkeeping professional.

6. Separate bookkeeping from practice coordination

Excel or an accounting system stores the relevant financial data. Bookwits can coordinate client documents, requested information and bookkeeping tasks around that work; it does not execute this workbook, replace a ledger or automatically validate formulas.

For each month, retain the reviewed register version, statement reconciliation, unresolved items and responsible reviewer. This makes the file understandable to the next person and helps identify when a simple register has outgrown its original purpose.