How to Prepare a Trial Balance in Excel With SUMIFS
By ExcelReady TeamUpdated 6 min read
The short answer
A trial balance lists every account's balance in a debit or credit column, and the two totals should match. In Excel, list the accounts, use SUMIFS to subtract each account's credits from its debits, then put positive balances in the Debit column with MAX(balance,0) and negative ones in Credit with MAX(-balance,0). Finally, check the column totals.
Key takeaways
- Start from a journal with one row per line: date, reference, account, debit and credit.
- Net balance = SUMIFS of debits minus SUMIFS of credits for that account.
- MAX(B2,0) puts a debit balance in the Debit column; MAX(-B2,0) puts a credit balance in the Credit column.
- Total debits must equal total credits. Compare them with ROUND to avoid tiny decimal differences.
- A balanced trial balance doesn't prove the books are right: wrong accounts and missing entries still balance.
On this page+
A trial balance in Excel lists every account with its balance in either a Debit or a Credit column, and the two column totals should be equal. You build it from a journal with SUMIFS: for each account, total the debits, subtract the credits, and place the result in the right column. This guide is for bookkeeping students, junior accountants and job seekers who want to see the Excel side clearly. It explains spreadsheet mechanics in plain English, not accounting advice, and the figures are a small example.
What a trial balance is#
A trial balance is a list of all the accounts in the books with their balances at one date. In double-entry bookkeeping, every transaction has equal debits and credits, so the total of all debit balances should equal the total of all credit balances. The trial balance is the check that they do.
Two terms to know:
- Debit and credit are the two sides of each entry. Assets and expenses usually end with a debit balance. Liabilities, owner's capital and revenue usually end with a credit balance.
- A journal is the list of entries, one row per line. Each entry has at least one debit line and one credit line.
Step 1: set up the journal#
Put each journal line on its own row, with the same account names every time. On a sheet called Journal:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Ref | Account | Debit | Credit |
| 2 | 1 Mar | J1 | Cash | 5,000 | 0 |
| 3 | 1 Mar | J1 | Owner capital | 0 | 5,000 |
| 4 | 3 Mar | J2 | Rent expense | 1,200 | 0 |
| 5 | 3 Mar | J2 | Cash | 0 | 1,200 |
| 6 | 10 Mar | J3 | Cash | 3,400 | 0 |
| 7 | 10 Mar | J3 | Sales revenue | 0 | 3,400 |
| 8 | 15 Mar | J4 | Supplies | 650 | 0 |
| 9 | 15 Mar | J4 | Accounts payable | 0 | 650 |
| 10 | 28 Mar | J5 | Accounts payable | 400 | 0 |
| 11 | 28 Mar | J5 | Cash | 0 | 400 |
Account names must match exactly, because SUMIFS compares text. "Cash " with a trailing space is a different account. A drop-down list of account names in column C prevents most of these slips.
Step 2: list each account once#
On a sheet called TB, put the account names in column A. You can type them from your chart of accounts, which keeps them in the usual order: assets, liabilities, capital, revenue, expenses. In Microsoft 365 or Excel 2021, =UNIQUE(Journal!C2:C11) lists them for you, in the order they first appear.
Step 3: total each account with SUMIFS#
The net balance of an account is its debits minus its credits. In B2 on the TB sheet:
=SUMIFS(Journal!$D$2:$D$11,Journal!$C$2:$C$11,A2)-SUMIFS(Journal!$E$2:$E$11,Journal!$C$2:$C$11,A2)The first SUMIFS adds every Debit where the account matches A2. The second adds every Credit for the same account. Fill the formula down (Windows: Ctrl+D; Mac: ⌘D); the $ signs keep the journal ranges fixed. For Cash, that is (5,000 + 3,400) − (1,200 + 400) = 6,800.
The SUMIF version gives the same result, with the sum range last:
=SUMIF(Journal!$C$2:$C$11,A2,Journal!$D$2:$D$11)-SUMIF(Journal!$C$2:$C$11,A2,Journal!$E$2:$E$11)SUMIFS is easier to extend. For a trial balance at a date, add a date condition: Journal!$A$2:$A$11,"<="&$H$1. The SUMIFS function guide and the SUMIF function guide explain both, and the guide to SUMIF with examples shows more criteria.
Step 4: split balances into Debit and Credit columns#
A trial balance shows each balance on its normal side, not as a plus or minus number. MAX does the split neatly. In C2 and D2:
C2: =MAX(B2,0)
D2: =MAX(-B2,0)A positive net balance goes to Debit, and a negative one shows as a positive number in Credit. The other column shows 0. Fill down, then total both columns:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Account | Net | Debit | Credit |
| 2 | Cash | 6,800 | 6,800 | 0 |
| 3 | Supplies | 650 | 650 | 0 |
| 4 | Accounts payable | −250 | 0 | 250 |
| 5 | Owner capital | −5,000 | 0 | 5,000 |
| 6 | Sales revenue | −3,400 | 0 | 3,400 |
| 7 | Rent expense | 1,200 | 1,200 | 0 |
| 8 | Total | 8,650 | 8,650 |
Both totals are 8,650, so the trial balance balances. You can hide column B once you trust the formulas.
Step 5: check that debits equal credits#
Add a check cell so you don't have to compare the totals by eye:
=IF(ROUND(SUM(C2:C7)-SUM(D2:D7),2)=0,"Balanced","Out by "&ROUND(SUM(C2:C7)-SUM(D2:D7),2))ROUND to 2 decimal places matters. Amounts with cents can leave tiny differences, such as 0.0000001, which make an exact comparison fail even when the books balance.
Also check that the trial balance captured every journal line. =SUM(C2:C7) should equal =SUM(Journal!D2:D11). If it is lower, an account in the journal is missing from your list, often because of a spelling difference.
How to find the entry that doesn't balance#
If the totals differ, check each journal reference. Every entry should net to zero, so in a small helper table with references in column G:
=SUMIFS(Journal!$D$2:$D$11,Journal!$B$2:$B$11,G2)-SUMIFS(Journal!$E$2:$E$11,Journal!$B$2:$B$11,G2)Any reference that doesn't show 0 is the problem entry. This is the same two-SUMIFS pattern as before, with the reference as the condition instead of the account. The guide to COUNTIFS and SUMIFS covers using several conditions together.
Remember what a trial balance can't catch. An entry posted to the wrong account, a transaction left out entirely, or one entered with the same wrong amount on both sides all still balance. It is a first check, not a full review.
From trial balance to reports#
Once the trial balance is right, the same SUMIFS pattern feeds the financial statements. Revenue and expense accounts flow into a profit and loss statement, and assets, liabilities and capital into a balance sheet. Account codes help here: if every expense code starts with 5, "5*" as a SUMIFS criterion totals all expenses at once.
These are everyday tasks in finance roles. The Excel skills for bookkeepers and the Excel test for accountants show what employers tend to check.
Practise it#
A trial balance is SUMIFS and SUMIF used carefully, with locked ranges and exact names. Practise COUNTIFS and SUMIFS in a live spreadsheet, then practise SUMIF to total by account. ExcelReady checks each formula as you type. Start with the Cash balance in the exercise on this page, or test yourself with the finance Excel mock test.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Find the Cash balance from these journal lines: total Cash debits minus total Cash credits. Click G2, use SUMIFS twice, and press Enter.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Credit | |||||||
| 2 | Cash balance | |||||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 | ||||||||
| 9 | ||||||||
| 10 |
Tip: The first SUMIFS adds the Debit column where the Account is Cash. The second adds the Credit column for Cash. Subtract the second from the first.
Practise this in a lesson
Frequently asked questions
How do I make a trial balance in Excel?
Put your journal lines in a table with account, debit and credit columns. On a second sheet, list each account once and use SUMIFS to total its debits and credits. Show the net balance in a Debit or Credit column and check that both columns add up to the same total.
Why doesn't my trial balance balance in Excel?
Usually one journal entry has unequal debits and credits, an account name is spelled differently from the account list, or a row sits outside the SUMIFS range. Check the difference per journal reference to find the entry.
Should I use SUMIF or SUMIFS for a trial balance?
Either works for one condition. SUMIFS is easier to extend, for example to add a date condition for a period-end trial balance, so many people use it by default.
Does a balanced trial balance mean there are no errors?
No. It only proves total debits equal total credits. An entry posted to the wrong account, an entry left out, or one with the same wrong amount on both sides still balances.