Bookkeeping Template in Excel: How to Build One Step by Step

Short answer: a useful bookkeeping template in Excel needs only three sheets: a Settings sheet with your categories and accounts, a Transactions sheet formatted as an Excel table with drop-down lists, and a Summary sheet that adds up each category by month with SUMIFS. Add a reconciliation check that compares your book balance with the bank statement, and you have a template you can use every week without fighting the spreadsheet.

This guide shows how to build that template from a blank workbook, column by column and formula by formula. It works in any recent desktop version of Excel and in Excel for the web. If you want to understand the routine behind it first — cash or accrual, separate accounts, which records to keep — start with our small business bookkeeping guide and come back here to build the file.

What the template will do

• Record every sale and expense in one place, with the same fields every time.
• Let you pick categories and accounts from a drop-down list, so spelling never splits one category into two.
• Total income and expenses by category for each month automatically.
• Show gross profit and net profit per month.
• Flag when your records and the bank statement do not agree.

What it will not do: payroll, inventory valuation, sales tax returns or multi-currency accounts. If you need those, accounting software is usually a better fit.

Step 1: Create the Settings sheet

Open a blank workbook and rename the first sheet Settings. This sheet holds the lists the rest of the file depends on.

1. In cell A1 type Category, in B1 Type. Below them list your categories and whether each is Income, COGS (cost of goods sold), Expense or Not P&L (owner draws, transfers, loan principal).
2. Select the list and press Ctrl+T (Format as Table). Name the table tblCategories on the Table Design tab.
3. A few columns to the right, create a second small table of your accounts — for example Business checking and Business card — with a column for each account’s opening balance and the date of that balance. Name it tblAccounts.

A starting category list might look like this. Keep it short; you can add rows later and every formula will pick them up.

Give the lists names for the drop-downs

Excel’s data validation does not accept table references typed straight into the list box, so give each column a name first. Go to Formulas > Define Name, name it CategoryList and set Refers to to =tblCategories[Category]. Do the same for the account column as AccountList. Because the names point at table columns, new categories appear in the drop-down as soon as you add them.

Step 2: Build the Transactions sheet

Add a sheet named Transactions and type these headers in row 1:

Select the headers and press Ctrl+T, then name the table tblTx. Using two amount columns instead of one signed column keeps entry simple: you never have to remember whether an expense is negative.

Add the drop-down lists

1. Select the Category column of the table (click just above its header until the column highlights).
2. Go to Data > Data Validation, choose Allow: List and enter =CategoryList as the source.
3. Repeat for the Account column with =AccountList.
4. For the two amount columns, use Allow: Decimal, greater than or equal to 0, so a negative number cannot slip in.

Fill the Type column automatically

In the first row of the Type column enter:

=IFERROR(XLOOKUP([@Category], tblCategories[Category], tblCategories[Type]), "")

The table copies the formula down to every new row. If your version of Excel does not have XLOOKUP, use =IFERROR(INDEX(tblCategories[Type], MATCH([@Category], tblCategories[Category], 0)), "") instead.

Step 3: Build the monthly Summary sheet

Add a sheet named Summary. In cell B1 enter the first day of your financial year, for example 1/1/2026. In B3 enter =B1, in C3 =EDATE(B3,1), and copy C3 across to M3 so row 3 shows the first day of each of the twelve months. Format row 3 as mmm yyyy.

In column A, from row 4 down, list your income, COGS and expense categories in the same order as on the Settings sheet. Then in B4 enter:

=SUMIFS(tblTx[Money in], tblTx[Category], $A4, tblTx[Date], ">="&B$3, tblTx[Date], "<="&EOMONTH(B$3,0)) - SUMIFS(tblTx[Money out], tblTx[Category], $A4, tblTx[Date], ">="&B$3, tblTx[Date], "<="&EOMONTH(B$3,0))

Copy it across the months and down the categories. The dollar signs lock the category column and the month row, so every cell reads the right pair. Income rows come out positive and cost rows negative, which is exactly what you want for the totals below. A refund you issue, recorded as money out under an income category, correctly reduces that month’s income.

