Mapping a Trial Balance to Balance Sheet is a core part of closing the books. 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 profit or loss from the P&L flows into retained earnings.
In this guide, we’ll walk through how to convert a trial balance to a balance sheet using Excel — both manually and with automation using PivotXL.
What Is a Trial Balance?
A trial balance is a list of all general ledger accounts with their debit and credit balances. It’s used to confirm that total debits equal total credits and serves as the foundation for preparing financial statements.
Each trial balance line item should fall under one of these categories:
- Assets (e.g., Cash, Accounts Receivable, Inventory)
- Liabilities (e.g., Accounts Payable, Loans)
- Equity (e.g., Share Capital, Retained Earnings)
- Revenues and Expenses (used to calculate Net Income)
To build a balance sheet, we only use the first three—assets, liabilities, and equity—but net income must first be calculated via the P&L and then transferred to retained earnings.
Why You Need a P&L First
Before you can build your balance sheet, you must calculate net income from the Profit & Loss statement. That value becomes part of your retained earnings under equity.
👉 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-by-Step: Manual Excel Method
Step 1: Categorize Trial Balance Accounts
Open your trial balance in Excel and classify each account as:
- Balance Sheet account (Asset, Liability, Equity)
- P&L account (Revenue or Expense — used for Net Income)
You can use a Chart of Accounts mapping tab and Excel’s VLOOKUP or XLOOKUP to assign each account to a category.
Step 2: Group Balance Sheet Items
Use SUMIFS or a Pivot Table to group trial balance accounts by line items:
=SUMIFS(TrialBalance[Amount], TrialBalance[Group], "Current Assets")
Do this for all key balance sheet sections:
- Current Assets
- Non-Current Assets
- Current Liabilities
- Non-Current Liabilities
- Equity (including updated Retained Earnings)
Step 3: Link Net Income to Retained Earnings
Once you have your Net Income from the P&L, link it into your retained earnings line. For example:
=Prior_Retained_Earnings + Net_Income
This completes the equity section of the balance sheet.
Step 4: Build Your Balance Sheet Format
Create a dedicated worksheet or section for your Balance Sheet with this structure:
| Assets | |
|---|---|
| Current Assets | $XXX |
| Non-Current Assets | $XXX |
| Total Assets | $XXX |
| Liabilities & Equity | |
|---|---|
| Current Liabilities | $XXX |
| Non-Current Liabilities | $XXX |
| Retained Earnings | $XXX |
| Other Equity | $XXX |
| Total Liabilities & Equity | $XXX |
Finally, verify:
= Total_Assets = Total_Liabilities + Total_Equity
✅ If they match, your Balance Sheet is complete!
Automating Balance Sheet Creation with PivotXL
While Excel works for small setups, it gets messy fast across months and entities. That’s where PivotXL helps.
Here’s how it works:
- Upload Your Trial Balance from QuickBooks, Zoho, or CSV
- Map Accounts to Balance Sheet and P&L line items (only once)
- Click “Get Data” – and PivotXL will populate Balance Sheet, P&L, and Cash Flow instantly
- Adjust Forecasts and review automated ratios like Working Capital and Debt-to-Equity
You can refresh it each month with new trial balances—no rework needed.
👉 Try PivotXL Free – No Credit Card Needed
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 Financial Statements workbook
✅ Final Thoughts
If you’re looking to understand financial reporting from the ground up, building a balance sheet manually is a valuable exercise. But for ongoing reporting, automating the process using PivotXL will save hours each month—and eliminate formula errors along the way.
Whether you’re a founder, accountant, or FP&A analyst, getting this foundation right sets you up for reliable reporting.
