Converting a trial balance to a balance sheet in Excel is a foundational task in accounting. It’s how you turn raw ledger data into structured financial insight. While the concept is simple — Assets = Liabilities + Equity — building a balance sheet from a trial balance in Excel takes a solid understanding of account mapping and formulas.
In this guide, we’ll walk through the full process using Excel, with a free downloadable template and an option to automate the workflow using PivotXL.
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: Import and Review the Trial Balance
Start by pasting your trial balance data into Excel. This includes:
- Account Code or Name
- Debit or Credit amount
- Account Type (if available)
Ensure your total debits equal total credits:
=SUM(Debits) - SUM(Credits) → should be 0
If the balance doesn’t match, fix the discrepancies before proceeding.
Step 2: Classify Accounts into Balance Sheet Categories
Create a mapping table that assigns each trial balance account to one of the following categories:
- Assets
- Cash, Accounts Receivable, Inventory
- Liabilities
- Accounts Payable, Accrued Expenses, Loans
- Equity
- Share Capital, Retained Earnings
Use Excel’s VLOOKUP or XLOOKUP to pull in the classifications from your Chart of Accounts.
Step 3: Adjust Retained Earnings with Net Income
If your trial balance includes revenue and expense accounts (P&L accounts), calculate Net Income:
=SUM(Revenue Accounts) - SUM(Expense Accounts)
Then add Net Income to Retained Earnings, since profits/losses ultimately roll into equity.
🧠 Pro Tip: Link to your P&L tab and pull net income automatically.
Step 4: Group and Sum Using SUMIFS
For each balance sheet line item (e.g., Current Assets, Long-Term Liabilities), use:
=SUMIFS(Amount, AccountGroup, "Current Assets")
Repeat this for every group. This helps roll up individual accounts into final line items.
📄 Step 5: Build the Balance Sheet
Now organize the grouped line items into a formal balance sheet layout:
Assets
- Current Assets
- Non-Current Assets
Total Assets
Liabilities
- Current Liabilities
- Long-Term Liabilities
Total Liabilities
Equity
- Share Capital
- Retained Earnings (+ Net Income)
Total Equity
= Total Assets - (Total Liabilities + Total Equity) → should be 0
This formula confirms that your balance sheet balances.
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
⚙️ Automate With PivotXL
Doing this monthly? Automate it with PivotXL, an Excel-based financial reporting platform:
- Import your trial balance (CSV, QuickBooks, Xero, etc.)
- Set account groupings once
- Get automated Balance Sheet (and P&L + Cash Flow) in Excel
- Validate accuracy with built-in checks
🔁 Summary
| Step | Description |
|---|---|
| 1 | Paste trial balance into Excel and validate it |
| 2 | Map accounts to balance sheet groups |
| 3 | Add net income to retained earnings |
| 4 | Use SUMIFS to group totals |
| 5 | Build and balance the sheet layout |
Building a balance sheet from a trial balance in Excel is a valuable skill — and once mastered, it opens the door to advanced reporting, forecasting, and automation.
