You don’t need accounting software to keep clean books. Plenty of small businesses, freelancers and side hustles run perfectly well on a spreadsheet, as long as it’s set up the right way and updated on a schedule. This guide walks you through bookkeeping in Excel step by step: the three sheets you need, how to record transactions, how to reconcile with your bank and how to turn it all into a profit and loss statement.
You can follow along with the free starter workbook, or try the online version right on this page first.
Free, no sign-up. Works in Excel, Google Sheets and LibreOffice.
Try it online: free bookkeeping and P&L tool
Enter a few transactions below, or click Load example. The tool builds a profit and loss statement and a monthly cash summary as you type. You can print it, save it as a PDF, download it as Excel, Word or CSV, or send a link to your accountant.
3. Your profit & loss
Income vs. expenses by month
Profit & loss by account
Monthly summary
Made with the free bookkeeping tool at bookkeepingsheets.com
What bookkeeping in Excel actually involves
Bookkeeping is simply recording every dollar that comes into and goes out of your business, and putting each one in a category. That’s it. Done consistently, it answers three questions at any time:
- Am I making money? Income minus expenses: your profit.
- Where is the money going? Expenses by category.
- How much cash do I have? Your bank balance, checked against the bank.
A spreadsheet can do all three with just three sheets: a list of categories, a list of transactions and a summary. That’s how the starter workbook is built.
Step 1: Open a separate business bank account
Before any spreadsheet, separate your business money from your personal money. One business checking account (and, if you use one, one business credit card) means your bank statement becomes the source for almost every entry. Mixing personal and business spending is the number one reason spreadsheet bookkeeping gets messy.
Step 2: Set up your chart of accounts
A chart of accounts is just your list of categories. Keep it short: 10 to 20 categories is plenty for most small businesses. The workbook’s Accounts sheet starts with three income accounts (Sales, Service income, Other income) and twelve common expense accounts such as Rent, Advertising, Contract labor, Insurance and Software & subscriptions.
If you file Schedule C, it helps to name your expense accounts after the lines on that form. At tax time, you can copy the totals straight across. Rename, add or remove accounts to fit your business, but keep the Type column set to Income or Expense, because the P&L uses it.
Step 3: Record every transaction
On the Transactions sheet, each row is one transaction:
- Date: the date it hit your bank or card.
- Description: short but specific, like “Invoice 1003 – Pine Street Cafe” or “Online ads”, not “payment”.
- Account: pick from the dropdown, which lists your chart of accounts.
- Money in or Money out: the amount, always as a positive number.
- Receipt (Y/N): whether you’ve kept the receipt. Expenses without a receipt are highlighted.
The Balance column keeps a running total starting from the opening bank balance on the Start Here sheet. An account name that isn’t on your chart of accounts turns red, so typos don’t silently drop out of your reports.
How often? Weekly is ideal; monthly is the minimum. Ten minutes a week is far easier than a weekend in April trying to remember what a $64.50 charge from March was.
Step 4: Reconcile with your bank every month
Reconciling means checking that your spreadsheet agrees with your bank. At the end of each month, compare the running balance on the last transaction of the month with the ending balance on your bank statement. If they match, you’re done. If they don’t, the difference is almost always one of these:
- A transaction you forgot to enter (bank fees and small subscriptions are the usual suspects).
- A transaction entered twice.
- A typo in an amount, such as 86.40 entered as 864.00.
- A payment that’s in your sheet but hasn’t cleared the bank yet.
Reconciling monthly keeps mistakes small and easy to find. It’s also the step that makes your numbers trustworthy enough to base decisions on.
Step 5: Read your profit and loss statement
The P&L sheet adds up every transaction by account and month, with no extra work from you. Here’s the summary for the example data in the workbook, a small service business, for January to March:
| Account | Jan | Feb | Mar | Total |
|---|---|---|---|---|
| Service income | $4,300 | $3,200 | $2,500 | $10,000 |
| Sales | – | – | $940 | $940 |
| Total income | $4,300 | $3,200 | $3,440 | $10,940 |
| Rent | $1,200 | $1,200 | $1,200 | $3,600 |
| Contract labor | – | $650 | – | $650 |
| Insurance | – | – | $375 | $375 |
| Advertising | – | $300 | – | $300 |
| Utilities & phone | – | $120 | – | $120 |
| Office & supplies | $86.40 | – | – | $86.40 |
| Meals | – | – | $64.50 | $64.50 |
| Software & subscriptions | $45 | – | – | $45 |
| Bank & card fees | $15 | – | – | $15 |
| Total expenses | $1,346.40 | $2,270 | $1,639.50 | $5,255.90 |
| Net profit | $2,953.60 | $930 | $1,800.50 | $5,684.10 |
From this we can read a lot. The business earned $10,940 and spent $5,255.90, a net profit of $5,684.10, or 52% of income. Rent is by far the largest expense at a third of income. February was the weakest month, with net profit of only $930, because two one-off costs (the designer and the ads) landed in the same month as a slower invoice month. And the bank balance grew from $5,000 to $10,684.10, which matches the closing balance in the monthly cash summary.
The formulas behind it
Everything in the workbook uses a handful of standard functions, so it works in Excel, Google Sheets and LibreOffice:
Running balance = Opening balance + SUM(Money in - Money out, up to this row)
Account type = INDEX(Accounts Type, MATCH(Account, Accounts Name, 0))
Income by month = SUMIFS(Money in, Account, "Sales", Month, 1)
Expense by month = SUMIFS(Money out, Account, "Rent", Month, 1)
Net profit = Total income - Total expenses
The key idea is SUMIFS: “add up the Money out column, but only rows where the account is Rent and the month is January”. One formula, copied across 12 months and every account, builds the whole P&L.
Common mistakes to avoid
- Recording owner transfers as income or expenses. Money you put into the business, or take out for yourself, isn’t income or an expense. Use a separate “Owner contribution” or “Owner draw” account (add it to the chart of accounts with its own type) so it doesn’t distort your profit.
- Recording loan payments as an expense. Only the interest part of a loan payment is an expense. The rest reduces what you owe.
- Too many categories. Fifty categories make every entry a decision. Start small and split a category only when you need the detail.
- Skipping reconciliation. A spreadsheet that isn’t checked against the bank will drift, and you won’t know by how much.
- Throwing away receipts. The IRS generally expects you to keep records that support your income and deductions for at least three years after you file, and longer in some cases. A photo of each receipt in a dated folder is enough for most small businesses.
When to move from Excel to accounting software
A spreadsheet works well until it doesn’t. It’s usually time to switch when you have more than a few hundred transactions a month, need to send invoices and track who has paid, run payroll, collect sales tax in several states, or have an accountant who wants direct access to your books. Until then, a clean spreadsheet costs nothing and teaches you exactly how your business makes money.
Once your basic books are running, these templates cover the next steps: an accounts receivable aging report for money customers owe you, an accounts payable tracker for bills you owe, and a 13-week cash flow forecast to see what’s coming.
Bookkeeping Kit Pro 2026 links your P&L, invoices, bills, bank rec, mileage and quarterly tax in one Excel workbook (works in Google Sheets). The free templates on this site stay free.
Related calculators and templates
- Sales Tax Calculator: Price Plus Tax & Total With Tax
- Free Small Business Bookkeeping Spreadsheet
- Monthly Bank Reconciliation Template + How to Reconcile in 5 Steps
- Free Income and Expense Tracker for Self-Employed
- Accounts Payable Tracker Template
FAQ
Can I really do my bookkeeping in Excel?
Yes. For a small business with a single bank account and a few dozen transactions a month, Excel or Google Sheets is perfectly adequate, provided you record every transaction, categorize it consistently and reconcile with your bank monthly.
Is this single-entry or double-entry bookkeeping?
The workbook uses single-entry bookkeeping: each transaction is recorded once, as money in or money out. That’s the norm for small cash-based businesses. Double-entry, where every transaction affects two accounts, is what accounting software does behind the scenes and becomes important as a business grows.
Should I record transactions on the date of the invoice or the date of payment?
For simple cash-basis bookkeeping, record them when the money actually moves: when the payment hits your bank or card. Track unpaid invoices separately, for example with an A/R aging report.
Does it work in Google Sheets?
Yes. Upload the .xlsx file to Google Drive and open it with Google Sheets. The formulas, dropdowns and color highlights all carry over.
What’s the difference between this and your bookkeeping spreadsheet template?
This starter workbook is the step-by-step learning version with a monthly P&L built in. Our small business bookkeeping spreadsheet and the income and expense tracker for the self-employed are alternatives with different layouts. Pick the one that feels most natural to you.
This guide and template are for general record-keeping and information. They aren’t tax, legal or accounting advice. For questions about your own situation, talk to a tax professional.
Comments
4 responses to “How to Do Bookkeeping in Excel: A Step-by-Step Guide for Beginners”
[…] How to Do Bookkeeping in Excel […]
[…] How to Do Bookkeeping in Excel […]
[…] How to Do Bookkeeping in Excel […]
[…] Yes, for many very small businesses: one person in the books, low volume, no payroll and no inventory. It works as long as you reconcile the bank every month and keep the file backed up. See our guide on how to do bookkeeping in Excel. […]