Cellwise Start free

Excel guide · 6 min read

How to make a balance sheet in Excel

Quick answerGive every ledger account a type (Asset, Liability, Equity, or a P&L type) with a lookup to your chart of accounts, then sum each type with SUMIFS on every line dated before the as-of date plus one: debits minus credits for assets, credits minus debits for liabilities and equity. Add this year's profit (revenue minus costs since 1 January) to equity, and finish with a check cell, assets - liabilities - equity, which must be 0.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJK
1DateAccountDebitCreditTypeAccountTypeAs ofMar 31, 2026
2Jan 2, 2026Cash10000AssetCashAssetTotal assets24300
3Jan 2, 2026Owner capital10000EquityAccounts receivableAssetTotal liabilities10200
4Jan 5, 2026Cash8000AssetEquipmentAssetOwner capital10000
5Jan 5, 2026Loan8000LiabilityAccounts payableLiabilityCurrent-year profit4100
6Jan 10, 2026Equipment6000AssetLoanLiabilityTotal equity14100
7Jan 10, 2026Cash6000AssetOwner capitalEquityCheck (should be 0)0
8Feb 15, 2026Accounts receivable7500AssetSalesRevenue
9Feb 15, 2026Sales7500RevenueMaterialsCOGS
10Mar 3, 2026Materials2200COGSRentExpense
11Mar 3, 2026Accounts payable2200LiabilityWagesExpense
12Mar 20, 2026Cash5000Asset
13Mar 20, 2026Accounts receivable5000Asset
14Mar 31, 2026Rent1200Expense
15Mar 31, 2026Cash1200Asset
16Apr 6, 2026Wages1800Expense
17Apr 6, 2026Cash1800Asset
18Apr 10, 2026Loan500Liability
19Apr 10, 2026Cash500Asset

Download this sheet as an Excel practice file (.xlsx)

Use the same ledger lines as your P&L

A balance sheet shows what a business owns (assets), what it owes (liabilities) and what's left for the owners (equity) on one date. Like the profit and loss statement, it's a summary of the ledger, so build it with formulas from the ledger detail rather than typing balances.

The sample in A:D is the first months of a small workshop: the owner puts in 10,000, borrows 8,000, buys equipment, invoices a customer, buys materials on credit, gets paid and pays rent. Every transaction is two lines, a debit and a credit of the same amount, as in a general ledger. The chart of accounts in G:H gives each account a type, and column E looks it up with XLOOKUP, returning "Missing" for any account you haven't classified.

Which account gets which type is a classification choice for you or your accountant. The formulas below work the same whatever you decide.

=XLOOKUP(B8,G2:G11,H2:H11,"Missing")

Result: Asset

Asset: money a customer owes you (accounts receivable) is something the business owns. In Excel 2019 or earlier, use =IFERROR(VLOOKUP(B8,$G$2:$H$11,2,FALSE),"Missing").

Balances add up all history, not one month

This is the step people get wrong when they copy a P&L layout. A P&L adds one period. A balance sheet balance is everything that ever happened to the account up to the date: the cash you have on 31 March is the opening deposit plus every receipt minus every payment since the business started.

So the only date condition is "<"&K1+1, where K1 holds the as-of date. Adding 1 and using "<" includes the whole last day even if your export has times in its dates. Assets normally carry debit balances, so the formula is debits minus credits.

=SUMIFS(C2:C19,B2:B19,"Cash",A2:A19,"<"&K1+1)-SUMIFS(D2:D19,B2:B19,"Cash",A2:A19,"<"&K1+1)

Result: 15800

15,800 of cash on 31 March. The April wages and loan repayment are already in the ledger but fall after the date, so they don't count.

What goes wrong with a month window?

Limiting the dates to March gives you March's movement, not the balance: the customer's 5,000 payment minus the 1,200 rent. That's useful for a cash flow view, but it isn't what's in the bank.

=SUMIFS(C2:C19,B2:B19,"Cash",A2:A19,">="&DATE(2026,3,1),A2:A19,"<"&K1+1)-SUMIFS(D2:D19,B2:B19,"Cash",A2:A19,">="&DATE(2026,3,1),A2:A19,"<"&K1+1)

Result: 3800

3,800: March's change in cash. The balance is 15,800.

Total assets, liabilities and owner capital

Swap the account name for the type to get the section totals in J2:K4. Assets are debits minus credits. Liabilities and equity normally carry credit balances, so flip the order: credits minus debits. Lock the ranges and the date cell with $ so the formulas can be copied around the statement.

For a detailed statement, list each account under its heading and use the account-name version from the last section for each line, with the type total underneath. The type version is the short form, and a new account only needs a row in the chart of accounts.

