Bookkeeping has a reputation for being complicated, and professional bookkeeping certainly can be. But basic bookkeeping for a small business, recording what money comes in, what goes out, and keeping those records organized and reconciled, is something any business owner can handle in Excel with the right setup.
This guide walks through building a simple single-entry bookkeeping system in Excel. It is not a replacement for a CPA or full accounting software for complex businesses. But for a sole proprietor, freelancer, or small service or retail business with straightforward finances, it handles everything you need at zero cost.
Your general ledger is the master record of every financial transaction in your business. Every dollar that comes in or goes out gets recorded here, in chronological order, with enough detail to understand what happened and why. This is the foundation of your bookkeeping system.
| Column | What to Enter |
|---|---|
| Date | Transaction date |
| Description | Clear description of what the transaction was for |
| Category | Income or expense category, use a dropdown for consistency |
| Type | Income or Expense |
| Amount In | Money received, leave blank for expenses |
| Amount Out | Money paid out, leave blank for income |
| Running Balance | =Previous Balance plus Amount In minus Amount Out |
| Account | Which bank account or card this transaction is from |
| Reconciled | Yes or No, confirmed against bank statement |
| Reference | Invoice number, receipt number, or check number |
Here is what a week of entries looks like in practice:
A chart of accounts is simply a standardized list of categories for your income and expenses. Getting this right from the start matters because it determines how your financial reports will be organized and what detail you can see in them.
Keep a separate sheet listing all your accounts with a short code for each. Your ledger entries reference these codes via a dropdown so categories stay consistent.
Common income categories for small businesses:
Common expense categories:
Reconciliation means comparing your Excel ledger against your actual bank statement to confirm they match. This is the most important bookkeeping habit you can develop. It catches missed transactions, duplicate entries, and errors before they compound into bigger problems.
The monthly reconciliation process works like this:
Step 1: Download your bank statement for the month as a PDF or CSV.
Step 2: Go through each transaction on the bank statement and find the matching entry in your Excel ledger. Mark each matched transaction as Reconciled by changing the Reconciled column from No to Yes.
Step 3: Count any unmatched transactions on both sides. Bank transactions with no ledger entry need to be added. Ledger entries with no bank transaction need to be investigated, they may be timing differences or errors.
Step 4: Your ledger ending balance should match your bank statement ending balance. If it does not, the difference equals the value of whatever is missing or wrong.
With your general ledger populated and reconciled, generating a monthly profit and loss report is a handful of SUMIF formulas. This is the report your accountant, bank, or investors want to see and it takes about five minutes to set up once your ledger is running.
Total income for the month:
Total expenses by category for the month:
Net profit for the month:
Set this up for each month of the year in separate columns and you have a full year profit and loss statement that updates automatically as you log transactions. This is the document your accountant needs at year end and the one your bank asks for when you apply for a loan or line of credit.
Excel bookkeeping works well for businesses with relatively straightforward finances. The signals that it is time to consider dedicated software are usually the same ones. Transaction volume gets high enough that manual entry becomes impractical. You need automatic bank feeds that import transactions directly. You have inventory that needs to be tracked as an asset. You need to generate formal financial statements for investors or lenders. Or your accountant specifically recommends it.
For everyone else, which is most small businesses in their first few years, a well-maintained Excel ledger is completely sufficient and significantly cheaper than software that you would use at 20% of its capacity.
Tell us about your business and we'll build a complete Excel bookkeeping system — general ledger, chart of accounts, reconciliation tracker, and monthly P&L report. Starting at just $75.
📧 Get Started Today