Converting a trial balance to an income statement and balance sheet is a fundamental process in financial reporting. These two statements — the Profit & Loss (P&L) and the Balance Sheet — are essential for understanding business performance and financial position.

In this article, we’ll walk through how to prepare both reports from a trial balance using Excel, complete with examples, downloadable templates, and an automated method using PivotXL.

What Is a Trial Balance?

A trial balance is a list of all general ledger accounts and their balances at a point in time. It is used to ensure total debits equal total credits and forms the basis for preparing financial statements.

Example Trial Balance:

Account CodeAccount NameDebitCredit
1000Cash25,000–
1010Accounts Receivable15,000–
2000Accounts Payable–10,000
3000Retained Earnings–15,000
4000Sales Revenue–50,000
5000Salaries Expense30,000–
5010Rent Expense5,000–

Step 1: Create the Income Statement (Profit & Loss)

The Income Statement summarizes revenues and expenses over a period to calculate Net Profit or Loss.

Steps:

  1. Filter only revenue and expense accounts from the trial balance.
  2. Use SUMIFS to group totals by category (e.g., Operating Expenses).
  3. Calculate net income:
=SUM(Revenue Accounts) - SUM(Expense Accounts)

Output Example:

Income Statement
Sales Revenue
Salaries Expense
Rent Expense
Net Income

This Net Income will be added to Retained Earnings in the Balance Sheet.

Step 2: Build the Balance Sheet

The Balance Sheet shows the financial position:
Assets = Liabilities + Equity

Steps:

  1. Group trial balance accounts by category: Assets, Liabilities, and Equity.
  2. Add the Net Income from the P&L to the Retained Earnings line.
  3. Use Excel formulas like SUMIFS to calculate subtotals.

Output Example:

Balance Sheet
Assets
Cash
Accounts Receivable
Total Assets
Liabilities
Accounts Payable
Equity
Retained Earnings (Start)
Current Year Net Income
Total Equity
Total Liabilities + Equity

Now, your Balance Sheet is balanced, and both financial reports are complete.

Automate with PivotXL

If you’re doing this monthly, automating the process can save hours.

PivotXL is an Excel-based FP&A automation tool that:

  • Imports trial balances from CSV or QuickBooks/Xero
  • Maps accounts to P&L and Balance Sheet categories
  • Generates reports in pre-formatted Excel templates
  • Recalculates with one click for each reporting period

👉 Try PivotXL for Free

Download Excel Template

Trial Balance to Financial Statements Examples – Excel Template

👉 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

👉 Check out our FREE YouTube Course: Automating Trial Balance to Financial Statements in Excel with PivotXL (currently in progress!)


🔍 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

Summary Table

StepDescription
1Filter trial balance to show revenues and expenses
2Create Income Statement with net profit
3Use net profit to adjust Retained Earnings
4Build Balance Sheet using account categories
5Verify Assets = Liabilities + Equity

Key Takeaways

  • A trial balance is the source of truth for preparing both Income Statement and Balance Sheet.
  • Excel formulas like SUMIFS, XLOOKUP, and grouping logic are essential for mapping and summarizing accounts.
  • Net income from the P&L flows into the Balance Sheet’s equity section, linking both statements.
  • Tools like PivotXL can automate the entire workflow — saving time and improving accuracy.