Balance Sheet Template for Excel (With a Built-In Balance Check)
You typed the numbers in, the totals footed, and the sheet still doesn't balance — assets are out by 4,812.50 and you have no idea which side is wrong. Or worse: it balanced last month, you added two rows, and now it doesn't. A balance sheet that can't tell you the moment it stops balancing is one you re-audit by hand every period.
Below is a balance sheet format in Excel that prints "OK - balanced" or "OUT BY <amount>" in one cell, plus everything under it: the accounting equation, the current/non-current split, how to pull each line off a trial balance, and the four reasons balance sheets fail. (For converting a trial balance into a full statement set, see trial balance to financial statements.)
Get the template first
Download the HISAB balance sheet template for Excel. Plain .xlsx, no macros, no add-in needed.
It lays out five blocks — Current assets, Non-current assets, Current liabilities, Non-current liabilities, Equity — each with the usual line items already labelled and an automatic subtotal, plus calculated Total assets and Total liabilities. Columns for This year and Last year run across it. At the bottom, one row computes the raw difference (assets − liabilities − equity) and a Status row prints OK - balanced or OUT BY and the amount.
You type into the line-item rows only; every subtotal, total and both check rows are formulas. Companions: the profit and loss template, whose net profit feeds retained earnings, and the bank reconciliation template, where a wrong cash balance gets caught first.
The accounting equation, and why the check earns its cell
Every balance sheet is one identity rendered in two columns:
Assets = Liabilities + Shareholder's Equity
Zoho Books states it that way and gives the consequence: it "ensures both sides of the sheet are equal." Rearranged, the same identity defines equity — Equity = Assets - Liabilities, or "Owner's equity is equal to total assets minus total liabilities."
Hard-code the check rather than eyeballing two totals: the identity holds for a complete, correctly-signed set of balances, not for your spreadsheet. Insert a row outside a SUM range, paste over a formula, or forget to roll retained earnings forward, and it breaks silently.
Write the check formula
Assume your totals live in named cells TotalAssets, TotalLiabilities and TotalEquity (select the cell, type the name into the Name Box, press Enter). The downloadable file uses plain cell references instead. The minimum viable check:
=ROUND(TotalAssets-(TotalLiabilities+TotalEquity),2)ROUND(number, num_digits) takes both arguments; two decimals is right for currency. Make the result readable with IF(logical_test, value_if_true, [value_if_false]), remembering that text results must be wrapped in double quotes:
=IF(ROUND(TotalAssets-(TotalLiabilities+TotalEquity),2)=0,
"OK - balanced",
"OUT BY "&TEXT(TotalAssets-(TotalLiabilities+TotalEquity),"#,##0.00"))TEXT(value, format_text) gets you OUT BY 4,812.50 rather than OUT BY 4812.5. One caveat outside the US: the thousands separator follows regional settings — Microsoft notes "in other locales it might be a period (.)" — so accept the local rendering or drop the separator.
A tolerance variant, using =ABS(number):
=IF(ABS(TotalAssets-(TotalLiabilities+TotalEquity))<0.01,"BALANCED","CHECK")Don't tell yourself the tolerance is needed because Excel can't reach zero. Microsoft documents an optimisation: "Should an addition or subtraction operation result in a value at or very close to zero, Excel 97 and later will compensate for any error introduced as a result of converting an operand to and from binary." For plain sums of typed figures, an unwrapped subtraction usually does return 0. Wrap it in ROUND anyway: figures derived from percentage allocations, division or an import can still leave a sub-cent tail, and a check reading "OUT BY 0.00" is worse than useless. (Excel's other fix for this, Set precision as displayed, permanently truncates stored values — skip it.)
Make the check impossible to ignore
Select the status cell (B43 in the template), then Home tab > Styles group > Conditional Formatting > New Rule, and under "Select a Rule Type" choose "Use a formula to determine which cells to format". Enter a formula that is TRUE when the sheet is broken:
=$B$43<>"OK - balanced"Format it red fill, bold white text. Edit it later via Home > Conditional Formatting > Manage Rules, which opens the Conditional Formatting Rules Manager.
The structure, and why classification isn't decoration
Assets and liabilities each split on a one-year horizon. Zoho's definitions: current assets "can be converted to cash in a very short period of time... say less than a year," while non-current assets "take longer periods of time to get converted to cash... usually more than a year." Liabilities work the same way — current fall due within a year, non-current (long-term) extend past it.
That split isn't a spreadsheet convention someone invented; production software ships it in the chart of accounts. Zoho Books' asset types include Other Current Assets, Fixed Asset, Stock and Accounts Receivable; its liability types Other Current Liability, Long Term Liability and Accounts Payable; plus a single Equity type. Mirror those groupings and exports map cleanly.
Ordering inside a block is convention, not a published specification — just keep it consistent across periods.
Where each figure comes from: map the trial balance
Typing a balance sheet from a printed report is how transposition errors get in. Map it instead.
Step 1: paste the trial balance onto its own sheet
Four columns: Account, Group, Debit, Credit. Select the range, press Ctrl+T to make it a Table, then rename it TB in Table Design > Table Name. In Group, type the statement line the account rolls into — Accounts receivable, Long-term loans — spelled exactly as in column A of the balance sheet, and keep the labels unique. SUMIF matches text literally, leading spaces included, so if your labels are indented (the template's are), copy them rather than retyping.
Step 2: one SUMIF per statement line
SUMIF(range, criteria, [sum_range]) needs range and criteria; sum_range is optional, and it "should be the same size and shape as range." For a debit-balance line (assets), with the label in A8:
=SUMIF(TB[Group],$A8,TB[Debit])-SUMIF(TB[Group],$A8,TB[Credit])For a credit-balance line (liabilities and equity), flip it so the figure lands positive in the statement:
=SUMIF(TB[Group],$A8,TB[Credit])-SUMIF(TB[Group],$A8,TB[Debit])$A8 is mixed — absolute column, relative row — so it stays on the label column as you fill down the block. (F4 while editing cycles $A$1 → A$1 → $A1 → A1.) Don't drag these sideways, though: table column references behave like relative ones, so copying right quietly repoints TB[Debit] at the next column.
Step 3: subtotal with SUM, and leave room inside the range
SUM(number1,[number2],...) accepts up to 255 numbers and "will ignore text values and give you the sum of just the numeric values" where =A1+B1+C1 returns #VALUE! on a stray text cell. It also auto-adjusts when you insert or delete rows inside the referenced range — so insert new lines in the middle of a block, never immediately above or below the last figure. That one habit prevents the commonest cause of a sheet drifting out.
Step 4: add the prior-year column
Copy the This-year formulas one column right, then edit them to point at a second trial balance table (TB_PY) — copying alone won't repoint them. Duplicate the balance check for that column too.
Accumulated depreciation: the sign error that doubles your assets
Accumulated depreciation is a contra asset: it increases with a credit where a normal asset increases with a debit, it lowers the fixed asset's cost on the balance sheet, and it sits directly beneath that asset so historical cost stays visible. Net the two and you get book value — cost 50,000 less 10,000 accumulated carries at 40,000.
In Excel it must therefore be entered as a negative number inside the non-current asset block; the template's subtotal simply sums the block, so a positive is wrong. Enter it positive and total assets are overstated by twice the depreciation — the check then reports exactly double the accumulated figure, which is a useful fingerprint.
One honest nuance: contra-account presentation is the standard reporting convention, not universal software behaviour. Zoho Books tells users recording depreciation by recurring journal to debit Depreciation Expense and credit "the account under which the asset was recorded" — the asset directly — and it doesn't natively support contra accounts. So check how your system posts it before assuming an Accumulated Depreciation balance exists in the trial balance.
To show negatives in parentheses: select the cells, press Ctrl+1 (Windows) or Command+1 (Mac), choose Number or Currency under Category, then pick an option under Negative numbers. If it's missing, Microsoft notes it "may be due to an operating system setting" — use a custom format instead:
#,##0.00_);(#,##0.00)Four reasons a balance sheet doesn't balance — and how to find each
Work these in order. The first two account for most failures.
1. Retained earnings weren't rolled forward
Opening retained earnings plus the period's net profit, less dividends or drawings, is what makes the equity block reconcile. Import a fresh trial balance without carrying last year's closing retained earnings into the opening figure and you'll be out by exactly last year's profit. How to find it: compare the difference against prior-year net profit on your P&L; if they match to the cent, that's your answer.
2. A one-sided journal, or an incomplete trial balance
If the source doesn't balance, no formula will rescue you. Test the source, not the statement:
=ROUND(SUM(TB[Debit])-SUM(TB[Credit]),2)Anything other than 0 means the trial balance is the problem — a half-posted journal, a truncated export, or rows lost to a row cap or a filter left on.
3. Sign errors — accumulated depreciation, drawings, contra revenue
Covered above. The tell: the difference equals exactly twice some single figure on the sheet — scan for a number equal to half the variance.
4. Unposted opening balances or unmapped accounts
An account with a blank Group gets summed by nothing and vanishes from the statement. Compare all debits against the mapped ones:
=ROUND(SUM(TB[Debit])-SUMIF(TB[Group],"<>",TB[Debit]),2)The criteria "<>" means "not blank", so the result is whatever sits in unmapped rows — anything other than 0 needs a Group. Figures arriving as text disappear the same way, since text isn't summed; cleaning messy data in Excel covers text-stored numbers.
Ratios worth adding under the totals
Three ratios take five minutes and make the sheet decision-useful. Guard each with IFERROR(value, value_if_error), which traps #DIV/0! when current liabilities are zero. Thresholds below are Zoho Books' wording.
| Ratio | Formula | Reading |
|---|---|---|
| Current ratio | Current Assets / Current Liabilities | 1.0 or below means debts due within a year exceed assets |
| Quick ratio (acid test) | (Cash and its Equivalents + Marketable Securities + Accounts Receivable) / Current Liabilities | 1.0 or above means short-term liabilities are covered |
| Debt to equity | Total Liabilities / Total Shareholder's Equity | 1.0 or below is considered safe; 2.0 or higher, risky |
In Excel:
=IFERROR(CurrentAssets/CurrentLiabilities,"n/a")
=IFERROR((Cash+MarketableSecurities+AccountsReceivable)/CurrentLiabilities,"n/a")
=IFERROR(TotalLiabilities/TotalEquity,"n/a")The quick ratio deliberately excludes inventory. You'll also see it written (Current Assets - Inventory) / Current Liabilities: the two are not equivalent, since the subtraction version leaves prepaid expenses in the numerator. Pick one and label it. And don't stack nested IFs to grade the output — Microsoft's own advice on nesting up to 64 IFs is that "it's not at all advisable to do so."
When the bottleneck is getting the trial balance, not building the sheet
Everything above works with nothing but Excel, and it will keep working. What it doesn't fix is the export-map-repaste loop: every period you re-run the trial balance, re-map new account codes, and re-hunt whichever line moved.
That's where HISAB 360 fits — a paid Excel add-in with an AI chat panel docked inside Excel, connected to QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage. It pulls ledgers, journals and reports straight into the sheet, so the mapping table you built once points at fresh figures instead of a fresh CSV. Ask in plain English why a block moved and it writes the formulas or Power Query steps and applies them; double-clicking a total expands the rows behind it. Connecting QuickBooks and Xero to Excel covers the connection.
Honest limits: Windows desktop Excel only (365 or 2019 and later) — not Mac, not Excel on the web. It's paid after a 15-day free trial covering every feature plus 50 HISAB AI credits, no credit card, once per machine; there's no permanently free tier, nothing runs on a schedule and nothing gets emailed to you. Connecting a ledger means authorising it via OAuth yourself, and any write-back should be reviewed before approval and reconciled afterwards. Pricing and the feature list have the specifics.
The template needs none of that. Download it, map your trial balance once, and let the check cell do the auditing.
Frequently asked questions
Why does my balance sheet balance in one column but not the other?
Almost always the prior-year column's SUM ranges weren't extended when a row was inserted, or figures were pasted over formulas. Duplicate the check for every column.
Should I enter liabilities as positive or negative numbers?
Positive, with the statement doing the arithmetic — that's what the template assumes. The only figures entered negative are genuine contra items: accumulated depreciation, drawings, dividends, treasury stock. Never mix conventions.
Is a tiny difference like 0.000001 a real error or a rounding artefact?
Excel 97 and later compensate when an addition or subtraction lands at or very close to zero, so plain sums of typed figures normally return a clean 0. A residue that small usually came from a percentage allocation, a division or an import — wrap the check in ROUND(...,2), then find the derived cell.
Can I use the same template for a sole trader?
Yes. Rename the equity block — owner's capital, current-year profit, drawings — and the equation is unchanged: Equity = Assets - Liabilities holds regardless of entity type.
Do I need macros or an add-in for the balance check?
No. SUM, IF, IFERROR, ROUND, ABS, SUMIF, TEXT and conditional formatting are built in, and the file is a plain .xlsx. An add-in changes where the data comes from, not whether the check works.
Try HISAB 360 on your own workbook
HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.