Trial Balance to Balance Sheet Excel Workbook, Step by Step

To turn a trial balance into a balance sheet in Excel, keep three tabs: the trial balance with one signed balance column, a mapping tab that assigns every account to a balance sheet line, and the balance sheet itself built with SUMIFS. Revenue and expense accounts roll into equity as current-year earnings, and one check cell proves the sheet balances.

The rest of this post builds that workbook for one fictional client, Cedar Row Supply LLC, for the year ended December 31, 2026. Every name, account number and amount is an example. The arithmetic is real: the trial balance foots, and the balance sheet it produces balances to the dollar.

Setting up the trial balance tab for balance sheet formulas

A trial balance tab that feeds balance sheet formulas needs four things on every row: an account number, an account name, the debit and credit amounts as exported, and one signed balance column. The signed balance is debit minus credit, so debit balances are positive and credit balances are negative. Every later formula reads that single column.

Name the tab TB, with no spaces, so formulas can refer to it as TB!E:E without quotation marks. Microsoft's page on creating or changing a cell reference explains the sheet-name-then-exclamation-point form these formulas use, and why names with spaces need single quotes.

Here is the example trial balance as it lands on the TB tab. Column E holds =C2-D2, filled down.

Two habits keep this tab reliable. First, paste the export as values and leave the layout alone; do not insert subtotal rows, because SUMIFS will count them. Second, make sure the amounts are numbers. Exports often arrive as text, and a text amount is silently skipped. Microsoft's guide to converting numbers stored as text to numbers shows the green-triangle indicator and the Convert to Number fix.

The total of column E should be zero. If it is not, the trial balance itself is out of balance, and no balance sheet built on it will balance either.

Summing trial balance accounts into balance sheet lines with SUMIFS

SUMIFS adds the cells in one range whose matching cells in another range meet a condition. In this workbook it runs twice: once on the mapping tab to pull each account's balance from the trial balance, and once on the balance sheet to add every account mapped to a line. Both formulas read the same signed balance column.

Microsoft's SUMIFS function reference gives the argument order as the sum range first, then each criteria range and its criteria. It also warns that every criteria range must be the same size as the sum range, which is why the formulas below use whole columns on both sides.

The mapping tab

The Map tab lists every account once, with the balance sheet line it belongs to. Column A is the account number, column B is the line, and column C pulls the balance:

=SUMIFS(TB!$E:$E, TB!$A:$A, $A2)

Equipment and its accumulated depreciation share one line, so the balance sheet shows the net figure. Designing a mapping table that survives new accounts and a second client is its own job; the guide to building a reusable trial balance mapping table covers line naming and account ranges.

The balance sheet formula

On the BS tab, column A holds the line label exactly as typed on the mapping tab, column B holds a sign (1 for assets, -1 for liabilities and equity), and column C holds the amount:

=SUMIFS(Map!$C:$C, Map!$B:$B, $A5) * $B5

The label must match the mapping tab exactly. A trailing space or an abbreviation such as "Accts payable" will not match, and the line returns zero without an error. Copy labels from the mapping tab rather than retyping them.

Where does current-year net income go on an Excel balance sheet?

Current-year net income goes in the equity section, on its own line beneath beginning retained earnings. Before the books are closed, revenue and expense accounts still carry balances on the trial balance. Mapping all of them to one equity line rolls their net total into equity, so the balance sheet balances without a closing entry.

In the example, the revenue and expense accounts on the mapping tab add up to -14,000: sales of -225,000 plus expenses of 211,000. Multiplied by the equity sign of -1, the BS tab shows current-year net income of 14,000. That matches what an income statement for Cedar Row Supply would show: 225,000 of sales less 131,000, 52,000, 21,000 and 7,000 of costs.

The equity section then reads:

This is a retained earnings roll in three lines: beginning balance, plus the year's result, equals the ending figure of 32,000. If the client recorded owner draws or distributions in a separate equity account, give that account its own line and map it there, so the roll shows beginning, plus income, less distributions. For an S corporation, the S corp year-end checklist lists the equity and distribution records to collect before the balance sheet is final.

A trial balance pulled after the year is closed is different. Its revenue and expense accounts are zero and beginning retained earnings already includes the year. The same workbook still works, but the current-year net income line shows zero. Check which kind of trial balance you have before reading the equity section.

