📊

Excel Bookkeeping Template: What Your Spreadsheet Needs

Q: Is a spreadsheet good enough for small business bookkeeping?

For a sole proprietor or small LLC with one or two accounts and simple income, often yes. The IRS lets you use any recordkeeping system that clearly shows your income and expenses; the spreadsheet just has to be complete and backed by statements and receipts.

Q: Should my bookkeeping spreadsheet be cash basis or accrual?

A spreadsheet works most naturally on a cash basis, recording income when it lands in the bank and expenses when they leave. Tracking unpaid invoices and bills, which accrual needs, is where accounting software earns its keep.

Q: Does the same template work in Google Sheets?

Yes. Every formula in this layout, including SUMIFS, EOMONTH and the data validation dropdown, exists in Google Sheets, and tab references work the same way.

Q: How do I handle a business credit card in the spreadsheet?

Treat it as its own bank account in the Bank column. Purchases go to expense codes, and the payment from checking to the card is a transfer, coded to a transfer account so it is never counted as an expense.

6 min readOctober 26, 2026

Quick Answer: A small business bookkeeping spreadsheet needs four tabs: a chart of accounts listing every income and expense code, a cash-basis ledger with one row per bank or card transaction, an import tab where raw statement rows land before they are coded, and a monthly summary built with SUMIFS that turns codes into a profit and loss. Add a reconciliation check that ties each account to its statement balance.

A bookkeeping spreadsheet is a ledger plus rules. The ledger holds every transaction; the rules say which account each one belongs to and how they total up. Download any free template and you'll find versions of the same four parts. Building them yourself takes an hour, and you'll understand exactly where every number in your summary came from.

Drop your bank statement here

PDF or OFX · Max 4.4 MB

Uploaded over an encrypted connection. We never ask for your bank login. Privacy

Preview free with no account. Free account to download.

What tabs does a bookkeeping template need?

Tab	What it holds	Why it matters
`Accounts`	Your chart of accounts: code, name, type, tax line	Keeps categories consistent and makes totals possible
`Ledger`	One row per transaction, coded	The book itself
`Import`	Raw rows from bank and card statements	Keeps messy source data out of the ledger
`Summary`	Monthly totals by account	Your profit and loss
`Rec`	Statement balances against ledger balances	Proves nothing is missing

The IRS's recordkeeping page says you may choose any recordkeeping system suited to your business that clearly shows your income and expenses. A spreadsheet qualifies, as long as the rows are complete and you keep the supporting documents.

How do you set up the chart of accounts tab?

Give every account a numeric code so it sorts and can't be misspelled. A common pattern uses the first digit for the type:

Code	Name	Type	Notes
1000	Business checking	Asset	One code per bank account
1010	Business savings	Asset
2000	Business credit card	Liability
3000	Owner contributions	Equity	Money you put in
3100	Owner draws	Equity	Money you take out
4000	Sales	Income
4100	Service income	Income
4900	Refunds given	Income	Negative income
5000	Cost of goods sold	Expense	For product businesses
6000	Advertising	Expense
6100	Software and subscriptions	Expense
6200	Office supplies	Expense
6300	Professional fees	Expense
6400	Bank and card fees	Expense
6500	Travel	Expense
6600	Meals	Expense
6900	Other expenses	Expense	Review monthly
9000	Transfers between accounts	Transfer	Never income or expense

Add a fifth column for the tax form line each account rolls up to, if you file a Schedule C; that turns year-end into a single SUMIFS. Our chart of accounts guide explains how to choose accounts for your type of business.

What columns does the ledger need?

Make Ledger an Excel table (Ctrl+T) named Ledger. These columns cover a cash-basis book:

Column	Header	Content
A	TxnDate	Date the bank posted it
B	Ref	Check number, invoice number or statement reference
C	Bank	Account code the money moved through, such as 1000 or 2000
D	Memo	The bank's description, plus your note
E	Code	The account it belongs to, from `Accounts`
F	Received	Money in, as a positive number
G	Paid	Money out, as a positive number
H	Net	`=[@Received]-[@Paid]`
I	Type	`=XLOOKUP([@Code],Accounts!A:A,Accounts!C:C)`
J	Doc	Y once the receipt or invoice is filed

Put a dropdown on column E that points at the codes in Accounts, so every row uses a real code. Separate Received and Paid columns are easier to read than a signed amount, and the Net column gives you a signed figure for the formulas.

How do bank statements get into the ledger?

This is where most spreadsheet books fall behind. Typing from paper statements is slow and introduces errors, so give the raw data its own tab.

