Mapping a trial balance to a balance sheet is a core part of the financial close process. While it may seem simple at first glance, getting it right in Excel requires a clear understanding of how assets, liabilities, and equity roll up—and how your net profit or loss from the P&L flows into retained earnings.
In this article, we’ll walk through how to build a balance sheet from a trial balance in Excel—step by step. You’ll also learn how tools like PivotXL can automate this process and save you hours every month.
Free Excel Template: Trial Balance to Balance Sheet
👉 For a complete walkthrough on mapping trial balances to both the Income Statement and Balance Sheet, check out our full guide here: How to Prepare Financial Statements from Trial Balance
🔍 Want to try it yourself?
Download our free Excel workbook and follow along with the example. You’ll be able to click through each formula, see how trial balance accounts are mapped and grouped, and understand how they roll up into a complete Profit & Loss Statement and Balance Sheet. It’s a hands-on way to learn the structure of financial reporting from the ground up.
👉 Download the free trial balance to P&L Excel workbook
Step 1: Start with a Balanced Trial Balance
A trial balance is a list of all account balances from your general ledger, split into debits and credits. To proceed, make sure:
- Total debits = total credits
- All accounts are classified correctly (Assets, Liabilities, Equity, Revenues, Expenses)
At this point, your P&L net profit or loss is not yet part of the trial balance—this will come into play when we finalize retained earnings.
Step 2: Classify Trial Balance Accounts
For a balance sheet, focus on the permanent accounts:
- Assets (e.g., Cash, Accounts Receivable, Inventory)
- Liabilities (e.g., Accounts Payable, Loans)
- Equity (e.g., Retained Earnings, Share Capital)
Use a mapping table (or Excel formula like XLOOKUP) to assign each account to its correct line item on the balance sheet.
If you’re building this from a trial balance that includes temporary accounts like revenue and expenses, make sure those flow into net income, which we’ll address in the next step.
Step 3: Link Net Income from the P&L
To complete the balance sheet, you need to calculate Net Income from the Income Statement and add it to Retained Earnings.
- Net Income = Revenues – Expenses (from your P&L)
- Adjust Retained Earnings = Prior Retained Earnings + Net Income
This is the bridge between the P&L and Balance Sheet.
🧠 Note: If you’re only mapping a balance sheet and skipping the P&L, you must still compute net income separately and include it.
👉 For a full explanation, see our pillar guide:
How to Prepare Financial Statements from Trial Balance
Step 4: Build the Balance Sheet in Excel
Now, structure your Balance Sheet using grouped accounts:
- Assets
- Current Assets
- Fixed Assets
- Liabilities
- Current Liabilities
- Long-term Liabilities
- Equity
- Share Capital
- Retained Earnings (adjusted)
Make sure the equation balances:
Assets = Liabilities + Equity
If it doesn’t, review your classifications and net income linkage.
Step 5: Validate with a Check Formula
Add a formula to confirm the balance:
=Assets_Total - (Liabilities_Total + Equity_Total)
The result should be zero. If not, double-check your mappings and totals.
Automate It With PivotXL
Doing this every month manually can be time-consuming. PivotXL automates the entire process:
- Upload your trial balance
- Set account groupings once
- Click “Get Data” and instantly populate your Balance Sheet
- Net Income from the P&L flows automatically into Retained Earnings
- Built-in validations ensure accuracy
This turns a manual, error-prone task into a repeatable, one-click process.
Summary
| Step | Action |
|---|---|
| 1 | Start with a balanced trial balance |
| 2 | Classify accounts as assets, liabilities, equity |
| 3 | Add net income to retained earnings |
| 4 | Group accounts into balance sheet structure |
| 5 | Validate that Assets = Liabilities + Equity |
