Excel guide · 6 min read
How to create a general ledger in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Date | Journal | Account | Description | Debit | Credit | Balance | Account | From | To | ||
| 2 | Oct 28, 2026 | J-101 | Cash | Owner investment | 5000 | 5000 | Cash | Nov 1, 2026 | Nov 30, 2026 | |||
| 3 | Oct 28, 2026 | J-101 | Owner's equity | Owner investment | 5000 | -5000 | ||||||
| 4 | Oct 30, 2026 | J-102 | Rent expense | October rent | 1200 | 1200 | ||||||
| 5 | Oct 30, 2026 | J-102 | Cash | October rent | 1200 | 3800 | ||||||
| 6 | Nov 5, 2026 | J-103 | Accounts receivable | Invoice 1001 Lakeview | 2400 | 2400 | ||||||
| 7 | Nov 5, 2026 | J-103 | Sales | Invoice 1001 Lakeview | 2400 | -2400 | ||||||
| 8 | Nov 9, 2026 | J-104 | Supplies | Printer ink and paper | 290 | 290 | ||||||
| 9 | Nov 9, 2026 | J-104 | Postage | Courier | 50 | 50 | ||||||
| 10 | Nov 9, 2026 | J-104 | Cash | Office Depot card | 340 | 3460 | ||||||
| 11 | Nov 12, 2026 | J-105 | Cash | Lakeview pays 1001 | 2400 | 5860 | ||||||
| 12 | Nov 12, 2026 | J-105 | Accounts receivable | Lakeview pays 1001 | 2400 | 0 | ||||||
| 13 | Nov 16, 2026 | J-106 | Accounts receivable | Invoice 1002 Harbor Gym | 1850 | 1850 | ||||||
| 14 | Nov 16, 2026 | J-106 | Sales | Invoice 1002 Harbor Gym | 1580 | -3980 | ||||||
| 15 | Nov 4, 2026 | J-107 | Cash | Cash sale (entered late) | 950 | 6810 | ||||||
| 16 | Nov 4, 2026 | J-107 | Sales | Cash sale (entered late) | 950 | -4930 |
Download this sheet as an Excel practice file (.xlsx)
One table of journal lines
A general ledger in Excel works best as a single list, not a sheet per account. Each row is one line of a journal entry: the date, a journal ID shared by all the lines of that entry, the account, a description, and the amount in either Debit or Credit (never both). The sample has seven journals for a small business's first weeks, including J-104, which spreads one card payment across two expense accounts.
Keeping everything in one table means every report (an account's ledger, a trial balance, a period's activity) is a formula on the same data. Turn it into an Excel table with Ctrl+T so new lines are picked up automatically, and use a drop-down for the account column fed by your chart of accounts, because SUMIFS treats "Cash" and "Cash " as different accounts.
Do I need account numbers?
They help once you have more than a few dozen accounts: numbers sort in the order of the balance sheet and income statement, and they're harder to misspell. Many people put the number and name in one cell (1000 Cash) or keep the number in its own column and look the name up.
Check every journal balances
In double-entry bookkeeping, each journal's debits equal its credits. Check that per journal, not just for the whole ledger: a whole-ledger check tells you something is wrong, a per-journal check tells you where. In Excel 365, one formula lists every journal ID with its difference.
Result: spills 7 rows × 2 columns
| J-101 | 0 |
| J-102 | 0 |
| J-103 | 0 |
| J-104 | 0 |
| J-105 | 0 |
| J-106 | 270 |
| J-107 | 0 |
Every journal shows 0 except J-106, which shows 270: the Harbor Gym invoice was debited 1,850 to receivables but credited 1,580 to sales.
Just the journals that don't balance
LET names the list of IDs once, and FILTER keeps the ones whose debits and credits differ. An empty result (#CALC! in Excel when nothing matches) means every journal balances; wrap it in FILTER's third argument, such as "All balanced", for a friendlier message.
Result: J-106
J-106.
Why 270?
1,850 typed as 1,580 swaps two digits, and a swap of two digits always makes a difference divisible by 9 (270 / 9 = 30). When a difference divides by 9, look for transposed digits first. In older Excel, put =SUMIFS($E$2:$E$16,$B$2:$B$16,B2)-SUMIFS($F$2:$F$16,$B$2:$B$16,B2) next to each line instead and filter for anything that isn't 0.
A running balance per account
Column G shows each account's balance after each line, with debits as positive and credits as negative. The trick is a range that grows as you fill down: E$2:E2 in row 2 becomes E$2:E10 in row 10, so SUMIFS adds only this account's lines so far.
G2 is =SUMIFS(E$2:E2,C$2:C2,C2)-SUMIFS(F$2:F2,C$2:C2,C2), filled down. Accounts that normally carry a credit balance (sales, equity, liabilities) show negative numbers, which is correct: Sales reaches -4,930 at the last line. Multiply by -1 in a report if your readers expect positive sales.
Result: 6810
6,810: the Cash balance after the last cash line (row 15). Running down column G, Cash goes 5,000, 3,800, 3,460, 5,860, 6,810.
The late entry problem
The running balance follows row order, not dates. J-107 is a cash sale on 4 Nov that was entered after the 16 Nov invoice, so row 10 says Cash was 3,460 on 9 Nov when it was really 4,410. Either sort the table by date (Data > Sort, Date, Oldest to Newest) before reading balances, or make the running balance use dates instead of rows. The date version gives the end-of-day balance, so lines on the same date all show the same figure.
Result: 4410
4,410: Cash at the end of 9 Nov, including the 950 cash sale from 4 Nov.
Pull out one account's ledger with FILTER
To see one account the way a paper ledger shows it, put the account name in a cell (J2 = Cash) and FILTER the journal for it. CHOOSECOLS keeps the columns you want (date, journal, description, debit, credit), and SORT puts the result in date order, which also fixes the late entry. Format the first column of the result as a date; FILTER returns the date serial numbers and shows an empty debit or credit as 0.
Result: spills 5 rows × 5 columns
| 46323 | J-101 | Owner investment | 5000 | 0 |
| 46325 | J-102 | October rent | 0 | 1200 |
| 46330 | J-107 | Cash sale (entered late) | 950 | 0 |
| 46335 | J-104 | Office Depot card | 0 | 340 |
| 46338 | J-105 | Lakeview pays 1001 | 2400 | 0 |
Five Cash lines in date order: 28 Oct owner investment 5,000 in, 30 Oct rent 1,200 out, 4 Nov cash sale 950 in, 9 Nov card payment 340 out, 12 Nov Lakeview's 2,400 in.
Excel 2019 or earlier
FILTER and SORT need Excel 2021 or later; CHOOSECOLS needs Excel 365 or Excel 2024. In older versions, use the filter arrows on the Account column (Data > Filter) and sort by date, or build a PivotTable with Account and Date in Rows and Debit and Credit in Values.
Opening balance and activity for a period
Reports cover a period, so put the start and end dates in cells (K2 and L2, 1 to 30 Nov here) and add date conditions to SUMIFS. Everything before the start is the opening balance; everything between the dates is the period's activity; opening + activity = closing.
For Cash: the opening balance is 3,800 (the October investment less October rent), November activity is 950 + 2,400 - 340 = 3,010, and the closing balance is 6,810, the same as the running balance. To filter the account ledger to the period, multiply the conditions inside FILTER: (C2:C16=J2)*(A2:A16>=K2)*(A2:A16<=L2).
Result: 3800
3,800: Cash at the start of 1 Nov. With ">="&K2 and "<="&L2 instead, the same pattern gives the November activity, 3,010.
Check the whole ledger and move on to a trial balance
One last check cell: =SUM(E2:E16)-SUM(F2:F16) should be 0. Here it's 270, the J-106 error, which agrees with the per-journal check. Once it's 0, the next step is a trial balance: one row per account from your chart of accounts with its debit or credit balance, built from this same table with SUMIFS. The trial balance guide picks up from here, and the bank reconciliation guide shows how to match the Cash account to your bank statement.
Common mistakes
A separate sheet for every account
Posting each entry twice to two sheets invites one side going missing. Keep one journal table and let FILTER and SUMIFS build each account's view.
Reading the running balance with lines out of date order
Sort by date first, or use the date-based running balance that adds every line up to and including the row's date.
Only checking the grand total
Two offsetting mistakes cancel in the total. Check debits = credits for every journal ID.
Typing an amount in both Debit and Credit
Each line goes in one column only. A quick check: =COUNTIFS(E2:E16,"<>",F2:F16,"<>") should be 0.
FAQ
Can I use Excel as a general ledger?
Yes, for a small business or a side project with a modest number of entries. Excel won't stop you posting an unbalanced entry or editing last year, so add the checks above and lock closed periods. Accounting software does those things for you as volume grows.
What columns does a general ledger need?
Date, a journal or reference ID, account, description, debit and credit. A running balance column and a source document reference (invoice or receipt number) are worth adding.
How do I calculate a running balance in a ledger?
Use an expanding range: =SUMIFS(E$2:E2,C$2:C2,C2)-SUMIFS(F$2:F2,C$2:C2,C2) filled down gives each account's balance after each line, as long as the lines are in date order.
What's the difference between a journal and a general ledger?
The journal lists entries in the order they happen, each with its debit and credit lines. The general ledger groups the same lines by account. In Excel they can be one table: the journal is the table itself, and each account's ledger is a filter of it.
Functions used
SUMIFSAdds with several conditionsUNIQUERemoves duplicates, spillsHSTACKJoins arrays side by sideLETNames values inside a formulaFILTERKeeps matching rows, spillsSORTSorts a range, spillsCHOOSECOLSPicks columns, spillsSUMAdds numbersCOUNTIFSCounts with several conditions
Related guides
- How to make a profit and loss statement in Excel
- How to make a trial balance in Excel
- How to do a bank reconciliation in Excel
- How to make a running total (cumulative sum) in Excel
- How to make a balance sheet in Excel
- How to SUMIF across multiple columns in Excel
- How to create a pivot table in Excel
Updated