Double Entry Bookkeeping in Excel: A Free Spreadsheet Setup

Quick Answer: A free double-entry bookkeeping spreadsheet in Excel needs four tabs: a chart of accounts, a journal where every transaction is entered as at least one debit and one credit of equal total, a ledger that sums each account with SUMIFS, and a trial balance that proves total debits equal total credits. You can build it in an hour with no template, and the journal checks itself as you type.

Double-entry bookkeeping records every transaction twice: once as a debit to one account and once as an equal credit to another. Excel handles this well with a journal tab for entries, SUMIFS formulas that roll entries up into account balances, and a trial balance that flags any entry that doesn't balance. The result is a free, transparent ledger you control completely.

What makes bookkeeping "double entry"?

The IRS describes the method in Publication 583, Starting a Business and Keeping Records: a double-entry system "uses journals and ledgers." Transactions are first entered in a journal and then posted to ledger accounts showing income, expenses, assets, liabilities and net worth. Each account "has a left side for debits and a right side for credits," and the system "is self-balancing because you record every transaction as a debit entry in one account and as a credit entry in another."

The same publication describes single-entry as the simpler alternative, "based on the income statement," recording the flow of income and expenses. It notes that you may find double-entry better "because it has built-in checks and balances to assure accuracy and control."

Single entry or double entry: which are you keeping now?

Most people who start bookkeeping in Excel begin with a single-entry cash book without calling it that: one row per bank transaction, a category column, and totals by category.

If all you need is income and expenses for a tax return, a single-entry sheet can be enough. Double entry starts paying off once you owe money, are owed money, or own equipment, because those balances have nowhere to live in a cash book.

Tab 1: the chart of accounts

List every account with a number, a name, a type and its normal balance. Keep it short; you can add accounts later.

Format the range as an Excel table (Home > Format as Table, per Microsoft's table instructions) and name it Accounts. Then add a data validation drop-down on the journal's Account column that points at this list, so you can't post to a misspelled account. For help choosing accounts, see the small business chart of accounts guide.

Tab 2: the journal

The journal is the only tab you type into. Columns:

Entry | Date | Account | Debit | Credit | Memo

Each transaction gets one entry number and two or more lines. Three examples:

Notice that the laptop never touched the bank. A cash book would miss it until the card was paid; the journal records the asset and the debt on the day of purchase.

Make each entry check itself

Format the journal as a table named Journal and add a column Out of balance with:

=SUMIFS([Debit],[Entry],[@Entry])-SUMIFS([Credit],[Entry],[@Entry])

It shows zero for every line of a balanced entry. Add conditional formatting that turns the cell red when it is not zero, and you will catch a mistyped amount before it spreads. Microsoft's SUMIFS reference explains the argument order: the range to sum comes first, followed by pairs of criteria ranges and criteria.

Tab 3: the ledger

The ledger tab lists each account from the chart and computes its balance from the journal:

• Total debits: =SUMIFS(Journal[Debit],Journal[Account],A2)
• Total credits: =SUMIFS(Journal[Credit],Journal[Account],A2)
• Balance: debits minus credits for debit-normal accounts, credits minus debits for credit-normal accounts. Use an IF on the normal balance column so every balance shows as a positive number in its natural direction.

To see the detail behind one account, filter the journal table on that account. Add a date criterion to the SUMIFS (Journal[Date],"<="&$B$1) and the whole ledger becomes "as of" whatever date you type into B1.

How do you check the books balance?

The trial balance tab puts every account in one list with its balance in a Debit column or a Credit column, then totals both. If total debits equal total credits, every entry was balanced. Use a cell that shows =ROUND(SUM(Debit)-SUM(Credit),2) and expect 0.

A balanced trial balance does not prove the books are right. It won't catch:

• An entry posted to the wrong account (Bank fees instead of Software).
• A transaction never entered at all.
• The same entry entered twice.
• Debit and credit swapped on both lines.

That is why the checking account in your spreadsheet should still be reconciled to the bank statement each month: the ledger balance for 1000 Checking, adjusted for items in transit, must match the statement. The bank statement reconciliation guide walks through it.

From the trial balance, a profit and loss is just the income and expense rows, and a balance sheet is the asset, liability and equity rows plus the year's net profit.

Getting bank transactions into the journal without retyping

Most entries in a small business involve the bank, and each bank line already supplies one side: the checking account. You only decide the other side.

1. Get the statement as a spreadsheet. Use the bank's CSV export if it offers one, or convert the statement PDF to Excel; QuickBankConvert produces rows of date, description, category and signed amount, with money out negative.
2. Add a column Offset account and fill in the other side for each row (Sales, Bank fees, Credit card payable, and so on).
3. For each row, build two journal lines: Checking debited when the amount is positive (money in) and credited when negative; the offset account takes the opposite side.

With a helper sheet, step 3 can be formulas rather than typing: one row per bank line, with Debit =MAX(Amount,0) and Credit =MAX(-Amount,0) for the checking line, swapped for the offset line. Paste the results into the journal as values.

Bookkeepers doing this for several clients usually move to accounting software sooner; the bookkeepers page describes how statement conversion fits that workflow.

When a spreadsheet stops being enough

Excel double entry works well until one of these appears: more than a few hundred entries a month, several people editing, sales tax filings, payroll, or invoices you need to track by customer. At that point software that enforces balanced entries and produces reports automatically saves more time than it costs. Until then, a four-tab workbook teaches you exactly where every number comes from, which makes the later switch easier.

Related guides

• Bookkeeping spreadsheet template
• Debits and credits in a bank reconciliation
• Bookkeeping for small business