A balance sheet check cell that proves assets equal liabilities and equity

A balance sheet check cell subtracts total liabilities and equity from total assets and shows the difference. When the result is zero, the sheet balances. When it is not, the cell should say so in words and show the amount, so nobody prints an out-of-balance statement by mistake.

Here is the full BS tab for the example, with the totals as plain SUM formulas:

Suppose total assets are in C10, total liabilities in C16 and total equity in C21. The check cell is:

=IF(ROUND(C10-(C16+C21),2)=0, "Balances", "Out by " & ROUND(C10-(C16+C21),2))

The ROUND function rounds the difference to two decimals, so a sheet that balances to the cent does not show as out by 0.0000001. The IF function turns the result into a word a reviewer cannot miss.

Add a second check beside it: the total of the mapping tab's balance column should equal the total of the trial balance's balance column, which is zero. If the mapping total differs, an account is missing from the mapping tab, and its balance never reached any line. When either check fails, the walkthrough on checking a balance sheet against the trial balance in order lists which causes to rule out first.

How do you add a prior-year column to the balance sheet workbook?

A prior-year column comes from a second trial balance tab laid out exactly like the first. Name it PriorTB, paste last year's year-end trial balance into the same columns, and add a fourth column to the mapping tab that pulls from it. The balance sheet then gets a second amount column with the same formula pointed at that column.

The mapping tab's new column D reads:

=SUMIFS(PriorTB!$E:$E, PriorTB!$A:$A, $A2)

and the balance sheet's prior-year column reads:

=SUMIFS(Map!$D:$D, Map!$B:$B, $A5) * $B5

Three details matter. First, accounts opened this year return zero in the prior-year column, which is correct. Accounts that existed last year and are gone now are the problem: if an account is on PriorTB but not on the mapping tab, its balance disappears. Add every account from both years to the mapping tab.

Second, the prior year's beginning retained earnings plus its net income should equal this year's beginning retained earnings. In the example, if last year's balance sheet showed 18,000 of ending retained earnings, this year's beginning line must also show 18,000. A difference there points to a prior-period adjustment or a late entry booked after last year's statements were issued.

Third, keep both years on the same basis. A cash-basis trial balance next to an accrual-basis one gives a comparison that looks precise and means little. If you need the statement from a PDF instead of a trial balance, the guide to getting a balance sheet to Excel from a PDF or software report covers keeping the prior-year column intact during conversion.

Balance sheet sign errors from credit-balance trial balance accounts

Balance sheet sign errors happen because liability and equity accounts normally carry credit balances, and a signed trial balance column stores credits as negative numbers. Sum those accounts directly and the balance sheet shows accounts payable of -15,600. The fix is to multiply each line by a sign column, not to retype the numbers.

In the example, the sign column holds 1 for every asset line and -1 for every liability and equity line. The SUMIFS result for accounts payable is -15,600, and multiplying by -1 gives 15,600. Contra accounts need no special sign, because they sit on the same line as the account they reduce: accumulated depreciation of -14,000 and equipment of 58,000 net to 44,000 under the asset sign of 1.

Four sign mistakes come up often:

• Abnormal balances. A customer overpayment can leave accounts receivable with a credit balance. The sign column still says 1, so the line shows a negative asset. That is correct and worth a look, not a formula fix.
• Separate debit and credit columns summed together. Adding column C and column D instead of subtracting them turns every credit balance positive, so assets never equal liabilities and equity. Use the signed column E only.
• Export signs already flipped. If a report shows every balance as a positive number with a "Cr" suffix, the signs are hidden in text. Strip the suffix and make credits negative before the balance sheet reads them; Microsoft's page on finding and replacing text and numbers on a worksheet covers Replace All across a column.
• Display mistaken for sign. A number format that shows negatives in parentheses changes only how a value looks. Microsoft's page on changing the way negative numbers are displayed shows the Format Cells option; the value underneath keeps its sign.

When the client sends a trial balance as a scan or a PDF rather than an export, the signs and column positions are only as good as the conversion. Schema CPA reads general ledgers and trial balances alongside the rest of a client's paper and exports the figures to Excel or CSV, so the TB tab can start from a file instead of retyping; the document types table on the homepage lists everything it reads.