=SUMIFS(D2:D19,E2:E19,"Liability",A2:A19,"<"&K1+1)-SUMIFS(C2:C19,E2:E19,"Liability",A2:A19,"<"&K1+1)

Result: 10200

10,200 of liabilities: 2,200 owed to the materials supplier and the 8,000 loan. Total assets in K2 are 24,300 (cash 15,800, receivables 2,500, equipment 6,000) and owner capital in K4 is 10,000.

Bring this year's profit into equity

Assets of 24,300 minus liabilities of 10,200 leaves 14,100, but owner capital is only 10,000. The missing 4,100 is the profit the business has made and kept. Until the books are closed for the year, it still sits in the revenue and expense accounts, so the balance sheet has to pull it in as a separate equity line.

Current-year profit is the P&L for the year so far: credits minus debits on the Revenue, COGS and Expense lines from 1 January to the as-of date. SUMIFS accepts the three types as an array constant, and SUM adds the three results. If you already have a P&L sheet, you can link to its year-to-date net profit instead.

=SUM(SUMIFS(D2:D19,E2:E19,{"Revenue","COGS","Expense"},A2:A19,">="&DATE(YEAR(K1),1,1),A2:A19,"<"&K1+1))-SUM(SUMIFS(C2:C19,E2:E19,{"Revenue","COGS","Expense"},A2:A19,">="&DATE(YEAR(K1),1,1),A2:A19,"<"&K1+1))

Result: 4100

4,100: sales of 7,500 minus materials of 2,200 and rent of 1,200. Total equity (K6) is 10,000 + 4,100 = 14,100.

What about profit from earlier years?

Once your bookkeeping software closes a year, last year's profit moves into a retained earnings account, which you'd type as Equity in the chart of accounts. If your ledger runs over several years without closing entries, add a Retained earnings line that sums the P&L types before DATE(YEAR(K1),1,1). Ask your accountant how your books handle it.

Where do owner drawings go?

Give a Drawings account the type Equity. Drawings are debits, so credits minus debits makes them reduce equity automatically.

The check cell: assets - liabilities - equity = 0

Every double-entry transaction has equal debits and credits, so assets always equal liabilities plus equity. Put that test in a cell and keep it visible. If it isn't 0, a transaction has only one side, an account is typed "Missing", or a line was posted with the wrong date.

Change K1 to another date and the whole statement moves with it. On 30 April, after the wages and the loan repayment, assets are 22,000, liabilities 9,700 and equity 12,300: the check is still 0.

  1. To show a comparison, copy column K to L and put the earlier date (for example 31 December) in L1. Every formula already points at the date in its own column if you lock only the row: use K$1 instead of $K$1.
  2. Format the statement with Home > Number Format > Accounting, or a custom #,##0;(#,##0) so negative balances show in brackets.
=K2-K3-K6

Result: 0

0: total assets of 24,300 equal liabilities of 10,200 plus equity of 14,100.

My check isn't 0. Where do I look first?

Count the unclassified lines, then compare total debits and credits up to the date. If =COUNTIF(E2:E19,"Missing") is 0 and debits equal credits, the balance sheet will balance; a trial balance shows which account is off.

=SUMIFS(C2:C19,A2:A19,"<"&K1+1)-SUMIFS(D2:D19,A2:A19,"<"&K1+1)

Result: 0

0: debits and credits up to 31 March are equal.

Common mistakes

Using a month window for balances

A balance is all history up to the date. Use only "<"&as-of+1 as the date condition.

Forgetting current-year profit

Until the year is closed, profit sits in the P&L accounts. Add it to equity as its own line, or the statement is out by exactly the profit.

Same sign rule for every section

Assets are debits minus credits; liabilities and equity are credits minus debits. One formula for all three gives negative liabilities.

Typing balances from the bank statement

Typed numbers don't move when you change the date or post a late entry. Keep every line a formula and let the check cell catch problems.

FAQ

What is the balance sheet formula?

Assets = liabilities + equity. In Excel, each total is a SUMIFS over the ledger up to the as-of date, and a check cell of assets - liabilities - equity should show 0.

How do I make a balance sheet from a trial balance in Excel?

Add a Type column to the trial balance, then use SUMIF on that type for each section: debit balances for assets, credit balances for liabilities and equity. Remember to add the net of the revenue and expense lines to equity as current-year profit.

What's the difference between a balance sheet and a P&L?

A P&L covers a period (income and costs for a month or year). A balance sheet is a snapshot on one date (what you own and owe). The P&L's profit feeds the balance sheet's equity.

Does Excel have a balance sheet template?

File > New has a few to search for, and they work if you type each balance. Building it from your ledger, as above, keeps it up to date and lets the check cell catch mistakes.

Functions used

Related guides

Updated