How to prepare a trial balance in Excel

A general ledger can run to thousands of lines. With a few formulas, a spreadsheet turns it into a trial balance: one row per account, debit and credit balances, and a check that the two columns agree. Here is how, step by step.

What you are building

A trial balance lists every account with its balance, debits in one column and credits in the other. Because every journal entry has equal debits and credits, the column totals must agree. If the idea is new, start with trial balance practice, which builds one by hand from T-accounts.

In a spreadsheet, the starting point is usually a ledger export: one row per posting, with a date, an account name, and an amount in either a Debit or a Credit column. Your job is to summarize it by account. Three tools do almost all the work:

• A conditional sum adds up a column only where the account name matches. The solution uses SUMIFS.
• MAX turns a negative difference into 0, so each balance lands in one column only.
• An exact-match lookup pulls information such as the account type from a chart of accounts. The solution uses XLOOKUP.

The MO-220 objective this covers

This lesson is MO-220 practice for the objective "Prepare a trial balance", under "Organize financial data with formulas": using math, statistical, logical and lookup functions on accounting data. MO-220 task instructions describe the purpose of a calculation, such as "show each account's total debits", rather than naming the function to use. So the skill being tested is choosing the right function from the task's wording. The exercises here are worded the same way. Accounting Exercises is not affiliated with Microsoft or Certiport.

The worksheet layout

Here is Northside Print Shop's February 2026 ledger, as it appears in the free exercise. Every entry is two lines: one debit and one credit.

The account list sits in H4:H10 (Cash, Accounts Receivable, Supplies, Accounts Payable, Owner's Capital, Service Revenue, Rent Expense), with "Total" in H11. Your formulas go in columns I and J.

Step 1: total debits and credits by account

For Cash, you want the sum of column C, but only on rows where column B says "Cash". That is a conditional sum. In I4:

=SUMIFS($C$4:$C$21,$B$4:$B$21,H4)

Read it as: add up C4:C21 where B4:B21 equals the account name in H4. For credits, change only the column being added. In J4:

=SUMIFS($D$4:$D$21,$B$4:$B$21,H4)

The dollar signs lock the ledger ranges. The criterion H4 has none, so when you fill the two formulas down to row 10 it becomes H5, H6 and so on, while the ledger stays put. Finally, total both columns in row 11 with =SUM(I4:I10) and =SUM(J4:J10).

Check one by hand: Cash debits are 10,500.00 + 16,500.00 + 4,055.00 + 640.00 = 31,695.00. Both columns total 43,450.00, so every posting was picked up.

Step 2: turn totals into balances

A true trial balance shows each account's net balance in one column. Cash, for example, is 31,695.00 − 4,050.00 = 27,645.00, a debit balance. Service Revenue is a 10,645.00 credit balance.

The third exercise asks for exactly this, on a different ledger (Juniper Landscaping, July). The Debit balance column subtracts credits from debits and keeps the result only if it is positive:

=MAX(SUMIFS($C$4:$C$21,$B$4:$B$21,H4)-SUMIFS($D$4:$D$21,$B$4:$B$21,H4),0)

The Credit balance column does the reverse:

=MAX(SUMIFS($D$4:$D$21,$B$4:$B$21,H4)-SUMIFS($C$4:$C$21,$B$4:$B$21,H4),0)

MAX(…,0) returns the larger of the difference and zero. A debit-balance account shows its balance in I and 0 in J; a credit-balance account the other way round. For Juniper, Cash shows 19,330.00 in I and 0 in J, while Service Revenue shows 0 in I and 8,710.00 in J. Both columns total 29,080.00.

Accounts Receivable is a good one to check by hand. Juniper billed customers 820.00, 2,240.00 and 2,980.00, a total of 6,040.00, and collected 5,560.00. The balance is 6,040.00 − 5,560.00 = 480.00, a debit, so it appears in column I.

Step 3: add a balance check

A good worksheet tells you when something is wrong. In I12:

=IF(ROUND(I11-J11,2)=0,"In balance","Out of balance")

ROUND(…,2) compares the totals to the cent. Spreadsheets store decimals in binary, so two totals that print as equal can differ by a tiny fraction. Rounding the difference first stops a false "Out of balance".

Step 4: look up each account's type

The second exercise adds a chart of accounts in L4:N10 (account, type, normal balance) and asks for each ledger line's type in column E. The solution uses an exact-match lookup:

=XLOOKUP(B4,$L$4:$L$10,$M$4:$M$10)

It finds the account name from B4 in column L and returns the matching value from column M, such as Asset or Revenue. XLOOKUP matches exactly by default. With a type column, you can filter or sort the ledger by type, or total by type with another conditional sum.

Why formulas beat typing the numbers

You could add up each account with a calculator and type the results in. For eighteen lines, that works. For a real month, it doesn't, and typed numbers go stale the moment someone posts a correction. A formula-driven trial balance in Microsoft® Excel® spreadsheets updates itself: change a ledger amount and every total, balance and the status cell follow.

Formulas also make your work auditable. A reviewer can click any balance and see exactly which range and which account name produced it. That is much easier to check than a number with no history.

The MO-220 objective also covers organizing the transactions themselves. Sorting the ledger by account, or filtering it to one account, is a quick way to confirm a single SUMIFS result by eye before you trust the whole column.

Common mistakes

Ranges that shift when you fill down

If I4 is written as =SUMIFS(C4:C21,B4:B21,H4), filling it down moves the ledger too: by row 10 it reads C10:C27 and misses the first rows. For Northside, Rent Expense then shows 800.00 instead of 4,050.00, and Service Revenue credits show 6,855.00 instead of 10,645.00. Lock the ranges: $C$4:$C$21.

Totaling credits from the Debit column

Copying the debit formula into column J without changing C to D makes the credits repeat the debits. The totals still agree at 43,450.00, so the check alone won't catch it. Credits come from column D.

Approximate-match lookups

An older lookup such as VLOOKUP with TRUE as its last argument expects a sorted table. This chart isn't sorted, so some lines come back with the wrong type. Ask for an exact match.

Returning the wrong column

Returning column N gives "Debit" or "Credit", the normal balance, instead of the type. Return column M.

Gross totals instead of net balances

Showing total debits and total credits for each account isn't a trial balance. For Juniper, Cash would show 27,230.00 and 7,900.00 instead of a single 19,330.00 debit balance, and the totals would be 42,540.00 instead of 29,080.00.

Negative numbers in the other column

Without MAX, Cash's credit column would show −19,330.00. Every account then nets to zero across the row, and both totals become 0, so the check says "In balance" while the trial balance is meaningless. Show 0, not a negative.

Related topics

Once the trial balance is in balance, its figures feed financial ratios in Excel. For the journal entries behind each ledger line, see journal entry practice problems.