Excel guide · 4 min read
How to make a trial balance in Excel
=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.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Journal | Date | Account | Debit | Credit | Account | Name | Type | Debits | Credits | Balance (normal side) | |
| 2 | JE-01 | Oct 1, 2026 | 1000 | 20000 | 0 | 1000 | Cash | Asset | 22650 | 4495 | 18155 | |
| 3 | JE-01 | Oct 1, 2026 | 3000 | 0 | 20000 | 1100 | Accounts receivable | Asset | 3800 | 2650 | 1150 | |
| 4 | JE-02 | Oct 3, 2026 | 1500 | 6400 | 0 | 1500 | Equipment | Asset | 6400 | 0 | 6400 | |
| 5 | JE-02 | Oct 3, 2026 | 2000 | 0 | 6400 | 2000 | Accounts payable | Liability | 2400 | 6400 | 4000 | |
| 6 | JE-03 | Oct 8, 2026 | 1100 | 3800 | 0 | 3000 | Owner's equity | Equity | 0 | 20000 | 20000 | |
| 7 | JE-03 | Oct 8, 2026 | 4000 | 0 | 3800 | 4000 | Sales | Income | 0 | 3800 | 3800 | |
| 8 | JE-04 | Oct 12, 2026 | 5100 | 1500 | 0 | 5100 | Rent | Expense | 1500 | 0 | 1500 | |
| 9 | JE-04 | Oct 12, 2026 | 1000 | 0 | 1500 | 5200 | Utilities | Expense | 650 | 0 | 650 | |
| 10 | JE-05 | Oct 20, 2026 | 1000 | 2650 | 0 | |||||||
| 11 | JE-05 | Oct 20, 2026 | 1100 | 0 | 2650 | |||||||
| 12 | JE-06 | Oct 25, 2026 | 2000 | 2400 | 0 | |||||||
| 13 | JE-06 | Oct 25, 2026 | 1000 | 0 | 2400 | |||||||
| 14 | JE-07 | Oct 30, 2026 | 5200 | 650 | 0 | |||||||
| 15 | JE-07 | Oct 30, 2026 | 1000 | 0 | 560 | |||||||
| 16 | JE-08 | Oct 31, 2026 | 6150 | 35 | 0 | |||||||
| 17 | JE-08 | Oct 31, 2026 | 1000 | 0 | 35 |
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.
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.
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.
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.
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.
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
SUMIFSAdds with several conditionsSUMAdds numbersIFPicks a result from a testORTrue if any test is trueFILTERKeeps matching rows, spillsCOUNTIFCounts cells that matchUNIQUERemoves duplicates, spillsMODRemainder after division
Related guides
- How to make a balance sheet in Excel
- How to make a profit and loss statement in Excel
- How to create a general ledger in Excel
- How to do a bank reconciliation in Excel
- Excel test for accounting roles: what it covers and how to pass (or set) one
- Excel formulas cheat sheet: the 25 everyone should know
- How to do a price volume mix analysis in Excel
Updated