Excel guide · 6 min read
How to make a balance sheet in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Date | Account | Debit | Credit | Type | Account | Type | As of | Mar 31, 2026 | ||
| 2 | Jan 2, 2026 | Cash | 10000 | Asset | Cash | Asset | Total assets | 24300 | |||
| 3 | Jan 2, 2026 | Owner capital | 10000 | Equity | Accounts receivable | Asset | Total liabilities | 10200 | |||
| 4 | Jan 5, 2026 | Cash | 8000 | Asset | Equipment | Asset | Owner capital | 10000 | |||
| 5 | Jan 5, 2026 | Loan | 8000 | Liability | Accounts payable | Liability | Current-year profit | 4100 | |||
| 6 | Jan 10, 2026 | Equipment | 6000 | Asset | Loan | Liability | Total equity | 14100 | |||
| 7 | Jan 10, 2026 | Cash | 6000 | Asset | Owner capital | Equity | Check (should be 0) | 0 | |||
| 8 | Feb 15, 2026 | Accounts receivable | 7500 | Asset | Sales | Revenue | |||||
| 9 | Feb 15, 2026 | Sales | 7500 | Revenue | Materials | COGS | |||||
| 10 | Mar 3, 2026 | Materials | 2200 | COGS | Rent | Expense | |||||
| 11 | Mar 3, 2026 | Accounts payable | 2200 | Liability | Wages | Expense | |||||
| 12 | Mar 20, 2026 | Cash | 5000 | Asset | |||||||
| 13 | Mar 20, 2026 | Accounts receivable | 5000 | Asset | |||||||
| 14 | Mar 31, 2026 | Rent | 1200 | Expense | |||||||
| 15 | Mar 31, 2026 | Cash | 1200 | Asset | |||||||
| 16 | Apr 6, 2026 | Wages | 1800 | Expense | |||||||
| 17 | Apr 6, 2026 | Cash | 1800 | Asset | |||||||
| 18 | Apr 10, 2026 | Loan | 500 | Liability | |||||||
| 19 | Apr 10, 2026 | Cash | 500 | Asset |
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.
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.
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.
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.
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.
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.
- 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.
- Format the statement with Home > Number Format > Accounting, or a custom #,##0;(#,##0) so negative balances show in brackets.
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.
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
SUMIFSAdds with several conditionsXLOOKUPModern lookup in any directionVLOOKUPVertical lookupIFERRORFallback when a formula errorsSUMAdds numbersDATEBuilds a dateYEARYear of a dateCOUNTIFCounts cells that match
Related guides
- How to build a fixed asset register in Excel
- How to make a cash flow statement in Excel
- How to calculate retained earnings in Excel
- How to make a profit and loss statement in Excel
- How to calculate the current ratio and quick ratio in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- How to calculate inventory shrinkage in Excel
Updated