Balance sheet in Excel: layout and balancing checks

A balance sheet Excel workbook starts with a trial balance, an account mapping and a control that proves assets equal liabilities plus equity. Build the statement from account balances at one reporting date, and keep the source signs separate from the display signs. A zero balancing difference is necessary, but completeness and classification need their own checks.

Start with a closed trial balance

The SEC's guide to financial statements describes a balance sheet as a snapshot of assets, liabilities and equity at a fixed point in time. For the accounting concepts behind each section, use the balance sheet guide; this page focuses on the workbook layout and its controls.

Use a post-close trial balance for one US company, in USD, as of September 30, 2026. The example assumes income has already been closed into retained earnings. All values below are whole dollars, with no rounding differences.

Keep the original export on a source worksheet. On a calculation worksheet named TB, place the following columns in A:F. Account codes should remain text so leading zeros survive. Each code appears once because this export contains one company, one currency and one date; a more detailed export may require those fields in its account key.

The source totals belong outside the account range. Do not include them as additional input accounts.

Calculate source signs and display signs

Column G is the signed balance, and H is the statement amount. Enter these formulas in row 2 and fill through row 12:

G2 = B2-C2
H2 = G2*F2

Equipment contributes 500,000 and accumulated depreciation contributes -120,000 to the same statement line. Their net amount is 380,000. Payables have a signed balance of -190,000; multiplying by -1 presents 190,000 within liabilities.

Keep the factor in the mapping rather than changing individual balances to absolute values. An absolute-value formula would turn a debit balance in a liability account into an ordinary positive liability and hide the exception.

Every account needs an approved statement line, group and factor. Review new accounts before refreshing the report. A mapping label should describe an accounting classification, not serve as a convenient place to put an unexplained difference.

Lay out the historical statement

Create a Statement worksheet with headings in row 1, line labels in A and amounts in B. The row numbers below make the subtotal formulas explicit. Only account-level rows use the mapping formula; subtotal labels are calculated separately.

For each account-level statement row, use this formula with that row's label. In B2:

=SUMIFS(TB!$H$2:$H$12,TB!$D$2:$D$12,A2)

Microsoft documents SUMIFS as a way to add values that meet specified conditions. Here the condition is the statement-line mapping. Covirage is not affiliated with Microsoft.

Prove the statement with independent checks

First, confirm the source control:

=SUM(TB!B2:B12)-SUM(TB!C2:C12)

That gives 1,150,000 - 1,150,000 = 0. Next, the statement gives 1,030,000 - 520,000 - 510,000 = 0. These are different controls: the first checks imported debits against credits; the second checks the presented accounting equation.

Then review completeness. The input contains 11 account rows, each with one mapping. A blank-label check is:

=COUNTBLANK(TB!D2:D12)

It must return zero, but it cannot detect a misspelled label that matches no statement row. Maintain a distinct list of permitted account-level statement labels and review every mapping against that list. Compare account-level statement totals by group with SUMIFS totals on TB, excluding subtotals from the statement side.

Finally, tie cash to the bank reconciliation, receivables and payables to their subledgers, inventory to its valuation schedule, and debt to the debt schedule. Record each reconciliation difference separately. For an unexplained equation difference, use the focused guide to a balance sheet not balancing.

Add a comparative period after the first one ties

Repeat the same layout for the prior date using its own trial-balance export and the same approved mappings. Calculate current minus prior for each line. Investigate changes using flux analysis, retaining the account detail beneath each movement.

A historical statement carries actual account balances. A projected balance sheet needs assumptions and links to future activity; keep that work separate from this close control.

Where it goes wrong

• Counting earnings twice. Adding current income to equity when the closed retained-earnings balance already contains it overstates equity.
• Adding subtotals to accounts. Including both current assets and their individual lines in total assets duplicates balances.
• Losing contra-account signs. Accumulated depreciation reduces assets; it is not another positive asset or an ordinary liability.
• Treating a zero difference as approval. Incorrect classifications and offsetting omissions can still produce a balanced statement.
• Mixing dates or currencies. Every balance in one column must share the reporting date and currency basis stated above it.

Check your own balance-sheet file in Covirage

Bring a trial-balance workbook with account mappings and comparative dates; Covirage maps the columns, and its tools compute and check account totals and period differences. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See Covirage for finance teams for the wider reporting context.