How to Do Bookkeeping for a Small Business in Excel, Step by Step

Last updated September 28, 2026. This article is general information, not tax or accounting advice. For advice about your situation, talk with a tax professional.

Quick answer

To do bookkeeping for a small business in Excel, choose the cash method, then build a workbook with a tab for your categories, a tab where you log every transaction, and a summary tab that totals income and expenses by month with SUMIFS formulas. Each month, compare your log with your bank and card statements, fix anything missing, and review your profit. Keep your receipts and statements as proof.

Key takeaways

• The IRS calls a single-entry system "a simple and practical system if you are starting a small business."
• The cash method records income when you receive it and expenses when you pay them.
• You need four tabs to start: Categories, Transactions, Summary, and a monthly checklist.
• Use Schedule C expense categories so your totals are ready for tax time.
• Reconcile every month. Bookkeeping falls apart when it's left for April.

Can you really do small business bookkeeping in Excel?

Yes, for many small businesses. If you're a freelancer, consultant, creative, or tradesperson with one business account and a manageable number of transactions each month, a well-built spreadsheet covers the basics.

IRS Publication 583 describes the simplest approach, single-entry bookkeeping: "A single-entry system is based on the income statement (profit or loss statement). It can be a simple and practical system if you are starting a small business."

A single-entry system records each transaction once, as income or as an expense. That's exactly what a spreadsheet does well.

Excel starts to struggle when you have inventory, employees on payroll, many bank accounts, or hundreds of transactions a month. We compare the two in bookkeeping spreadsheet vs. QuickBooks.

Step 1: Choose cash or accrual

Before you build anything, decide when you'll record income and expenses. IRS Publication 583 defines the two main methods:

• Cash method: "Under the cash method, you report income in the tax year you receive it. You usually deduct or capitalize expenses in the tax year you pay them."
• Accrual method: "Under an accrual method, you generally report income in the tax year you earn it, even though you may receive payment in a later year."

Most solo business owners use the cash method because it matches their bank account. Money in is income on the day it arrives. Money out is an expense on the day you pay. If you're unsure which method applies to you, ask a tax professional.

Step 2: Set up your categories

Create a tab named Categories. List your income categories first, such as Client services, Product sales, and Affiliate income. Then list your expense categories.

Use the expense lines from Schedule C so your year-end totals match your tax return. Common ones for small businesses include:

• Advertising
• Car and truck expenses
• Contract labor
• Insurance (other than health)
• Legal and professional services
• Office expense
• Supplies
• Taxes and licenses
• Travel
• Deductible meals
• Utilities
• Other expenses

Add two more categories that aren't expenses: Owner's draw, for money you take out, and Owner contribution, for money you put in. These keep transfers from distorting your profit.

For a plain-English explanation of every line, read Schedule C expense categories explained.

Step 3: Build your transaction log

Create a tab named Transactions. This is where you'll spend most of your time. Use these columns:

Column	What goes in it
Date	The day money moved
Description	What it was for
Vendor or client	Who you paid or who paid you
Account	Business checking, business card, and so on
Type	Income, Expense, or Transfer
Category	Picked from your Categories list
Amount	Always a positive number
Month	The formula =MONTH(A2)
Receipt saved?	Yes or No
Notes	The business purpose, especially for meals and travel

Add dropdown lists to the Type and Category columns:

• Select the column.
• On the Data tab, click Data Validation.
• In the Allow box, choose List.
• In the Source box, select your list on the Categories tab.
• Click OK.

Then format the whole log as a table. Select any cell in your data and press Ctrl+T on Windows or Command+T on a Mac. Tables grow automatically when you add rows, and formulas fill down on their own.

Step 4: Build your monthly summary

Create a tab named Summary. List every category down column A and put the months 1 through 12 across row 1.

In cell B2, enter a SUMIFS formula that totals the Amount column where the category and month match:

=SUMIFS(Transactions!$G:$G, Transactions!$F:$F, $A2, Transactions!$H:$H, B$1)

Here, column G is Amount, column F is Category, and column H is Month. Adjust the letters to match your layout. Copy the formula across and down.

Then add these rows at the bottom:

• Total income: The sum of your income category rows.
• Total expenses: The sum of your expense category rows. Leave out Owner's draw and Owner contribution.
• Net profit: Total income minus total expenses.

