If you run a small business, a freelance practice or a side hustle, you don’t need accounting software on day one. You need a clean, consistent record of every dollar that comes in and goes out. A well-built spreadsheet does that job surprisingly well, and it’s free.
This page gives you a free small business bookkeeping spreadsheet for Excel and Google Sheets. You record transactions on one sheet, and it builds a monthly summary, a running cash balance and a full-year profit and loss statement for you. Below the download you’ll find a step-by-step guide to setting it up, an explanation of the formulas, and the mistakes that most often throw small business books off.
Free, no sign-up. Works in Excel, Google Sheets and LibreOffice.
What’s inside the template
The workbook has five tabs. You only type on two of them.
- Start Here: your business name, the year you’re tracking and your opening cash balance. Plus a short how-to and a color legend.
- Categories: 21 ready-made income and expense categories you can rename, delete or add to (up to 40).
- Transactions: room for 1,000 entries. Pick a category from a dropdown; the template works out whether it’s income or an expense and which month it belongs to.
- Monthly Summary: income and expenses by category for each month, net profit, profit margin, and cash at the start and end of every month.
- Profit & Loss: a one-page annual income statement with each line shown as a percentage of total income.
There are no macros, no locked cells and no hidden sheets. Every formula is visible, so you can check it or adapt it.
| Date | Description | Category | Type | Amount | Paid via | Month |
|---|---|---|---|---|---|---|
| 01/03/2026 | Invoice #1003 – client project | Service income | Income | $2,800.00 | Bank transfer | Jan 2026 |
| 01/05/2026 | Office rent | Rent | Expense | $1,200.00 | Bank transfer | Jan 2026 |
| 01/09/2026 | Accounting software | Software & subscriptions | Expense | $65.00 | Credit card | Jan 2026 |
| 01/14/2026 | Online store sales week 1 | Sales | Income | $1,100.00 | PayPal/Stripe | Jan 2026 |
| 01/18/2026 | Stripe processing fees | Bank & payment fees | Expense | $28.00 | PayPal/Stripe | Jan 2026 |
| 01/22/2026 | Facebook ads | Advertising & marketing | Expense | $250.00 | Credit card | Jan 2026 |
| 01/27/2026 | Internet & phone | Utilities & phone | Expense | $110.00 | Debit card | Jan 2026 |
| 02/03/2026 | Invoice #1006 – client project | Service income | Income | $3,500.00 | Bank transfer | Feb 2026 |
| 02/05/2026 | Office rent | Rent | Expense | $1,200.00 | Bank transfer | Feb 2026 |
| 02/09/2026 | Accounting software | Software & subscriptions | Expense | $65.00 | Credit card | Feb 2026 |
How to set it up in 10 minutes
Step 1: Fill in the settings
Open the Start Here tab and fill in the three yellow cells. The year matters most: the monthly columns and the profit and loss statement only count transactions dated in that year, so last December’s invoices won’t sneak into this January.
For opening cash balance, use the combined balance of your business bank accounts (and any business cash on hand) at the start of the year. If you’re starting mid-year, enter your balance on January 1 and add the transactions from January onward, or simply start a fresh copy next January.
Step 2: Tailor the categories
The default categories cover what most service businesses and small online stores need: sales, service income, rent, software, advertising, payment fees and so on. Rename them to match how you think about your business, but resist the urge to create 40 tiny categories. Ten to fifteen expense categories is usually the sweet spot: detailed enough to spot where money goes, simple enough that you’ll categorize consistently.
Each category needs a Type of either Income or Expense. That one setting is what lets you type every amount as a positive number later.
Step 3: Record transactions
On the Transactions tab, add one row per money movement: the date, a short description you’ll understand in six months (“Invoice #1042 – Acme Corp” beats “payment”), the category, the amount and how it was paid.
Two rules keep the numbers right:
- Always enter positive amounts. The sheet blocks negative numbers on purpose. A $1,200 rent payment is entered as 1200 with the category Rent; the category makes it an expense.
- Use the dropdown for categories. If a category doesn’t match the list, the Type column shows Check category in red, and the Monthly Summary counts how many rows need fixing. Those rows are left out of the totals until you correct them.
Step 4: Read your monthly summary
The Monthly Summary tab updates the moment you add a transaction. Read it top to bottom: income by category, expenses by category, net profit, profit margin, then cash at the start and end of each month.
| Category | Jan | Feb | Mar | Apr | May | Jun | Total |
|---|---|---|---|---|---|---|---|
| Income | |||||||
| Sales | $1,100 | $1,340 | $850 | $850 | $1,100 | $920 | $6,160 |
| Service income | $2,800 | $3,500 | $2,400 | $3,100 | $3,100 | $3,100 | $18,000 |
| Total income | $3,900 | $4,840 | $3,250 | $3,950 | $4,200 | $4,020 | $24,160 |
| Expenses | |||||||
| Advertising & marketing | $250 | $150 | $250 | $250 | $150 | $250 | $1,300 |
| Bank & payment fees | $28 | $28 | $31 | $28 | $28 | $28 | $171 |
| Rent | $1,200 | $1,200 | $1,200 | $1,200 | $1,200 | $1,200 | $7,200 |
| Software & subscriptions | $65 | $65 | $65 | $65 | $65 | $65 | $390 |
| Utilities & phone | $110 | $110 | $110 | $110 | $110 | $110 | $660 |
| Total expenses | $1,653 | $1,553 | $1,656 | $1,653 | $1,553 | $1,653 | $9,721 |
| Net profit (loss) | $2,247 | $3,287 | $1,594 | $2,297 | $2,647 | $2,367 | $14,439 |
| Profit margin | 57.6% | 67.9% | 49.0% | 58.2% | 63.0% | 58.9% | 59.8% |
| Cash at start of month | $5,000 | $7,247 | $10,534 | $12,128 | $14,425 | $17,072 | |
| Cash at end of month | $7,247 | $10,534 | $12,128 | $14,425 | $17,072 | $19,439 | $19,439 |
Step 5: Reconcile with your bank every month
This is the step most people skip, and it’s the one that makes your books trustworthy. Once a month, compare the cash at end of month figure with your bank statement’s closing balance. If they don’t match, something is missing, duplicated or entered with the wrong amount. Find it now, while the month is fresh in your mind, rather than at tax time.
How the formulas work
You don’t need to understand the formulas to use the template, but knowing how they work makes it easy to adapt.
Income or expense? The Type column looks up each category on the Categories tab:
=IFERROR(INDEX(Categories!$C$6:$C$45, MATCH(C5, Categories!$B$6:$B$45, 0)), "Check category")MATCH finds the row of the category name, INDEX returns the type from that row, and IFERROR catches anything that isn’t on the list.
Monthly totals. Each cell on the Monthly Summary adds up the amounts for one category within one month:
=SUMIFS(Transactions!$E$5:$E$1004, Transactions!$C$5:$C$1004, $B8,
Transactions!$A$5:$A$1004, ">="&C$5,
Transactions!$A$5:$A$1004, "<="&EOMONTH(C$5, 0))The header cell (C5) holds the first day of the month, and EOMONTH(C5, 0) returns the last day. Filtering on real dates instead of month names is what keeps different years apart.
Running cash balance. January starts with your opening balance. Every later month starts with the previous month’s closing cash, and closing cash is simply opening cash plus that month’s net profit.
Common bookkeeping mistakes (and how the template helps)
- Mixing personal and business money. No spreadsheet fixes this. Use a separate business bank account and card, and only record business transactions here. If you pay a business bill from a personal card, record it and note it, so you can reimburse yourself properly.
- Treating owner withdrawals as business expenses. Money you take out for yourself is not a business expense, and moving money between your own accounts is not income. If you want the cash balance to keep matching your bank, add an “Owner draws” category (Type: Expense) and record draws there, then ignore that line when you judge your profit.
- Entering the same payment twice. This happens when you record an invoice when you send it and again when it’s paid. This template is cash-basis: record income when the money arrives, not when you invoice.
- Vague descriptions. “Amazon” could be office supplies, inventory or a personal purchase. Write what it was for.
- Waiting until tax season. Ten minutes a week beats a lost weekend in March. Many owners set a recurring calendar reminder for Friday afternoon.
When to move beyond a spreadsheet
A spreadsheet is a great fit when you have a few dozen to a few hundred transactions a month, one or two bank accounts, and you’re on cash-basis bookkeeping. It starts to strain when you need to track unpaid invoices and bills (accrual accounting), manage inventory, run payroll, or handle sales tax across several states. At that point, dedicated accounting software, or a bookkeeper, will usually save more time than it costs. The categories and habits you build with this template make that move much easier.
Frequently asked questions
Does this template work in Google Sheets?
Yes. In Google Drive, choose New > File upload, upload the .xlsx file, then open it with Google Sheets (or use File > Import in an open sheet). The dropdowns, formulas and formatting carry over. It also works in LibreOffice Calc and Apple Numbers, though Numbers may show some formatting differently.
Is this cash-basis or accrual bookkeeping?
Cash-basis. Income is recorded when you receive the money and expenses when you pay them. That’s how most very small businesses keep their books, but check with your tax professional about which method applies to you.
Can I use it for my taxes?
It gives you organized totals by category, which makes tax preparation far easier for you or your accountant. It is not tax software, though, and the default categories aren’t mapped to any specific tax form. Ask your tax preparer how they’d like your categories grouped. For how long to keep records, see the IRS guidance on record retention.
What if I have more than 1,000 transactions a year?
Insert new rows inside the table (anywhere above the last row) so the summary formulas expand to include them, then copy the Type and Month formulas down into the new rows. If you regularly go well past 1,000 transactions a year, that’s a good sign it’s time to look at accounting software.
How do I track multiple years?
Keep one file per year. At the start of a new year, make a copy, clear the Transactions sheet, change the year on Start Here, and enter last year’s closing cash as the new opening balance.
This template and guide are for general record-keeping and educational purposes only, and are not accounting or tax advice. See our disclaimer.