Get the rows. Download the bank's CSV if it offers one. If you have PDF statements, QuickBankConvert turns them into spreadsheet rows with date, description and amount, and shows the opening and closing balance for the period; it doesn't need your bank login. A free account covers a few statements a month.
Paste into Import. Keep the bank's columns as they come.
Map to the ledger. In the ledger, reference the import or paste values: date to TxnDate, description to Memo, positive amounts to Received, negative amounts to Paid, using =MAX(0,amount) and =MAX(0,-amount).
Code each row. Pick the account in column E. Payments to your own card and moves to savings go to 9000.
Clear Import once the rows are in the ledger.

Our bookkeeping data entry guide has more on coding speed and consistency.

How does the monthly summary work?

On Summary, list income and expense codes down column A and their names in B. Put the first day of each month across row 3, starting in C3 (1 Jan 2026, 1 Feb 2026, and so on). In C5:

=SUMIFS(Ledger[Net], Ledger[Code], $A5, Ledger[TxnDate], ">="&C$3, Ledger[TxnDate], "<="&EOMONTH(C$3,0))

Fill across and down. Income codes show positive totals and expense codes show negative ones. Below the list:

Total income: =SUMIFS(C5:C40, $A5:$A40, ">=4000", $A5:$A40, "<5000")
Total expenses: =SUMIFS(C5:C40, $A5:$A40, ">=5000", $A5:$A40, "<9000")
Net profit: total income plus total expenses, for example =C42+C43 if those two totals sit in rows 42 and 43

Microsoft's SUMIFS reference covers the argument order; the range you add comes first. Transfers (9000) and owner equity (3000s) are left out on purpose, because they aren't profit or loss.

How do you prove the books are complete?

On Rec, one row per bank account per month:

Column	Formula or source
Statement opening balance	From the statement
Ledger movement	`=SUMIFS(Ledger[Net], Ledger[Bank], 1000, Ledger[TxnDate], ">="&B2, Ledger[TxnDate], "<="&EOMONTH(B2,0))`
Expected closing	Opening plus movement
Statement closing balance	From the statement
Difference	Expected minus actual; should be zero

For a credit card the sign is reversed, since purchases increase what you owe. A difference that isn't zero means a missing or duplicated row; find it before you trust the summary. The bank reconciliation guide covers timing differences such as uncleared checks.

How do you adapt the template for your kind of business?

The structure stays the same; the chart of accounts changes. A few common adjustments:

Restaurant or cafe. Split cost of goods sold into food, beverages and packaging, and add codes for card processing fees and delivery-app commissions, since platforms usually pay out net of their cut. Record the gross sale and the fee separately so the summary shows both.
Salon or other appointment business. Add codes for booth rent received or paid, product sales versus service income, and tips if they pass through the business account.
eBay or other marketplace seller. Payouts arrive net of fees and refunds. Keep a code for marketplace fees and one for shipping labels, and match each payout to the platform's own payout report rather than coding the deposit as a single sale.
Airbnb host. Treat each property as a tag in an extra Property column, add codes for cleaning, supplies and platform fees, and record payouts the same way as a marketplace seller.
Sole trader in the UK. The layout works the same way, but name the tax column after the boxes on your self-assessment form rather than a Schedule C line.

Whatever the business, resist adding a code for every vendor. Codes describe what money was for; the Memo column already says who it went to.

When should you move from a spreadsheet to software?

A spreadsheet stops being the right tool when one of these is true:

You send invoices and need to track who owes you, or you need to track bills you owe.
You run payroll or collect sales tax.
More than one person needs to enter transactions at once.
An accountant or lender wants reports you are rebuilding by hand each time.

At that point QuickBooks Online, Xero or Wave will save time. The coded ledger you built moves with you: your chart of accounts becomes theirs, and your statement files can be imported as CSV, or as QBO for QuickBooks on a paid plan. See the full bookkeeping routine for what to do each month either way.

Frequently Asked Questions

Is a spreadsheet good enough for small business bookkeeping?

For a sole proprietor or small LLC with one or two accounts and simple income, often yes. The IRS lets you use any recordkeeping system that clearly shows your income and expenses; the spreadsheet just has to be complete and backed by statements and receipts.

Should my bookkeeping spreadsheet be cash basis or accrual?

A spreadsheet works most naturally on a cash basis, recording income when it lands in the bank and expenses when they leave. Tracking unpaid invoices and bills, which accrual needs, is where accounting software earns its keep.

Does the same template work in Google Sheets?