Bank Statement to General Ledger: Posting Guide

TL;DR Every line on a bank statement becomes a two-sided entry in the general ledger: one side hits the bank account, the other hits the account that explains why the money moved, such as sales, an expense, a loan, owner's equity or another bank account. To post from bank statements in Excel, convert each statement to one row per transaction, reconcile it to the closing balance, assign each row a GL account from your chart of accounts, then generate debit and credit columns from the signed amount. Treat transfers, loan repayments, owner contributions and drawings as balance sheet movements, not income or expenses. Finish with a trial balance check: total debits must equal total credits, and the bank account's ledger balance must equal the statement balance.

Plenty of small businesses, clubs and side projects keep their books from bank statements, either because they don't use accounting software or because they're catching up after months of not doing it. Even with software, understanding how statement lines become ledger entries makes you much better at spotting mistakes, because most bookkeeping errors are really posting errors.

This guide walks through the process from a converted statement to a working general ledger in Excel: what a ledger is, how to map transactions to accounts, the double-entry logic, a worked example with a trial balance, how to handle the awkward items, and how to move the result into accounting software. It's written for small businesses and their bookkeepers, using cash-basis bookkeeping driven by bank activity. If you need accrual accounts, the same ledger is a starting point, with adjustments on top.

What the general ledger is

The general ledger is the complete record of every financial transaction, organised by account. Each account, such as Bank, Sales, Rent or Loan, has its own running total. The chart of accounts is the list of those accounts, usually numbered and grouped:

The exact numbering doesn't matter; consistency does. If you don't have a chart of accounts yet, start small. Ten to twenty accounts are enough for most small businesses, and you can split them later.

Double entry means every transaction affects at least two accounts, with equal debits and credits. For a bank account:

• Money in is a debit to Bank and a credit to something else, for example Sales.
• Money out is a credit to Bank and a debit to something else, for example Rent.

You don't need to love debits and credits to do this well. You only need to answer one question for each bank line: what is the other side?

Step 1: convert and reconcile the statements

Start with one row per transaction and a single signed Amount column, money in positive and money out negative.

• If you have CSV exports, use them, keeping the PDFs for reference.
• If you have PDFs, convert them. SheetStatement produces one row per transaction and checks the opening and closing balances. The free plan includes 3 pages a month; Starter ($9), Pro ($19) and Business ($39) monthly plans cover more.

Reconcile each statement before posting:

=ROUND(Opening + SUM(Amount) - Closing, 2)

It must be zero. Posting an incomplete statement creates a ledger that can never agree with the bank. See reconciling a bank statement in Excel for the full method.

Step 2: assign a GL account to every row

Add a column for the GL account, the "other side" of each transaction. This is the judgement step.

Speed it up with rules. A lookup table of description keywords and GL accounts, with a formula that searches for each keyword, handles recurring items automatically:

=IFERROR(LOOKUP(2^15, SEARCH(Rules[Keyword], B2), Rules[GL account]), "REVIEW")

This returns the GL account for the last keyword in the table that appears in the description, or "REVIEW" if none does. Our guide to Excel formulas for categorising transactions explains this pattern and alternatives in detail, and categorising business expenses helps you decide which accounts to use.

Every "REVIEW" row needs a human decision. Don't skip them; an unposted row means the ledger won't balance.

Step 3: generate debits and credits

For each bank row, you need two ledger lines: one for the bank account and one for the GL account. With the signed Amount in column C:

Bank line

Account: 1000 Bank – current
Debit:   =IF(C2>0, C2, 0)
Credit:  =IF(C2<0, -C2, 0)

Other side

Account: [GL account from column D]
Debit:   =IF(C2<0, -C2, 0)
Credit:  =IF(C2>0, C2, 0)

In practice, the simplest way to build this in Excel is to create a "Journal" table where each bank row appears twice, once for each side. In Excel 365 you can stack them with VSTACK; in older versions, copy the bank rows into the journal table twice and fill the formulas.

