Free small business bookkeeping template for Excel (income, expenses and a bank check)
Most small businesses that keep their books in a spreadsheet need the same three things: one list of every transaction from the bank account and the business card, a category on each line, and a monthly total of income and expenses that agrees with the bank. This free Excel template does exactly that. It's single-entry bookkeeping, the kind a sole proprietor, freelancer, side business or small LLC can keep without accounting software, with a suggested US Schedule C line for each category and a check that every account agrees with its statement. Below: what's in the file, how the sheets fit together, a worked month that agrees to the cent, how to keep it, and when to move to QuickBooks or Xero.
Short answer: download the bookkeeping template (.xlsx). On Setup, list your bank accounts and cards with their opening balances and set the first month of your year. On Transactions, put every statement line in one list with its account, a category and the amount as a positive number in Money in or Money out. Summary totals income and expenses by category for each month and shows net profit; at month end, enter each statement's ending balance on Setup and the status reads Agrees. The Example sheets are one month for the same made-up business as our sample bank and card statements: income 29,597.43, expenses 24,758.47, net profit 4,838.96.
Download the free bookkeeping template (.xlsx)
Seven sheets: How to use, a blank Setup (6 accounts, 35 starter categories, room for 45), Transactions (1,500 rows with account and category drop-downs and a filter) and Summary (12 months), plus a filled-in Example of each. It uses only SUM, SUMIF, SUMIFS, SUMPRODUCT, COUNT, COUNTA, COUNTIF, COUNTIFS, ROUND, IF, OR, N, DATE, YEAR, MONTH and TEXT, so it works in Excel, Google Sheets (File, Import), LibreOffice and Numbers. No sign-up, no email, no macros.
1. What's in the template
- Setup: the business name and the first month of the year (January 1 for a calendar year), then up to six accounts (bank, card or cash) with an opening balance each. For every account the sheet adds up money in and out from Transactions, works out the closing balance, and compares it with the statement balance you type in: Agrees or Off by …. Card balances count what you owe, so a purchase raises it and a payment lowers it. Below the accounts is the yellow category list: name, type (Income, Expense or Not in profit) and a suggested Schedule C line, with the year's money in, money out and net for each.
- Transactions: Date, Account, Description, Category, Money in, Money out and Notes, for every account in one list. Account and Category are drop-downs read from Setup, the headings stay in view, and a filter lets you show one account or one category. At the top: lines entered, total in and out, and two counters that should read 0: lines without a category, and lines whose category isn't on Setup.
- Summary: each category's net for each of the 12 months from the first month on Setup, and a year total, then total income, total expenses, net profit, the not in profit total, the net cash in and out of all accounts, and Not categorised, which is 0.00 when every line has a category from the list.
Every amount is a positive number in either Money in or Money out, so there are no minus signs to get wrong. The sign is decided by the category's type: for an expense the net is out − in, so a refund lowers the expense; for income it's in − out.
2. The categories and Schedule C lines
The 35 starter categories follow the expense lines of the 2026 Schedule C (Form 1040), the form a US sole proprietor or single-member LLC uses to report business profit, so the Summary's year totals line up with the form. Rename them freely; outside the US, or for a partnership or corporation, ignore the Schedule C column or replace it with your own tax form's lines.
| Category | Type | Schedule C line (suggested) |
|---|---|---|
| Sales | Income | 1 Gross receipts or sales |
| Other income | Income | 6 Other income |
| Interest earned | Income | Usually Schedule B, not Schedule C |
| Advertising & marketing · Car & fuel · Merchant & platform fees · Contractors | Expense | 8 · 9 · 10 · 11 |
| Employee benefits · Insurance (not health) · Interest paid · Legal & professional | Expense | 14 · 15 · 16c · 17 |
| Office expense · Software & subscriptions | Expense | 18 · 18 or 27b |
| Equipment rental · Rent · Repairs & maintenance · Supplies | Expense | 20a · 20b · 21 · 22 |
| Payroll taxes · Taxes & licenses · Sales tax paid | Expense | 23 |
| Travel · Meals · Utilities · Phone & internet · Wages | Expense | 24a · 24b · 25 · 25 · 26 |
| Bank fees · Other expenses | Expense | 27b (itemised in Part V) |
| Inventory & materials | Expense | 4, via cost of goods sold in Part III |
| Owner money in · Owner drawings · Transfers between accounts · Card payments · Loan received · Loan principal repaid | Not in profit | None |
| Equipment purchases | Not in profit | 13, through depreciation or section 179 |
Three that catch people out. Sales tax: the IRS instructions for line 23 say sales tax you collected from buyers is included in gross receipts on line 1 and the tax you pay over is deducted on line 23, so customer payments go in Sales in full and the payment to the state goes in Sales tax paid. (In double-entry books, sales tax collected usually sits in a liability account instead, as in our chart of accounts template; either way the profit is the same.) Meals are generally only 50% deductible, and Car & fuel is either actual costs or the standard mileage rate, not both (a mileage log keeps the miles for the standard rate). The Schedule C column is a starting point, not tax advice: confirm the lines with your tax preparer.
3. Worked example
The Example sheets are September 2026 for a fictional design studio, the same made-up business as our sample statements, with its year starting January 1. It has two accounts: business checking ··0427, the 31 lines of the sample bank statement (opening 12,418.36), and business card ··7316, the 21 lines of the sample credit card statement (previous balance 2,846.12 owed). That's 52 lines, each with a category.
| Account | Opening | Money in | Money out | Closing | Status |
|---|---|---|---|---|---|
| Business checking ··0427 | 12,418.36 | 29,597.43 | 28,575.27 | 13,440.52 | Agrees |
| Business card ··7316 (owed) | 2,846.12 | 1,542.18 | 1,939.94 | 3,243.88 | Agrees |
Both closing balances are the statements' ending balances, so both accounts agree. The September column of the Summary then reads:
| Category | Line | September 2026 |
|---|---|---|
| Sales (Stripe payouts, customer checks, Zelle, a wire) | 1 | 29,594.22 |
| Interest earned | Sch. B | 3.21 |
| Total income | 29,597.43 | |
| Wages (two Gusto runs) | 26 | 12,818.95 |
| Contractors | 11 | 2,825.00 |
| Rent | 20b | 1,850.00 |
| Employee benefits (staff health premium) | 14 | 1,432.10 |
| Payroll taxes | 23 | 1,288.41 |
| Sales tax paid | 23 | 1,167.29 |
| Travel (flights, hotel, Uber, all on the card) | 24a | 1,076.85 |
| Supplies | 22 | 809.20 |
| Utilities | 25 | 412.66 |
| Software & subscriptions (bank and card) | 18 or 27b | 384.96 |
| Office expense (after a 42.18 refund) | 18 | 230.63 |
| Phone & internet | 25 | 189.34 |
| Car & fuel | 9 | 119.94 |
| Advertising & marketing | 8 | 59.99 |
| Meals | 24b | 56.79 |
| Interest paid (card interest) | 16c | 21.36 |
| Bank fees | 27b | 15.00 |
| Total expenses | 24,758.47 | |
| Net profit | 4,838.96 | |
| Not in profit: transfer to savings, card payments | -4,214.56 | |
| Net cash in − out, all accounts | 624.40 | |
| Not categorised | 0.00 |
Net profit plus the not-in-profit lines is the net cash movement of both accounts: 4,838.96 − 4,214.56 = 624.40, which is 31,139.61 in minus 30,515.21 out. That's the check behind Not categorised: if a line had no category, or one that isn't on Setup, its amount would show up there instead of 0.00.
Notice what isn't an expense. The 2,500.00 transfer to savings and the 3,214.56 Amex payment leave the checking account, and the 1,500.00 payment reaches the card, but none of them is spending: the card's purchases are already counted line by line. Card payments net to -1,714.56 here only because the Amex card and the account that paid card ··7316 aren't in the example; list every account and they cancel to 0.00.
4. How to keep your books in it, step by step
- Set up once. Business name, first month of the year, and each bank account, card and cash box with its type and opening balance: the ending balance on the statement just before your year starts (for a card, the amount owed). Last 4 digits of account numbers only.
- Check the categories. Rename the starter list to suit your business before you start; the drop-down and Summary follow. Keep it short: one category per line on your tax form or profit and loss is enough.
- Get the lines in, don't type them. Paste the rows from each account's CSV download, or a PDF statement converted to Excel, into Transactions, then fill in Account and Category. Typing a month of a busy account by hand is where most errors come from.
- Categorise every line. Sort or filter by Description to do all the Stripe payouts or all the Uber trips at once. Both counters at the top of Transactions should end at 0.
- Agree each account at month end. Enter each statement's ending balance (for a card, the new balance) on Setup. Agrees means nothing is missing or doubled for that account and month.
- Read the Summary. Income, expenses and net profit for the month and the year so far, ready for estimated tax payments, a loan application or your tax preparer. Keep the receipts and statements behind the lines (the IRS explains how long to keep records); the Notes column is for a receipt number or file name.
This is cash-basis bookkeeping. Income counts when the money arrives and expenses when they're paid, which is how most sole proprietors report. If you invoice and need to know who owes you (an invoice tracker or an aging report template covers that in a spreadsheet, a sales tracker logs sales vs bank deposits and a monthly goal, and a bill tracker does the same for bills you owe), or you hold inventory, you'll want invoices, bills and a balance sheet as well; see section 7.
5. Transfers, card payments and owner money
The Not in profit type is what keeps a simple spreadsheet honest. These lines are real cash moving in or out, so the accounts still agree with the bank, but they're not income or expenses:
| Line on the statement | Category | Why it isn't profit |
|---|---|---|
| Transfer to or from your savings | Transfers between accounts | The money is still the business's. With both accounts listed, it's out on one and in on the other. |
| Payment to the business card | Card payments | The purchases on the card are the expenses; counting the payment too would count them twice. |
| You put your own money in | Owner money in | A contribution, not a sale. |
| You pay yourself (sole proprietor) | Owner drawings | Not a business expense; profit is taxed whether you draw it or not. |
| Loan money in, principal repaid | Loan received, Loan principal repaid | Borrowed money isn't income and repaying it isn't an expense. The interest part is (Interest paid). |
| A laptop, vehicle or machine | Equipment purchases | Usually deducted over time as depreciation, or under section 179; ask your preparer. |
One payment that is profit even though it looks like a transfer: paying back yourself or an employee for business costs paid on a personal card. Split it across the expenses' own categories, using the category summary on an expense report, and don't also enter the receipts, or they count twice.
6. When an account doesn't agree
The difference on Setup is how far the sheet is from the statement for that account. Use its size:
- It equals one statement line: that line is missing, or it's in Transactions twice (common after pasting two overlapping downloads).
- It's twice an amount: that amount is in Money in when it should be in Money out, or the other way round.
- It divides by 9: transposed digits, such as 145.80 typed as 154.80.
- A line is on the wrong account: one account is off by an amount and another by the opposite amount.
- It's the same every month: the opening balance is wrong. Use the ending balance of the statement just before your year starts.
If you write checks, an account can be off by checks that haven't been cashed yet; the check register template tracks those, and the bank reconciliation template is the formal month-end proof. Our guide to finding a reconciliation discrepancy works through the causes in order.
7. When to move to QuickBooks or Xero
A spreadsheet is enough while one person keeps the books and the business runs on cash. It starts to strain when you send invoices and need to chase who owes you, pay bills on terms, run payroll, hold inventory, have a bookkeeper or accountant who wants access, or need a balance sheet for a lender. Moving over is mostly reuse:
- Categories become the chart of accounts. The chart of accounts template has numbered starter accounts with QuickBooks and Xero import files.
- The Summary is a cash-basis profit and loss. The profit and loss template lays the same totals out in the standard format with gross profit and margins, and the general ledger template is the double-entry version.
- Your transaction history imports. QuickBooks Online and Xero take bank lines from CSV; our QuickBooks Online CSV template and Xero CSV template show the exact columns, and catch-up bookkeeping from bank statements covers loading a whole year.
Only want to see spending against a monthly budget? The expense tracker template keeps expenses by category for any month with an over-budget status.
Only want one account in date order with a running balance? The cash book template is the one-sheet version of this, and the cash flow forecast template turns the Summary's monthly totals into a 12-month plan.
Statements only as PDFs? Get the rows instead of typing them
Utilly's converter reads a bank or credit card statement PDF, downloaded or scanned, into Excel, CSV, QuickBooks or Xero files and checks the rows against the statement's beginning and ending balance, the same check this template does. The preview is free with no sign-up. Try it on the sample bank statement first: it's the checking account in the Example sheets, and every download of the sample is free because the data is fictional.
Method: single-entry, cash-basis bookkeeping in spreadsheet form, with categories mapped to the expense lines of the 2026 Schedule C (Form 1040) and the IRS Instructions for Schedule C (line 23 for sales tax and employer payroll taxes), checked November 3, 2026. The example business, payees and amounts are fictional. Template built and checked November 3, 2026: the Example sheets recalculate in LibreOffice to both accounts agreeing (13,440.52 and 3,243.88 owed), income of 29,597.43, expenses of 24,758.47, net profit of 4,838.96 and 0.00 not categorised. Not tax advice.
FAQ
Is the bookkeeping template free?
Yes. Download the .xlsx with no sign-up or email address. It has a blank Setup, Transactions and Summary, a filled-in example of each and instructions, and it works in Excel, Google Sheets (File, Import), LibreOffice and Numbers.
Can I do my own bookkeeping in Excel?
Yes, for a small cash-based business: list every bank and card transaction, give each a category, total income and expenses by month, and check each account against its statement every month. That's what this template does. Move to accounting software when you need invoicing, bills, payroll, inventory or a balance sheet.
What is single-entry bookkeeping?
Recording each transaction once, as money in or money out with a category, like a cash book. It's enough to work out profit for a sole proprietor's tax return. Double-entry bookkeeping records each transaction in two accounts, which also gives you a balance sheet; the general ledger template shows how.
How do I categorise expenses for Schedule C?
Use one category per Schedule C expense line: advertising (8), car and truck (9), contract labor (11), office expense (18), rent (20b), supplies (22), taxes and licenses (23), travel (24a), meals (24b), utilities (25), wages (26) and so on. The template's categories come with a suggested line for each; confirm them with your tax preparer.
Should transfers and credit card payments be expenses?
No. A transfer to your own savings and a payment to the business card are cash moving between your accounts. The card's purchases are the expenses, so counting the payment as well would count them twice. The template has a Not in profit type for these, and for owner money in, drawings and loan principal.
How do I get my bank transactions into the template?
Download a CSV from your bank and paste the rows into Transactions, or convert a PDF statement to Excel first. Then pick the account and category on each line. Paste each statement once; overlapping downloads are the usual cause of doubled lines.
Related guides
- Free sales tracker template for Excel (deposits, channels and goal)
- Free invoice tracker template for Excel (paid, open and overdue)
- Free bill tracker template for Excel (due soon, paid and overdue)
- Free expense tracker template for Excel (monthly budget vs actual)
- Free expense report template for Excel (with mileage and receipt checks)
- Free mileage log template for Excel (IRS 2026 rates, business use %)
Related tools
- Bank reconciliation checker: compare a bank statement to your books and list missing, duplicate and mismatched transactions.
- Receipt to CSV: turn PDF or photo receipts and invoices into CSV, Excel, or QuickBooks and Xero bill imports.
