Balance Sheet Format in Excel: How to Build One Step by Step

A practical guide to laying out a balance sheet in Excel — standard format, subtotal formulas, a balance check, the retained earnings link and a fully worked example.

← Back to Resources

A balance sheet lists what a business owns (assets), what it owes (liabilities) and the owners’ stake (equity) on one date. This guide shows how to lay one out in Excel: the standard format, the formulas for every subtotal, a check that proves the two sides agree, the retained earnings link to the profit and loss account, and a fully worked example you can rebuild cell by cell.

What a balance sheet shows

Every balance sheet rests on one equation: Assets = Liabilities + Equity. Assets are resources the business controls; liabilities are amounts it owes to others; equity is what is left for the owners — the capital they put in plus profits kept in the business. Because every transaction affects both sides, a correctly prepared balance sheet always balances.

Unlike a profit and loss account, which covers a period, a balance sheet is a snapshot at a single date, such as 31 December. Under IFRS it is formally called the statement of financial position.

The standard balance sheet format

For a management balance sheet in Excel, the vertical (report) format is the easiest to read and print. Group the lines like this:

Current assets — cash, receivables, inventory and prepayments: items expected to turn into cash or be used up within 12 months.
Non-current assets — equipment, vehicles, property and other long-term assets, shown at cost less accumulated depreciation.
Current liabilities — supplier balances, accrued expenses, tax due and the part of any loan repayable within 12 months.
Non-current liabilities — borrowings and other amounts due after more than 12 months.
Equity — share or owner’s capital plus retained earnings.

The order of sections varies. Statements prepared under IFRS often show non-current items first, while many US-style statements start with current assets. Either is acceptable for an internal balance sheet; what matters is a consistent layout and correct classification. Statutory accounts must follow the presentation rules that apply to the business, which this guide does not cover.

How to build a balance sheet in Excel, step by step

Set up the columns. Put line labels in column A and amounts in column B. Add a title and the balance sheet date at the top, and format column B with a thousands separator.
Enter the asset lines. List current assets, then a subtotal with =SUM(B5:B8). Enter non-current assets at cost, with accumulated depreciation as a negative number on its own line, and subtotal them.
Total the assets. Add the two subtotals with =B9+B15 — never re-add every line, or a later insertion can be counted twice.
Enter the liability lines. Split each loan into the part due within 12 months (current) and the rest (non-current). Subtotal each section and add them for total liabilities.
Build the equity section. Enter share or owner’s capital, and link retained earnings to a roll-forward rather than typing it (step 6).
Roll forward retained earnings. Opening retained earnings + net profit for the year − dividends or drawings = closing retained earnings. Take net profit from your profit and loss account.
Add the balance check. Total assets minus total liabilities and equity should be zero. A check cell turns an invisible error into a visible one.

Sign convention: enter accumulated depreciation and dividends as negative numbers and add everything with SUM. If you enter them as positives, subtract them explicitly — but never both, or the figure is deducted twice.

Worked example: balance sheet format in Excel

A small trading business prepares its balance sheet at 31 December. All figures are fictional. Amounts are in column B; the right-hand column shows what to type for each subtotal.

Worked example: balance sheet as at 31 December (column B)
Row	Line (column A)	Amount	Formula in column B
Assets
4	Current assets
5	Cash and bank	18,400	input
6	Accounts receivable	24,600	input
7	Inventory	31,200	input
8	Prepaid expenses	2,800	input
9	Total current assets	77,000	=SUM(B5:B8)
10	Non-current assets
11	Equipment at cost	64,000	input
12	Less: accumulated depreciation	−21,500	input
13	Vehicles at cost	28,000	input
14	Less: accumulated depreciation	−9,800	input
15	Total non-current assets	60,700	=SUM(B11:B14)
16	Total assets	137,700	=B9+B15
Liabilities
19	Current liabilities
20	Accounts payable	19,300	input
21	Accrued expenses	4,100	input
22	Tax payable	3,600	input
23	Bank loan — due within 12 months	6,000	input
24	Total current liabilities	33,000	=SUM(B20:B23)
25	Non-current liabilities
26	Bank loan — due after 12 months	24,000	input
27	Total non-current liabilities	24,000	=B26
28	Total liabilities	57,000	=B24+B27
Equity
31	Share capital	30,000	input
32	Retained earnings	50,700	=E6
33	Total equity	80,700	=B31+B32
34	Total liabilities and equity	137,700	=B28+B33
35	Balance check (should be 0)	0	=B16-B34

Retained earnings in B32 links to a small roll-forward beside the balance sheet. Opening retained earnings of 38,900, plus the year’s net profit of 19,800, less dividends of 8,000, gives 50,700.

