A bookkeeping spreadsheet template for a one-person business needs three tabs: a transactions tab that holds every bank statement row, a category list, and a monthly summary that totals income and expenses by category. You paste each month's statement rows in, give each row a category, and the summary updates itself. That covers most sole owners' books.
What tabs does a bookkeeping spreadsheet template need?
A bookkeeping spreadsheet template needs one tab for raw transactions, one for the list of categories, and one for the monthly summary. Each tab has a single job. Keeping them apart means you can add rows without breaking formulas, and change a category name in one place.
A lot of free templates online arrive with ten or twelve tabs: one per month, a separate sheet for each expense type, a mileage log, an invoice register and a dashboard with charts. That looks thorough, but it multiplies the places a number can go missing. If January's rent sits on the January tab and February's sits on the February tab, a yearly total needs a formula that reaches across twelve sheets, and one renamed tab breaks it.
One long transactions tab is easier to trust. Every row from every month lives in the same table, with a date that says which month it belongs to. The summary tab then asks a simple question of that table: how much was spent on this category in this month? Nothing is copied between tabs, so nothing gets copied twice.
The three tabs at a glance
- Transactions: one row per statement line, in date order, never summarized by hand.
- Categories: a short list of income and expense names, plus a column saying which kind each one is.
- Summary: months across the top, categories down the side, a formula in every cell.
You can add a fourth tab later for something specific to your trade, such as mileage. Start with three.
Columns on the bookkeeping template's transactions tab
The transactions tab of a bookkeeping template needs six columns: date, description, amount, category, account and a notes field. The first three come straight from the bank statement. The other three are yours to fill. Money in is a positive amount and money out is a negative amount, so one column carries both.
Here is the layout with example values. The figures are made up for illustration.
| Column | What goes in it | Example value |
|---|---|---|
| Date | Posting date from the statement | 2026-09-04 |
| Description | Payee text exactly as printed | STRIPE TRANSFER 0904 |
| Amount | Positive for money in, negative for money out | 1,240.00 |
| Category | One name from the Categories tab | Sales |
| Account | Which account or card the row came from | Checking |
| Notes | Anything you will need to remember at tax time | Invoice 1043, client A |
Two choices in this layout save trouble later. A single signed amount column, rather than separate debit and credit columns, means every formula is a plain sum. And copying the description exactly as the bank printed it, rather than tidying it, lets you sort and match the same payee month after month.
Use a drop-down list on the category column that reads from the Categories tab. In Excel and Google Sheets this is data validation. It stops "Software" and "software " from becoming two categories that the summary counts separately.
Filling the bookkeeping template from a month of statement rows
Filling the bookkeeping template is a monthly paste: get the statement rows into the date, description and amount columns, check the count, then categorize. Rows can come from a CSV download in online banking, from a converted PDF statement, or, for a very quiet month, from typing.
The CSV download is the cleanest route when your bank offers it for the month you need. Open the file, copy the three columns, and paste them under last month's rows. Check that the amounts landed as numbers, not as text. A number aligns to the right of the cell by default; text that only looks like a number aligns to the left and adds up to zero.
When the bank only gives you a PDF, copying straight out of the PDF viewer tends to scramble the columns. A converter is faster. CheapBankConvert, which is preparing to open, turns a PDF statement into CSV or Excel rows with date, description, amount and balance columns, and lets you see the first two pages before paying; the CheapBankConvert homepage lists its early per-statement prices.
A check before you categorize
Before giving any row a category, compare two numbers. Add the amounts you just pasted and add that total to last month's closing balance. The result should equal this month's closing balance on the statement. If it does not, a row is missing or doubled, and finding it now takes two minutes. Finding it in April takes an afternoon.
Then sort the new rows by description so repeat payees sit together, and fill their category in blocks.
Summary formulas for a one-person bookkeeping workbook
The summary tab of a one-person bookkeeping workbook uses one formula, copied into every cell: SUMIFS. It adds the amounts on the transactions tab where the category matches the row label and the date falls inside the month in the column header. You write it once and fill it across and down.
Suppose the transactions tab is a table with columns named Date, Amount and Category, the summary has categories in column A and the first day of each month in row 1. The formula in cell B2 is:
=SUMIFS(Transactions[Amount], Transactions[Category], $A2, Transactions[Date], ">="&B$1, Transactions[Date], "<"&EDATE(B$1,1))
The dollar signs keep the category column and the month row fixed as you copy. EDATE moves the date forward one month, so each column covers exactly its own month.
The three lines that matter
Below the category rows, add three totals. Income is the sum of the income categories. Expenses is the sum of the expense categories. Profit is income plus expenses, since the expenses are negative. That profit line is the number you watch every month, and it is the line your tax return will start from.
Leave one category, called Transfer, out of all three totals. It holds money moving between your own accounts, such as a card payment from checking. Counting it as an expense would double the spending already recorded on the card.
Is a bookkeeping spreadsheet template enough at tax time?
A bookkeeping spreadsheet template is enough at tax time for many sole owners, if it clearly shows income and expenses and you keep the receipts. The IRS does not demand a particular system. Its Publication 583 on starting a business and keeping records says you can choose any system suited to your business that clearly shows your income and expenses.
The same publication describes supporting documents as the papers behind the entries: sales slips, paid bills, invoices, receipts, deposit slips and canceled checks. A spreadsheet row that says "Office supplies, 84.20" is a summary. The receipt is the proof. The Notes column is where you say which receipt or invoice a row belongs to, so you can find it if anyone asks.
What a spreadsheet will not do is check itself. It accepts a wrong category, a duplicated row or a missing month without complaint. That is why the monthly balance check above matters more in a workbook than in accounting software, which does some of that checking for you.
Bookkeeping workbook errors that throw off the monthly totals
Most errors in a bookkeeping workbook come from the same few habits: counting transfers as spending, pasting a month twice, leaving amounts stored as text, and mixing personal purchases into business rows. Each one moves the profit line without leaving any visible sign on the summary tab.
- Transfers counted as expenses. A card payment from checking appears on both statements. Give both sides the Transfer category and exclude it from the totals.
- A month pasted twice. Sort by date after each paste and look for identical rows on the same day. A quick count of rows per month also shows a doubled month at a glance.
- Amounts stored as text. Pasted amounts with currency symbols or trailing spaces can turn into text. SUMIFS skips them, so the total comes out short. Convert them to numbers before you categorize.
- Personal rows mixed in. If one account pays for both your groceries and your web hosting, mark the personal rows with a category that the summary ignores, or better, move the business onto its own account.
- Categories renamed halfway through the year. Renaming "Software" to "Subscriptions" in July leaves January to June under a name the summary no longer looks for. Rename on the Categories tab and fix old rows at the same time.
When the bookkeeping spreadsheet stops being the cheap choice
The bookkeeping spreadsheet stops being the cheap choice when the monthly routine takes more of your time than software or a bookkeeper would cost. Signs include several accounts to reconcile, invoices to chase, sales tax to track, or a first employee on payroll, which a workbook handles badly.
Put a number on it. If the monthly paste, categorize and check takes you two hours, multiply that by what an hour of your work earns. Then compare it with the yearly price of a software plan, which small business accounting software cost per year breaks down by tier, and with the fees in what a bookkeeper costs for a small business. If the spreadsheet still wins, keep it.
One practical point if you keep the workbook in Google Sheets: the file is only as safe as the account that opens it. Read whether signing in with Google is safe for your files before you store a year of business records there, and turn on two-step verification either way.