Blog · Board and management reporting · Finance and FP&A teams
Build a balance sheet in Google Sheets from closing account balances. Use an account map, statement formulas and a zero-difference check before comparing periods.
A balance sheet in Google Sheets starts with closing account balances, an account map and a check that assets equal liabilities plus equity. Keep the original ledger signs in the source sheet and change signs only for presentation. A balanced total is necessary, but complete mapping and supporting schedules establish whether the statement is usable.
Use a closing trial balance for a US company's September 30, 2026 reporting date. Confirm the entity, currency and close status before importing it. This layout assumes debit balances are positive and credit balances negative; an export with separate debit and credit columns needs a net debit balance calculated first.
Create three tabs: Source, Map and Statement. On Source, put date in A, account code in B and net debit balance in C. Keep account codes as text so leading zeros survive. There must be one closing balance per account and date, rather than a mixture of closing balances and transaction movements.
The general balance sheet guide covers the statement's meaning. Here the job is to build a controlled Sheets layout, including the formulas that turn a dated export into a statement.
On Map, use columns A:D for account code, reporting line, section and presentation sign. This example uses one source account for each reporting line; a real map may place several accounts on the same line.
| Account | Reporting line | Section | Presentation sign |
|---|---|---|---|
| 1000 | Cash | Assets | 1 |
| 1100 | Receivables | Assets | 1 |
| 1200 | Inventory | Assets | 1 |
| 1500 | Net property and equipment | Assets | 1 |
| 2000 | Accounts payable | Liabilities | -1 |
| 2500 | Long-term debt | Liabilities | -1 |
| 3000 | Paid-in capital | Equity | -1 |
| 3100 | Retained earnings | Equity | -1 |
Add reporting line in Source!D2, section in E2 and presentation sign in F2:
=IFERROR(VLOOKUP(B2,Map!$A$2:$D$100,2,FALSE),"UNMAPPED")
=IFERROR(VLOOKUP(B2,Map!$A$2:$D$100,3,FALSE),"UNMAPPED")
=IFERROR(VLOOKUP(B2,Map!$A$2:$D$100,4,FALSE),"UNMAPPED")
Copy each formula down its column. Google's VLOOKUP documentation recommends FALSE for an exact match. Approximate matching is inappropriate for account codes because it can return a different account's classification.
In G2 calculate the presentation balance, then fill down:
=IF(ISNUMBER(F2),C2*F2,NA())
An unmapped account must remain visible as an error. Do not replace it with a zero balance. Also check that the map contains no repeated account codes: a lookup returns one match and will not resolve conflicting classifications for you.
Put the reporting date in Statement!F1 as a real date. Enter section labels in A2:A9, reporting lines in B2:B9 and this formula in C2, copied down:
=SUMIFS(Source!$G$2:$G$1000,Source!$D$2:$D$1000,B2,Source!$A$2:$A$1000,$F$1)
SUMIFS adds values meeting multiple criteria. Both the line label and closing date must match here. Keep all ranges the same size, and extend them together when the export grows. Covirage is not affiliated with Google.
These are the complete source and statement balances for the example:
| Statement row | Reporting line | Source net debit balance (USD) | Presentation balance (USD) |
|---|---|---|---|
| 2 | Cash | 120,000 | 120,000 |
| 3 | Receivables | 180,000 | 180,000 |
| 4 | Inventory | 260,000 | 260,000 |
| 5 | Net property and equipment | 440,000 | 440,000 |
| 6 | Accounts payable | -210,000 | 210,000 |
| 7 | Long-term debt | -300,000 | 300,000 |
| 8 | Paid-in capital | -100,000 | 100,000 |
| 9 | Retained earnings | -390,000 | 390,000 |
Retained earnings here comes from the completed closing export and includes the company's reported accumulated result. Do not enter an invented earnings balance to force agreement. If the ledger leaves current earnings separate, map that supplied balance into equity under your reporting policy.
Add these statement controls:
| Cell | Calculation | Formula | Result (USD) |
|---|---|---|---|
| C11 | Total assets | =SUM(C2:C5) |
1,000,000 |
| C12 | Total liabilities | =SUM(C6:C7) |
510,000 |
| C13 | Total equity | =SUM(C8:C9) |
490,000 |
| C14 | Balance difference | =C11-C12-C13 |
0 |
All amounts are whole dollars, so no rounding adjustment is needed. Current assets are 120,000 + 180,000 + 260,000 = $560,000; against current liabilities of $210,000, working capital is $350,000. Classification matters even though it does not change total assets.
The source's signed balances must sum to zero for the selected date:
=SUMIFS(Source!$C$2:$C$1000,Source!$A$2:$A$1000,Statement!$F$1)
Check unmapped rows separately:
=COUNTIFS(Source!$D$2:$D$1000,"UNMAPPED",Source!$A$2:$A$1000,Statement!$F$1)
Both results must be zero. Compare the account count and balances with the original trial balance, then tie cash, receivables, inventory and debt to their supporting schedules. Equal missing debits and credits can leave the balance difference at zero.
Keep the account map under finance ownership. Record the extract date and close version, and retain each prior balance set. Use flux analysis to explain changes after reconciliation; use a separate cash flow workbook when the question concerns cash movements rather than closing balances.
Upload the closing financial workbook or Google Sheet; Covirage maps the columns, and its tools compute and check the supplied balances. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring a sample of your own data.
See Covirage for finance teams for the related financial reporting work.
Yes, if you also have complete opening balances and all movements through the reporting date. A month's transactions alone cannot establish closing assets, liabilities and equity. A closing trial balance is usually the simpler starting point for this workbook.
The balancing identity tests the relationship between totals, not each account's classification. Moving a balance between two asset lines leaves the check unchanged. Review the mapping, compare major lines with supporting schedules and investigate unusual changes before using the statement.
Normal liabilities usually appear as positive statement amounts, even when credits are negative in the source export. Apply a documented presentation sign through the account map. Preserve contra balances and unusual debit balances instead of making every source amount positive.
Append a complete dated closing balance set, extend every source formula and change the statement date. Check that accounts are unique within each date and that new codes have approved mappings. Keep the earlier closing set available for comparison.