Retained earnings roll-forward (columns D–E)
Row	Line (column D)	Amount	Formula in column E
3	Opening retained earnings	38,900	input
4	Net profit for the year (from the P&L)	19,800	input
5	Dividends paid	−8,000	input (negative)
6	Closing retained earnings	50,700	=SUM(E3:E5)

Total assets are 137,700 (77,000 current + 60,700 non-current). Total liabilities are 57,000 and total equity is 80,700, so liabilities and equity also total 137,700. The check in B35 returns 0.

The balance check: proving the two sides agree

The simplest check is =B16-B34, which should return 0. For a check that also explains itself, use =IF(ROUND(B16-B34,2)=0,"Balances","Out by "&TEXT(B16-B34,"#,##0")). ROUND avoids a false alarm from tiny decimal differences when figures come from other sheets.

A check of zero proves only that the two sides agree, not that every figure is right. A misclassified item — a long-term loan shown as current, for example — still balances. Review classification separately.

When the sheet does not balance, look for:

A difference equal to the year’s net profit — retained earnings not updated.
A difference of twice a figure — an item entered with the wrong sign.
A difference divisible by 9 — two digits transposed (for example 2,850 typed as 2,580).
Cash that does not agree to the bank — reconcile it first; see the bank reconciliation guide.

Key figures from the balance sheet

Once the balance sheet balances, a few lines of formulas turn it into the figures lenders and managers ask about:

Key figures from the example
Measure	Formula	Result
Working capital	=B9-B24	44,000
Current ratio	=B9/B24	2.33
Quick ratio (cash + receivables)	=(B5+B6)/B24	1.30
Debt to equity (total liabilities ÷ equity)	=B28/B33	0.71

Here current assets cover current liabilities 2.33 times, and the business has 0.71 of liabilities for every 1 of equity. Definitions vary: some analysts use only interest-bearing debt in debt to equity, and some include prepayments in the quick ratio. State the definition you use. To check these figures quickly, use the free Working Capital Calculator; for returns on the balance sheet, see the ROA and ROE calculators.

Common mistakes

Typing totals instead of using formulas. A hard-coded total does not update when a line changes, and the balance check is the only thing that will catch it.
Loans not split. The instalments due within 12 months belong in current liabilities; leaving the whole loan as non-current overstates working capital.
Retained earnings not linked to profit. If net profit is not added and dividends are not deducted, the sheet will be out by exactly those amounts.
Overdraft netted against cash. An overdraft is a liability. Show it under current liabilities rather than as negative cash, unless your accounting framework allows offsetting.
Mixed dates. Every figure must be as at the same date. A receivables total from one date and a bank balance from another will not balance.
Subtotals added into totals. Use subtotals in the grand total, not subtotals plus their detail lines.

Use the Balance Sheet Template

If you would rather not build the layout yourself, the BabaDigit Balance Sheet Template for Excel sets out current and non-current assets and liabilities and equity for two periods side by side, with a plain-English balance test that allows for rounding, plus working capital, the current ratio and debt to equity with the movement between periods.

View the Balance Sheet Template

Excel 2016 or later, or Microsoft 365 · no macros · includes clearly labelled sample data.

Frequently asked questions

What is the basic format of a balance sheet?

Assets (current, then non-current), liabilities (current, then non-current) and equity, with total assets equal to total liabilities plus equity. Some frameworks list non-current items first; keep whichever order you choose consistent.

Why does my balance sheet not balance?

Usually because retained earnings were not updated for the year’s profit and dividends, an item has the wrong sign, or a figure was typed instead of calculated. The size of the difference is often the clue — see the balance check section above.

Where does net profit go on the balance sheet?

It is added to retained earnings in the equity section, after deducting dividends or owner’s drawings. That link is what connects the profit and loss account to the balance sheet.

Is accumulated depreciation an asset or a liability?

Neither. It is a deduction from the cost of the related asset, so it appears as a negative line within non-current assets.

Is this format suitable for statutory accounts?

It is a management format. Statutory or filed accounts must follow the presentation and disclosure rules of the accounting framework and jurisdiction that apply, so check with your accountant.

Related resources

Bank Reconciliation guide — confirm the cash figure before it goes on the balance sheet.
Accounts Receivable Aging Report guide — support the receivables balance customer by customer.
Working Capital Calculator — current assets minus current liabilities, with the current ratio.

Balance Sheet Template Profit & Loss Statement Template Financial Ratio Analysis Template Financial Statements Pack All resources

This guide is general information, not financial, accounting or legal advice. The worked example uses fictional figures. Written and reviewed by Ghayas Uddin, MS (Finance), financial analyst. Last reviewed 18 October 2026. Found an error? Email support@babadigit.com.