Cellwise Start free

Excel guide · 6 min read

How to create a general ledger in Excel

Quick answerKeep every journal line in one table: date, journal ID, account, description, debit and credit. Check each journal balances with SUMIFS (debits minus credits by journal ID should be 0), add a running balance per account with SUMIFS over the rows so far, and pull any one account's ledger out with FILTER, sorted by date. Opening and period balances come from SUMIFS with date limits in cells.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1DateJournalAccountDescriptionDebitCreditBalanceAccountFromTo
2Oct 28, 2026J-101CashOwner investment50005000CashNov 1, 2026Nov 30, 2026
3Oct 28, 2026J-101Owner's equityOwner investment5000-5000
4Oct 30, 2026J-102Rent expenseOctober rent12001200
5Oct 30, 2026J-102CashOctober rent12003800
6Nov 5, 2026J-103Accounts receivableInvoice 1001 Lakeview24002400
7Nov 5, 2026J-103SalesInvoice 1001 Lakeview2400-2400
8Nov 9, 2026J-104SuppliesPrinter ink and paper290290
9Nov 9, 2026J-104PostageCourier5050
10Nov 9, 2026J-104CashOffice Depot card3403460
11Nov 12, 2026J-105CashLakeview pays 100124005860
12Nov 12, 2026J-105Accounts receivableLakeview pays 100124000
13Nov 16, 2026J-106Accounts receivableInvoice 1002 Harbor Gym18501850
14Nov 16, 2026J-106SalesInvoice 1002 Harbor Gym1580-3980
15Nov 4, 2026J-107CashCash sale (entered late)9506810
16Nov 4, 2026J-107SalesCash 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.

=HSTACK(UNIQUE(B2:B16),SUMIFS(E2:E16,B2:B16,UNIQUE(B2:B16))-SUMIFS(F2:F16,B2:B16,UNIQUE(B2:B16)))

Result: spills 7 rows × 2 columns

J-1010
J-1020
J-1030
J-1040
J-1050
J-106270
J-1070

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.

=LET(j,UNIQUE(B2:B16),FILTER(j,SUMIFS(E2:E16,B2:B16,j)<>SUMIFS(F2:F16,B2:B16,j)))

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.

=G15

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.

=SUMIFS(E$2:E$16,C$2:C$16,C10,A$2:A$16,"<="&A10)-SUMIFS(F$2:F$16,C$2:C$16,C10,A$2:A$16,"<="&A10)

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.

=SORT(FILTER(CHOOSECOLS(A2:F16,1,2,4,5,6),C2:C16=J2),1)

Result: spills 5 rows × 5 columns

46323J-101Owner investment50000
46325J-102October rent01200
46330J-107Cash sale (entered late)9500
46335J-104Office Depot card0340
46338J-105Lakeview pays 100124000

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).

=SUMIFS(E2:E16,C2:C16,J2,A2:A16,"<"&K2)-SUMIFS(F2:F16,C2:C16,J2,A2:A16,"<"&K2)

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

Related guides

Updated