Add the profit lines

Below the category rows, add these totals. Each uses SUMIFS over the Type column, so you never have to update a range when you add a category:

For example, the revenue cell for January is =SUMIFS(tblTx[Money in], tblTx[Type], "Income", tblTx[Date], ">="&B$3, tblTx[Date], "<="&EOMONTH(B$3,0)) - SUMIFS(tblTx[Money out], tblTx[Type], "Income", tblTx[Date], ">="&B$3, tblTx[Date], "<="&EOMONTH(B$3,0)). Change Income to COGS or Expense and swap the order of the two SUMIFS for the cost rows. Add a final column N with =SUM(B4:M4) for the year to date.

Rows of type Not P&L — owner draws, transfers, loan principal — are left out of profit on purpose. They move cash, but they are not income or expenses. Once the profit lines are in place, our guide on how to read a profit and loss statement explains what to look for each month.

Step 4: Add a reconciliation check

This is the part most homemade templates skip, and it is the one that catches mistakes. On the Summary sheet, or a small sheet of its own, make one row per account:

The book balance formula for the account named in A20, with the statement date in C20 and the opening balance in B20, is:

=B20 + SUMIFS(tblTx[Money in], tblTx[Account], A20, tblTx[Date], "<="&C20) - SUMIFS(tblTx[Money out], tblTx[Account], A20, tblTx[Date], "<="&C20)

Apply conditional formatting to the Difference column so any value other than zero turns red. When it is red, look for a missing transaction, a duplicate, or an amount typed into the wrong column.

Record a transfer between two of your accounts as two lines: money out of one account and money in to the other, both under the Transfer between accounts category. Each account then reconciles on its own, and the transfer has no effect on profit.

Step 5: Protect the formulas and keep backups

• Lock formula cells. Select the Summary sheet, unlock only the cells you type into (Format Cells > Protection), then use Review > Protect Sheet. A password is optional; the goal is to stop accidental overwrites.
• Freeze the header row on the Transactions sheet with View > Freeze Panes so the column names stay visible.
• Save one file per year, named with the year, and carry each account’s closing balance forward as the next year’s opening balance.
• Back up to a second location — cloud storage or an external drive — at least once a month, right after you reconcile.

Common formula problems and fixes

Using the template in Google Sheets

The same layout works in Google Sheets: SUMIFS, EDATE, EOMONTH and drop-down lists are all available. The main difference is the references. Use plain column ranges such as Transactions!F2:F instead of the tblTx[...] references shown here, and point the drop-down at the category range on the Settings sheet.

Frequently asked questions

Is an Excel bookkeeping template good enough for a small business?

For many freelancers and small businesses with a modest number of transactions, no payroll and no inventory to value, yes. What matters is that every transaction is recorded, categorized the same way, reconciled with the bank and backed by receipts. When you add employees, sales tax or several bank feeds, accounting software usually saves time. Our comparison of a bookkeeping spreadsheet vs accounting software lists the signs it is time to switch.

Should I use one amount column or two?

Two columns — money in and money out — are easier to enter correctly and easier to check. A single signed column works too, but every expense must be entered as a negative number, which is a common source of errors.

Can the template handle accrual accounting?

Partly. Recording the invoice date instead of the payment date gives you income when earned, but you would also need to track unpaid invoices and bills separately. If you use the accrual method, ask your tax professional whether a spreadsheet is enough for your situation.

How should I name my expense categories?

Sole proprietors report business expenses on Schedule C, so naming categories so they map onto its expense lines makes year-end easier. Keep the list short and use the same category for the same kind of cost all year.

Skip the build

We are preparing a ready-made Small Business Income and Expense Tracker for Excel built on this structure, with a transactions sheet, a monthly profit and loss view and a dashboard. It is marked as coming soon and is not on sale yet. For the routine that keeps any template accurate — weekly entry, receipts, monthly reconciliation — see the small business bookkeeping guide, and for more topics browse small business finance.