That bottom section is your profit and loss statement, month by month.

Step 5: Record transactions on a schedule

Pick a routine you'll stick with:

• Daily: Log each transaction as it happens. It takes a couple of minutes.
• Weekly: Once a week, open your bank and card accounts and log everything from the past seven days.

Download your bank transactions as a CSV file to save typing. Most banks offer this. Paste the date, description, and amount into your log, then fill in the category and notes.

Step 6: Reconcile every month

Reconciling means checking that your spreadsheet matches your bank. At the end of each month:

• Open your bank and card statements.
• Check that every transaction on the statements appears in your log.
• Check that nothing in your log is missing from the statements, or is duplicated.
• Make sure every row has a category. Look for blanks or anything labeled "misc."
• Compare your ending bank balance with what your records say it should be.
• Save the month's receipts and statements in a folder for that month.

Our monthly financial close-out workflow turns this into a checklist you can print.

Step 7: Review your numbers

Bookkeeping is only useful if you look at the results. Each month, check:

• Did the business make a profit?
• Which expense categories are growing?
• Are there subscriptions you no longer use?
• How does this month compare with last month?

A simple chart of monthly income and expenses on your Summary tab makes trends easy to see. For cash flow specifically, see our simple cash flow tracking template.

Step 8: Keep your records

Your spreadsheet summarizes your business. Your receipts and statements prove it. The IRS says, "Supporting documents include sales slips, paid bills, invoices, receipts, deposit slips, and canceled checks." Digital copies are fine. Publication 583 says, "All requirements that apply to hard copy books and records also apply to electronic storage systems that maintain tax books and records."

Read what receipts you need to keep for taxes for the full list and how long to keep them.

Excel tips that make bookkeeping easier

A few built-in Excel features save a lot of time and catch mistakes before they spread.

• Freeze the header row. On the View tab, click Freeze Panes, then Freeze Top Row. Your column names stay visible as you scroll.
• Highlight blank categories. Select the Category column. On the Home tab, click Conditional Formatting, then New Rule, and choose to format only cells that are blank. Pick a bright fill color. Anything you forgot to categorize now stands out.
• Sort and filter. Because your log is formatted as a table, each header has a filter arrow. Filter by category to see every Office expense, or by month to review one period.
• Format amounts as currency. Select the Amount column and choose Currency or Accounting from the number format list, so every figure lines up.
• Lock your formulas. On the Summary tab, protect the sheet so you don't overwrite a formula by accident. On the Review tab, click Protect Sheet.
• Save a copy at year-end. When the year is over, save the workbook under a new name, such as "Books 2026 Final," and start a fresh copy for the new year.

Example: one month of bookkeeping in Excel

Here's how one month looks for an example consultant who uses the cash method.

During the month, she logs 31 transactions from her business checking account and business card. Once a week, she downloads the transactions as a CSV file, pastes them into her Transactions tab, and picks a type and category for each one. It takes about ten minutes a week.

On the first of the next month, she runs her close-out. She compares her log with both statements and finds one charge she skipped, a software renewal, and adds it. Her conditional formatting shows one blank category, a restaurant receipt, so she labels it Deductible meals and adds a note about the client she met with. She checks that her two owner's draws are labeled as transfers, not expenses.

Then she opens her Summary tab. Her SUMIFS formulas have already totaled income and expenses for the month, and her net profit row shows what the business earned. She saves the month's receipts and statements in that month's folder and she's done.

Common Excel bookkeeping mistakes

• Mixing personal and business transactions. Keep separate accounts. Read how to separate business and personal expenses.
• Typing categories by hand. Use dropdown lists so totals don't split.
• Counting transfers as expenses. Owner's draws aren't expenses.
• Using negative numbers inconsistently. Keep all amounts positive and use the Type column to show whether each one is income or an expense.
• Skipping reconciliation. Small errors turn into big ones by year-end.
• Having no backup. Save your workbook to cloud storage so one broken laptop doesn't wipe out a year of records.

What if you're already behind?

Don't start from January and work forward. Start with the most recent month, get it clean, and set up your routine. Then work backward one month at a time. Our guide to catching up on overdue bookkeeping walks you through it.

Frequently Asked Questions

Sources

• IRS, Publication 583, Starting a Business and Keeping Records
• IRS, What kind of records should I keep
• IRS, Schedule C (Form 1040), 2025