Trial Balance in Excel: Build It from the General Ledger with SUMIFS

What a trial balance is (and what it is not)

A trial balance lists every account in the general ledger with its closing balance, debit balances in one column and credit balances in the other. Because every journal entry has equal debits and credits, the two column totals must be equal. If they are not, something was posted wrong.

A balanced trial balance proves the arithmetic, not the bookkeeping. An entry posted to the wrong account, recorded twice or left out completely still balances. That is why the template adds extra checks, and why you still reconcile the bank and review the accounts afterwards.

Step 1: Record every entry as ledger lines

The Ledger sheet holds one row per debit or credit, not one row per transaction. Each row has the date, the entry number (JE-001, JE-002, …), the account number, a description and the amount in either Debit or Credit. A transaction that touches three accounts takes three rows with the same entry number.

The example is the first month of trading for Maple Street Supply Co. JE-001 records the owner’s investment and a bank loan in one entry:

Column D fills in the account name from the chart of accounts, so you only type the number:

=IF(C5="","",IFERROR(VLOOKUP(C5,'Trial Balance'!$A$5:$B$19,2,FALSE),"Not in chart"))

A mistyped account number shows Not in chart instead of an error. The account number cells also have a drop-down list (Data > Data Validation) that points at the chart of accounts; for a list that grows with the chart, use a drop-down list from a Table.

Step 2: Check that each entry balances

Catch mistakes where they happen, before they reach the trial balance. In H5, filled down to the last row:

=IF(B5="","",IF(ROUND(SUMIFS($F$5:$F$40,$B$5:$B$40,B5)-SUMIFS($G$5:$G$40,$B$5:$B$40,B5),2)=0,"OK","Check"))

The first SUMIFS adds all debits with the same entry number, the second adds all credits. ROUND to cents stops tiny floating-point differences from showing as errors. Every line of an unbalanced entry shows Check, so you see exactly which entry to fix. If SUMIFS is new to you, start with summing monthly values with SUMIFS.

Step 3: List the chart of accounts

On the Trial Balance sheet, list every account once in A5:D19: account number, name, type and normal balance. Number accounts by type (1000s assets, 2000s liabilities, 3000s equity, 4000s revenue, 5000s and 6000s expenses) so the trial balance reads in the same order as the financial statements.

Include accounts that have no activity yet, such as a new expense account. The trial balance then shows a dash for them instead of silently leaving them out, and the chart is ready when the first entry arrives.

Step 4: Pull the debits and credits with SUMIFS

For each account, total the ledger debits in E5 and the ledger credits in F5, then fill down:

=SUMIFS(Ledger!$F$5:$F$40,Ledger!$C$5:$C$40,A5)

=SUMIFS(Ledger!$G$5:$G$40,Ledger!$C$5:$C$40,A5)

The sum range is the Debit (or Credit) column, the criteria range is the account number column, and the criterion is the account number in A5. The ranges run to row 40, past the last entry, so new lines are picked up as soon as you type them. Whole columns (Ledger!F:F) also work if you prefer.

Step 5: Put each balance in Debit or Credit

A trial balance shows each account’s net balance once, on its natural side. In G5 and H5:

=MAX(0,E5-F5)

=MAX(0,F5-E5)

If debits are larger, the difference lands in Debit and Credit shows a dash; if credits are larger, the reverse. Total both columns in row 20 and compare them in D22:

=IF(ROUND(G20-H20,2)=0,"Balanced","Out by "&TEXT(G20-H20,"#,##0.00;(#,##0.00)"))

The last check matters: a ledger line with an account number that is not in the chart is ignored by every SUMIFS, so the trial balance could look fine while the ledger is not. Comparing the ledger total with the trial balance total catches it.

Step 6: Check net income and the balance sheet

Because every account has a type, SUMIFS on the Type column turns the trial balance into a quick income statement and balance sheet test (J4:K13). Revenue, for example:

=SUMIFS($H$5:$H$19,$C$5:$C$19,"Revenue")-SUMIFS($G$5:$G$19,$C$5:$C$19,"Revenue")

Assets equal liabilities plus equity plus net income, so K13 shows Ties. Total assets are net of the 200.00 of accumulated depreciation, which comes from the depreciation schedule (12,000.00 of equipment over 60 months). From here, the revenue and expense lines feed the monthly profit and loss statement.

How to find an out-of-balance difference

1. Look at the entry check column first: any entry showing Check is the cause.
2. If every entry is OK, check Lines not in chart and Ledger = trial balance; an unknown account number drops a line from the trial balance.
3. Halve the difference and look for that amount: a debit entered as a credit moves the totals by twice the amount.
4. Divide the difference by 9: if it divides evenly, look for a transposition (3,600 typed as 3,060) or a slide (3,600 typed as 360).
5. Look for formulas that were overwritten with values; finding inconsistent formulas and Excel’s error checking rules both help.

Month-end checklist before you run the trial balance

• Bank accounts reconciled (see the bank reconciliation template) and petty cash replenished (see the petty cash log).
• Depreciation, accruals and prepaid amortization posted.
• Accounts Receivable and Accounts Payable balances agree with the aging reports.
• Inventory agrees with the stock count or the inventory system.
• Every entry shows OK and every check on the Trial Balance sheet passes.

Frequently asked questions

Final checklist

• Every ledger line has an entry number and an account number from the chart.
• Every entry shows OK in the Entry check column.
• Debits = credits shows Balanced, and both line counts show 0.
• Ledger = trial balance shows Ties.
• No account has a balance on the wrong side without a good reason.
• Assets = L + E shows Ties.