How to build a balance sheet in Excel that balances

A balance sheet lists what a business owns, what it owes and what belongs to its owners on one date. Those three parts must agree: assets equal liabilities plus equity. In a spreadsheet you total each class with a formula and then let one more formula prove that the two sides match.

The balance sheet in plain English

Where the income statement covers a period, the balance sheet is a snapshot at a single date, such as December 31. It has three sections.

• Assets are resources the business controls: cash, receivables, inventory, equipment, land. Current assets are expected to turn into cash or be used up within a year. Noncurrent assets last longer.
• Liabilities are amounts the business owes. Current liabilities are due within a year; long-term liabilities, such as most notes payable, are due later.
• Equity is the owners' claim: what they put in (common stock) plus profits kept in the business (retained earnings).

The accounting equation, assets = liabilities + equity, holds because every journal entry has equal debits and credits. So when a balance sheet built from a trial balance that balances comes out uneven, the problem is in the formulas, not the books. That is useful, because it means the final check catches most spreadsheet errors for you.

In the exercises, each trial balance row carries a classification label in column B, such as "Current asset" or "Long-term liability". A SUMIF adds the balances for one label, and simple additions build the totals.

Worked example: Copperleaf Studio

The free exercise gives you Copperleaf Studio's trial balance at December 31, 2026. All balances are in one column, and the rows are mixed up, as they would be in a list sorted by account number.

The balance sheet goes in H4:H12, with the line labels in column G.

Step 1, assets. Cash, inventory, prepaid insurance and receivables are current: $105,500 + $118,800 + $6,700 + $52,100 = $283,100. Equipment and land are noncurrent: $295,700. Total assets are $578,800. Prepaid insurance counts as an asset because the business has paid for coverage it has not used yet.

Step 2, liabilities. Accounts payable, unearned revenue and wages payable are current, $53,100. The note payable is long-term, $73,400. Total liabilities are $126,500. Unearned revenue is a liability, not revenue: customers have paid for work the business still owes them.

Step 3, equity. Retained earnings of $391,000 plus common stock of $61,300 gives $452,300.

Step 4, the other side. $126,500 + $452,300 = $578,800, the same as total assets.

Step 5, the check. The formula in H12 subtracts one side from the other, rounds the difference to cents and tests whether it is zero. Why round? A spreadsheet stores numbers in binary, so adding amounts with cents can leave a difference such as 0.0000000001 that should be zero. Rounding to two decimals first stops that from reporting a false "Out of balance". The task asks for a match "to the cent", and that is exactly what ROUND(…,2)=0 tests.

Contra assets and Debit and Credit columns

The second exercise, Maple Lane Catering, splits balances into Debit and Credit columns. Assets normally have debit balances, and liabilities and equity normally have credit balances. If you want a refresher on why, see normal balance of accounts.

Two asset accounts break the pattern. The Allowance for Doubtful Accounts ($1,000) and Accumulated Depreciation ($25,200) have credit balances. They are contra assets: they sit in an asset class but reduce it. The allowance trims receivables to the amount the business expects to collect; accumulated depreciation trims equipment to its remaining book value.

The formula handles them without any special case. For each asset class, take debits minus credits:

=SUMIF($B$4:$B$17,"Current asset",$C$4:$C$17)-SUMIF($B$4:$B$17,"Current asset",$D$4:$D$17)

Current assets come to $247,700 − $1,000 = $246,700, and noncurrent assets to $180,800 − $25,200 = $155,600. For liabilities and equity, flip the order and take credits minus debits. Maple Lane's total assets of $402,300 equal total liabilities of $208,000 plus equity of $194,300.

Before closing: bringing in net income

The third exercise uses Bluebird Veterinary's unadjusted trial balance, which has not been closed. It still contains revenue, expense and dividends accounts. Closing entries would move all three into retained earnings at year end (see closing entries practice). Until then, retained earnings is missing this year's profit, and the balance sheet cannot balance without it.

So the equity section gets three lines:

• Equity accounts (H10): credits minus debits for the Equity rows. Dividends ($11,500) is a debit in the Equity class, so it comes off: $79,300 + $282,200 − $11,500 = $350,000.
• Net income (H11): revenue credits less revenue debits, less expense debits net of expense credits. Service revenue of $165,200 less expenses of $83,000 gives $82,200.
• Total equity (H12): =H10+H11, or $432,200.

Total liabilities of $180,300 plus total equity of $432,200 gives $612,500, which matches total assets, and H14 shows Balanced.

Common mistakes

The check cell is your friend here: almost every mistake below turns it to "Out of balance". These are the ones the exercises recognize.

Leaving equity out of the total

If total liabilities and equity just repeats total liabilities, =H9, Copperleaf's right-hand side is $126,500 against assets of $578,800. The check can never pass. Add equity in H10.

Counting only current assets

Writing total assets as =H4 gives $283,100 and drops the $295,700 of equipment and land. Total assets are current plus noncurrent.

Ignoring the contra-asset credits

If you sum only the Debit column for each asset class, Maple Lane's assets come to $428,500, overstated by the $26,200 of the allowance and accumulated depreciation. Subtract each asset class's credits.

Liabilities as debits minus credits

Using the asset formula for liabilities gives negative totals: −$68,100 of current liabilities and −$139,900 of long-term liabilities. Liabilities have credit balances, so take credits minus debits.

Forgetting net income before closing

If total equity is only the equity accounts, Bluebird's right-hand side is $530,300, short by exactly the $82,200 of net income. When an unclosed trial balance is out by the amount of net income, this is the reason.

Letting dividends add to equity

Summing only the Equity credits gives $361,500 instead of $350,000, so the balance sheet is out by the $11,500 of dividends. Dividends are a debit in equity and reduce it.

How to find the error when it won't balance

• Look at the size of the difference. If it equals one account balance, that account is missing or in the wrong class. If it equals net income, you forgot to bring it in.
• Look for negatives. A negative liability or equity total means a subtraction is the wrong way round.
• Check the labels. A classification typed slightly differently in your formula, such as "Long term liability" without the hyphen, returns 0.
• Total one class by hand. Add the rows with one label yourself and compare. A mismatch shows which formula to fix, and the label check above usually explains why.

MO-220 practice: preparing financial statements

This lesson covers the "Prepare financial statements" objective in the "Prepare and analyze financial statements" area of the MO-220 exam outline: using formulas to aggregate account data into a statement. Accounting Exercises is not affiliated with Microsoft or Certiport. As MO-220 practice, remember that MO-220 task instructions describe the purpose, such as "show whether the balance sheet balances", rather than naming a function. The exercises are written the same way, so you decide on SUMIF, IF and ROUND yourself.

You build each balance sheet in a spreadsheet in your browser, using the same formulas you would write in Microsoft® Excel® workbooks, and each Check grades the resulting values. In this course the balance sheet follows the income statement in Excel, and its figures feed liquidity and solvency ratios in Excel. The free exercise below gives you new numbers each time.