Cellwise Start free

Excel guide · 4 min read

How to make a trial balance in Excel

Quick answerList every account from your chart of accounts, then total the ledger's debits and credits for each one with SUMIFS: =SUMIFS($D$2:$D$17,$C$2:$C$17,G2) for debits and the same on the credit column. Total debits must equal total credits; put the difference in a check cell. If it isn't zero, look for an unbalanced journal, an account missing from the chart, or a difference divisible by 9 (transposed digits).

Sample data

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

RowABCDEFGHIJKL
1JournalDateAccountDebitCreditAccountNameTypeDebitsCreditsBalance (normal side)
2JE-01Oct 1, 202610002000001000CashAsset22650449518155
3JE-01Oct 1, 202630000200001100Accounts receivableAsset380026501150
4JE-02Oct 3, 20261500640001500EquipmentAsset640006400
5JE-02Oct 3, 20262000064002000Accounts payableLiability240064004000
6JE-03Oct 8, 20261100380003000Owner's equityEquity02000020000
7JE-03Oct 8, 20264000038004000SalesIncome038003800
8JE-04Oct 12, 20265100150005100RentExpense150001500
9JE-04Oct 12, 20261000015005200UtilitiesExpense6500650
10JE-05Oct 20, 2026100026500
11JE-05Oct 20, 2026110002650
12JE-06Oct 25, 2026200024000
13JE-06Oct 25, 2026100002400
14JE-07Oct 30, 202652006500
15JE-07Oct 30, 202610000560
16JE-08Oct 31, 20266150350
17JE-08Oct 31, 20261000035

Download this sheet as an Excel practice file (.xlsx)

Set up the ledger and the chart of accounts

The sheet above has October's general ledger export in A:E, one row per journal line with the debit or credit amount, and the chart of accounts in G:I with each account's type. Columns J:L are the trial balance itself, built from the two.

Keep the export as it came out of your system: don't retype amounts, and fill empty debit or credit cells with 0 (or leave them truly blank) rather than a dash. Account numbers must be the same type in both lists: if the ledger has them as text and the chart as numbers, SUMIFS usually matches them anyway, but lookups like XLOOKUP and MATCH won't. This ledger has two mistakes planted in it, which the later steps find.

Step 1: Total debits and credits per account with SUMIFS

In J2, total the debit column for the account in G2, with absolute references so it copies down. Do the same in K2 on the credit column. One row per account from the chart, not from the ledger, so accounts with no activity this month still appear.

=SUMIFS($D$2:$D$17,$C$2:$C$17,G2)

Result: 22650

22,650 of debits to Cash. The credit version, =SUMIFS($E$2:$E$17,$C$2:$C$17,G2), gives 4,495.

Step 2: Show each balance on its normal side

Assets and expenses normally carry a debit balance; liabilities, equity and income normally carry a credit balance. Use the Type column to subtract in the right direction, so every balance shows as a positive number when it's normal. A negative result is an abnormal balance (cash below zero, a customer paying twice) and worth a look before anything else.

For the printed layout, show each balance in a Debit or a Credit column: =IF(OR(I2="Asset",I2="Expense"),L2,0) and the opposite for the credit side.

=IF(OR(I2="Asset",I2="Expense"),J2-K2,K2-J2)

Result: 18155

18,155 debit balance for Cash. Sales shows 3,800 and Owner's equity 20,000, both credits, both positive: normal.

Step 3: The check cell

Every journal has equal debits and credits, so the trial balance must too. Subtract total credits from total debits and make it impossible to miss: bold, and red with conditional formatting when it isn't zero. Wrap it in ROUND(...,2) if your amounts have cents.

=SUM(J2:J9)-SUM(K2:K9)

Result: 55

55, not 0. Something is wrong. The next two steps find it.

Step 4: Find accounts missing from the chart

A trial balance built from the chart silently drops any ledger line posted to an account that isn't on the list. Compare the ledger's total debits, =SUM(D2:D17), which is 37,435, with the trial balance's 37,400: 35 is missing. List the culprits with COUNTIF.

=FILTER(C2:C17,COUNTIF(G2:G9,C2:C17)=0)

Result: 6150

6150: a bank fee posted to an account nobody added to the chart. Add it (as an Expense) and the check cell becomes 90. In older Excel, put =COUNTIF($G$2:$G$9,C2)=0 next to the ledger and filter for TRUE.

Step 5: Find the unbalanced journal

The remaining 90 means some journal's debits don't equal its credits: one side typed wrong, or a line with only one side. Check every journal at once by comparing its debits and credits.

The size of the difference is a clue too. A difference divisible by 9, like 90, is the classic sign of transposed digits: =MOD(90,9)=0 is TRUE, and 650 typed as 560 is exactly 90 out. A difference equal to one amount suggests a line with one side missing; double an amount suggests a debit entered as a credit.

=FILTER(UNIQUE(A2:A17),SUMIFS(D2:D17,A2:A17,UNIQUE(A2:A17))<>SUMIFS(E2:E17,A2:A17,UNIQUE(A2:A17)))

Result: JE-07

JE-07: utilities debited 650, cash credited 560. Correct the credit to 650 in your accounting system, re-export, and the check cell reads 0.

Common mistakes

Building the list of accounts from the ledger

Use the chart of accounts, then check the ledger for accounts not on it. A UNIQUE list from the ledger hides zero-activity accounts and new unknown ones.

Plugging the difference

A typed balancing figure hides the real error. Find the journal; the trial balance exists to catch it.

Netting everything into one column

Show debit and credit totals separately before netting, so a missing side is visible.

Fixing it only in Excel

Correct the entry in the accounting system and re-export, or the books and the spreadsheet disagree next month.

FAQ

How do I create a trial balance in Excel?

Put the ledger export and the chart of accounts on one sheet, total debits and credits per account with SUMIFS, and check that total debits equal total credits.

What do I do if my trial balance doesn't balance?

Look for an account missing from the chart (COUNTIF), a journal whose debits and credits differ (SUMIFS by journal), and check whether the difference is divisible by 9, which suggests transposed digits.

Does a balanced trial balance mean the books are right?

No. It only proves debits equal credits. A balanced entry posted to the wrong account, or missed entirely, still balances. That's what reconciliations are for.

What is the normal balance of an account?

Debit for assets and expenses, credit for liabilities, equity and income. A balance on the other side is abnormal and worth checking.

Functions used

Related guides

Updated