Each journal line should carry the date, description, a reference back to the bank row (or the source statement and line number), the account, and the debit or credit amount.

Step 4: build the ledger and trial balance

With all journal lines in one table, the ledger for any account is just a filter on the Account column, sorted by date, with a running balance.

The trial balance summarises every account:

Debits:  =SUMIFS(Journal[Debit], Journal[Account], A2)
Credits: =SUMIFS(Journal[Credit], Journal[Account], A2)
Balance: =B2 - C2

Two checks prove the work:

1. Total debits equal total credits. =SUM(Journal[Debit]) - SUM(Journal[Credit]) must be zero. If it isn't, a journal line is missing its other side.
2. The bank account balance equals the statement. Opening balance plus the Bank account's net movement must equal the statement's closing balance. If the first check passes and this one fails, a bank line was posted to the wrong account.

Worked example

A small consultancy's first month, with one current account and one savings account. All figures are invented.

Current account opening balance: $5,000.00 (posted as an opening entry against 3000 Owner's capital). Closing balance: $6,071.10.

Sum of amounts: 4,200.00 − 900.00 − 42.00 − 1,000.00 + 1,800.00 − 650.00 − 2,000.00 + 700.00 − 1,020.00 − 16.90 = 1,071.10.

Statement check: 5,000.00 + 1,071.10 − 6,071.10 = 0.00.

Trial balance after posting (including the opening entry):

Bank – current debits are the opening 5,000.00 plus receipts of 6,700.00 (4,200.00 + 1,800.00 + 700.00); credits are the payments of 5,628.90. Net: 11,700.00 − 5,628.90 = 6,071.10, matching the statement.

Debits equal credits, and the bank agrees. Now look at what the profit and loss shows: sales of $6,000.00 against expenses of $1,978.90. If you'd treated every outgoing line as an expense, you'd have reported expenses of $5,628.90, wrongly including the savings transfer, the loan repayment and the owner's draw. If you'd treated every incoming line as sales, the owner's $700 contribution would have inflated revenue.

The loan line is simplified here: in a real ledger the loan account would also carry the outstanding balance from when the loan was received, so the repayment would reduce a credit balance. In reality, a loan repayment usually includes interest, which is an expense. Split the line using the lender's statement: principal to 2300 Bank loan, interest to an interest expense account.

The awkward items

Transfers between your own accounts. Both sides are bank accounts. If you post from both statements, the transfer appears twice: once as money out of current (other side: savings) and once as money in to savings (other side: current). That double-counts. The usual fix is a transfer clearing account: post both sides to it, and its balance returns to zero once both legs are posted. A non-zero balance flags a transfer you've only posted on one side, or one that's in transit at month-end.

Credit card repayments. A payment from your bank to your business credit card is a transfer to a liability account, not an expense. The expenses are the individual card purchases, posted from the card statement. See credit card reconciliation in Excel.

Net deposits from payment processors. A deposit from Stripe, Shopify or a card terminal is usually sales minus fees. Post the gross sales and the fees separately, with a clearing account for the processor. Our guides to reconciling Stripe payouts and Shopify payouts show how.

Owner contributions and drawings. Equity movements, not income or expenses. For a limited company, drawings may instead be salary, dividends or a director's loan, each with different treatment. Ask your accountant.

Loan proceeds. Money received from a loan is a liability, not income.

Tax payments. Payments to tax authorities are generally liabilities being settled, or in some cases expenses, depending on the tax. Use a separate account for each tax and confirm the treatment with your accountant.

Refunds. A supplier refund goes to the same expense account as the original purchase, reducing it. A refund to a customer reduces sales.

Keeping an audit trail

A ledger built in Excel is only as trustworthy as your ability to trace each number back to its source. A few conventions make that easy:

• A unique ID for every bank row. Something like CUR-2026-03-007 (account, year, month, line number). Both journal lines generated from that row carry the same ID, so you can always find the pair.
• The source file name. Keep the name of the statement PDF on every row. When your accountant asks where a figure came from, you can open the right page in seconds.
• No hard-coded edits in the journal. If a GL account is wrong, fix it on the bank row and let the journal regenerate. Typing over a formula in the journal breaks the link between the bank data and the ledger.
• A notes column for judgement calls. "Split per loan statement", "Owner confirmed personal", "Refund of 06/02 purchase". Short notes save long conversations later.
• Lock closed periods. Once a month is reconciled and reviewed, protect that range or move it to a separate sheet. Accidental edits to closed months are a common source of opening balance differences.

These habits matter most if your books will be reviewed by someone else: an accountant at year-end, a lender, a buyer of the business or a tax authority. Our checklist on preparing bank statements for an audit covers what reviewers typically look for.

Posting several accounts in one workbook

Most businesses have more than one account: a current account, a savings account, a credit card, perhaps a payment processor balance. Keep them in one workbook with a shared chart of accounts and a single journal table, but a separate bank data sheet for each account.

Each account's sheet has its own opening balance, its own reconciliation check and its own bank GL code (1000 for current, 1010 for savings, 2100 for the card). The journal pulls from all of them. The trial balance then shows every account at once, and the transfer clearing account tells you immediately whether transfers between them have been posted on both sides.

If you're combining a year of statements per account first, our guide to combining multiple bank statements in Excel shows how to stack them while keeping the source of each row.

Cash basis versus accrual

Posting from bank statements naturally gives cash-basis books: income when it's received, expenses when they're paid. That's fine for many small businesses and for management purposes.

If you need accrual-basis accounts, the bank-driven ledger is still the foundation. You then add adjusting entries at period-end: receivables for invoices issued but unpaid, payables for bills received but unpaid, prepayments and accruals. Those come from invoices and contracts, not the bank statement. Which basis you're required to use depends on your business type and jurisdiction; check with your accountant.

Moving the ledger into accounting software

If you later adopt accounting software, you have two choices:

• Import the bank statements and categorise them inside the software, using your Excel GL assignments as a guide. This is the usual route. See importing bank statements into QuickBooks Online or the Xero CSV import format.
• Post opening balances from your trial balance at a cut-over date, and start fresh from there.

Either way, your Excel ledger is a useful audit trail, because every software balance can be traced back to statement lines.

A monthly routine

1. Convert and reconcile each bank and card statement.
2. Assign a GL account to every row; clear all "REVIEW" items.
3. Generate journal lines and check debits equal credits.
4. Check each bank account's ledger balance against its statement.
5. Check the transfer clearing account is zero, or explained.
6. Review the profit and loss for oddities, such as a negative expense or an unusually large month.

This fits into a broader month-end close checklist.

Start from clean statement data

Good ledgers start with complete, reconciled statements. Upload your bank statement PDFs to SheetStatement, confirm the balances, and export to Excel ready for GL coding. Try 3 pages free each month.

FAQ

How do I post a bank statement to the general ledger?

Each statement line becomes a debit and a credit: one side to the bank account, the other to the account explaining the transaction, such as sales, an expense, a loan or equity.

Is money in a debit or a credit?

In your books, money into the bank is a debit to the bank account. Your bank's statement uses the opposite language because, from its point of view, your balance is money it owes you.

How do I handle transfers between my accounts?

Post both legs to a transfer clearing account. When both statements are posted, the clearing account returns to zero.

Are owner drawings an expense?

No. Drawings and contributions are equity movements. For companies, owner withdrawals may be salary, dividends or a director's loan, so check the treatment with your accountant.

Why don't my debits equal my credits?

A journal line is missing its other side, or a formula is pointing at the wrong row. Check that every bank row produced exactly two journal lines.

Can I keep a general ledger in Excel?

Yes, for a small business it's workable, especially with a reconciled bank statement as the source. As volume grows, accounting software becomes easier.

Is cash-basis bookkeeping acceptable?

It depends on your business and jurisdiction. This is general information, not accounting or tax advice; confirm with an accountant.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro is $19 and Business is $39.