General Ledger Template in Excel: Chart of Accounts, Running Balances and Trial Balance (With Example)
How to build a general ledger template in Excel: a chart of accounts, a double-entry journal table, running balances per account with SUMIFS, an automatic trial balance and checks that catch errors, with a worked month of transactions for a small service business.
Quick answer: a general ledger (GL) template in Excel is one table where every transaction is recorded as a double entry: at least one debit and one credit of equal value, each posted to an account from your chart of accounts. From that single table, formulas produce a running balance for each account and a trial balance that proves total debits equal total credits. The trial balance then feeds your profit and loss statement and balance sheet.
The minimum columns are: Date, Entry #, Account #, Account name, Description, Debit, Credit and Balance. This guide shows how to set them up, which formulas to use and how to check your work, using a fictional month of transactions.
When a general ledger spreadsheet makes sense
A simple bookkeeping template (money in, money out) is enough for many sole proprietors. A general ledger becomes useful when you need to track things that aren’t cash yet: invoices customers haven’t paid, bills you owe, equipment you own, loans and owner contributions. It is also the bridge between day-to-day records and real financial statements. If you are deciding whether to stay in Excel at all, see bookkeeping spreadsheet vs accounting software.
Step 1: Create a chart of accounts
On a sheet called Accounts, list every account with a number, a name, a type and its normal balance. A common numbering pattern:
| Range | Type | Normal balance | Examples |
|---|---|---|---|
| 1000–1999 | Assets | Debit | 1000 Cash, 1100 Accounts receivable, 1500 Equipment |
| 2000–2999 | Liabilities | Credit | 2000 Accounts payable, 2100 Credit card, 2500 Loan |
| 3000–3999 | Equity | Credit | 3000 Owner’s equity, 3100 Owner’s draws |
| 4000–4999 | Revenue | Credit | 4000 Service revenue, 4100 Product sales |
| 5000–6999 | Expenses | Debit | 5000 Cost of goods sold, 6100 Rent, 6200 Software |
Format the list as an Excel table named Accounts. Keep it short at first; you can add accounts later without breaking anything. Owner’s draws reduce equity, so they carry a debit balance even though they sit in the equity range.
Step 2: Build the journal table
On a sheet called Ledger, create a table named GL with these columns:
| Column | Purpose | Tip |
|---|---|---|
| Date | Transaction date | Data validation: dates only |
| Entry # | Groups the debit and credit lines of one transaction | Same number on every line of the entry |
| Account # | From the chart of accounts | Data validation list pointing at Accounts[Number] |
| Account name | Looked up automatically | =XLOOKUP([@[Account '#]],Accounts[Number],Accounts[Name],"CHECK") |
| Description | What happened | Customer, vendor or invoice number |
| Debit / Credit | Amounts | Only one of the two per line |
| Balance | Running balance for that account | Formula in Step 3 |
The pound sign in a column header has to be escaped with an apostrophe in structured references, which is why the lookup shows [Account '#]. If that feels fiddly, name the column AccountNo instead. In older Excel versions without XLOOKUP, use =IFERROR(VLOOKUP(C2,Accounts,2,FALSE),"CHECK").
Step 3: Running balance per account
Because the ledger mixes all accounts in date order, the running balance has to look only at earlier lines of the same account. An expanding-range SUMIFS does that. In H2, then filled down (assuming Account # in C, Debit in F and Credit in G):
=SUMIFS($F$2:F2,$C$2:C2,C2)-SUMIFS($G$2:G2,$C$2:C2,C2)
This returns debits minus credits to date, which is the natural sign for assets and expenses. For liability, equity and revenue accounts, a negative number simply means a credit balance. If you prefer to see every balance as a positive number, multiply by −1 when the account’s normal balance is credit:
=(SUMIFS($F$2:F2,$C$2:C2,C2)-SUMIFS($G$2:G2,$C$2:C2,C2))*IF(XLOOKUP(C2,Accounts[Number],Accounts[Normal])="Credit",-1,1)
To read one account’s history, filter the table by Account #. The Balance column on the last visible line is the current balance.
Step 4: Worked example — one month for a design studio
| Date | Entry | Account | Description | Debit | Credit |
|---|---|---|---|---|---|
| Mar 1 | 1 | 1000 Cash | Owner investment | 10,000 | |
| Mar 1 | 1 | 3000 Owner’s equity | Owner investment | 10,000 | |
| Mar 3 | 2 | 1500 Equipment | Computer | 2,400 | |
| Mar 3 | 2 | 1000 Cash | Computer | 2,400 | |
| Mar 5 | 3 | 1100 Accounts receivable | Invoice #101 | 4,500 | |
| Mar 5 | 3 | 4000 Service revenue | Invoice #101 | 4,500 | |
| Mar 10 | 4 | 6100 Rent | March rent | 1,200 | |
| Mar 10 | 4 | 1000 Cash | March rent | 1,200 | |
| Mar 15 | 5 | 6200 Software | Design software, billed | 300 | |
| Mar 15 | 5 | 2000 Accounts payable | Design software, billed | 300 | |
| Mar 20 | 6 | 1000 Cash | Partial payment #101 | 3,000 | |
| Mar 20 | 6 | 1100 Accounts receivable | Partial payment #101 | 3,000 | |
| Mar 28 | 7 | 2000 Accounts payable | Pay software bill | 300 | |
| Mar 28 | 7 | 1000 Cash | Pay software bill | 300 | |
| Mar 31 | 8 | 1000 Cash | Workshop fee, paid on the day | 800 | |
| Mar 31 | 8 | 4000 Service revenue | Workshop fee, paid on the day | 800 |
Cash ends the month at 10,000 − 2,400 − 1,200 + 3,000 − 300 + 800 = $9,900. Accounts receivable is 4,500 − 3,000 = $1,500 (the client still owes part of invoice #101). Accounts payable is back to zero.
Step 5: The trial balance
On a sheet called Trial Balance, list each account and pull its total debit-minus-credit balance from the ledger:
=SUMIFS(GL[Debit],GL[Account '#],A2)-SUMIFS(GL[Credit],GL[Account '#],A2)
Then split positive results into a Debit column and negative results into a Credit column: =MAX(C2,0) and =MAX(-C2,0).
| Account | Debit | Credit |
|---|---|---|
| 1000 Cash | 9,900 | |
| 1100 Accounts receivable | 1,500 | |
| 1500 Equipment | 2,400 | |
| 2000 Accounts payable | 0 | |
| 3000 Owner’s equity | 10,000 | |
| 4000 Service revenue | 5,300 | |
| 6100 Rent | 1,200 | |
| 6200 Software | 300 | |
| Total | 15,300 | 15,300 |
Debits equal credits, so the books are in balance. Revenue of $5,300 minus expenses of $1,500 gives a net income of $3,800 for March, which is exactly what your profit and loss statement should show. The asset, liability and equity lines (plus that net income) form the balance sheet: assets of $13,800 = liabilities of $0 + equity of $10,000 + net income of $3,800.
Step 6: Built-in error checks
- Whole-ledger check:
=IF(SUM(GL[Debit])=SUM(GL[Credit]),"OK","OUT OF BALANCE") - Per-entry check: add a column EntryDiff with
=SUMIFS([Debit],[Entry '#],[@[Entry '#]])-SUMIFS([Credit],[Entry '#],[@[Entry '#]])and highlight any non-zero value with conditional formatting. This tells you exactly which entry is broken. - Unknown accounts: the lookup returns “CHECK” when an account number isn’t in the chart of accounts.
- Both columns filled: flag lines where Debit and Credit are both above zero:
=AND([@Debit]>0,[@Credit]>0). - Cash reconciliation: compare the Cash account balance with your bank statement at month-end. A trial balance can balance and still be wrong if a transaction was posted to the wrong account, so reconciliation matters.
Common debit and credit pairs
| Transaction | Debit | Credit |
|---|---|---|
| Send an invoice | Accounts receivable | Revenue |
| Customer pays an invoice | Cash | Accounts receivable |
| Cash sale | Cash | Revenue |
| Receive a supplier bill | Expense (or inventory) | Accounts payable |
| Pay a supplier bill | Accounts payable | Cash |
| Owner takes money out | Owner’s draws | Cash |
| Loan payment | Loan (principal) and interest expense | Cash |
Sales tax collected from customers is usually a liability until you remit it, not revenue. How you record tax, payroll and depreciation depends on your situation, so confirm the setup with your accountant.
Keeping the template fast and tidy
- Keep one table for the whole year; filter or use PivotTables instead of creating a sheet per account.
- Expanding-range formulas recalculate a lot on large ledgers. Past a few thousand lines, consider replacing the running balance with a PivotTable or a balance-by-account summary.
- Lock formula columns so they aren’t overwritten; see how to protect formulas in Excel.
- Close each month: reconcile cash, run the trial balance, then avoid editing prior-month lines. Post corrections as new adjusting entries instead.
Related guide: Accounts Receivable Template for Excel.
Frequently asked questions
What is the difference between a journal and a general ledger?
The journal lists transactions in date order; the general ledger organizes the same postings by account. In this template, one table plays both roles: sort or filter by date to read it as a journal, filter by account to read it as a ledger.
Can I use this template in Google Sheets?
Yes. SUMIFS, XLOOKUP, MAX and IF all exist in Google Sheets. Google Sheets doesn’t use Excel’s structured table references in the same way, so use normal ranges such as $F$2:F2.
Do I need a general ledger if I use accounting software?
Accounting software keeps the general ledger for you. A spreadsheet ledger is still handy for very small businesses, side projects or for learning how double-entry works before switching.
How often should I update it?
Weekly is a good rhythm; at minimum, post everything and reconcile cash before you close each month.
For the bigger picture of keeping records, read small business bookkeeping, and to see how cash moved during the period, use a cash flow statement template. More guides are in Bookkeeping & income tracking.