Trial Balance from an Excel Ledger
Last updated: 27 June 2026
A trial balance is a list of all ledger account closing balances at a point in time. Its sole purpose is to confirm that total debits equal total credits — a prerequisite for producing a balance sheet or P&L. Indian SMBs running accounts in Excel can generate one automatically with a few SUMIF formulas.
Key takeaways
- A trial balance lists every ledger account with its debit or credit closing balance; the two columns must agree.
- It is not a financial statement — it is an internal arithmetic check run before finalising accounts.
- Run it monthly to catch posting errors early, not just at year-end.
- Excel's
SUMIFfunction can pull closing balances from a ledger sheet into a TB template automatically. - A trial balance catches many common errors but will miss errors of omission, errors of commission, and compensating errors.
- Adjusting entries (accruals, prepayments, depreciation) turn an unadjusted TB into an adjusted TB, which is the direct input for the P&L and balance sheet.
Fact box. Under double-entry bookkeeping, every transaction is posted as a debit to one account and an equal credit to another. If the trial balance does not agree, at least one posting is wrong — but agreement does not prove that all postings are correct.
What is a trial balance?
A trial balance is a two-column schedule — Debit and Credit — that lists the closing balance of every ledger account as at a specific date. If your books are in balance, SUM(Debit column) = SUM(Credit column).
It is run at three natural points in the accounting cycle:
- Monthly — to catch errors before they compound.
- Quarter-end — to support advance tax calculations and management accounts.
- Year-end — before preparing the audited financial statements.
The trial balance is not itself a financial statement. It is the internal working document from which the P&L and balance sheet are assembled.
How to structure a trial balance in Excel
Column layout
| Column | Header | Notes |
|---|---|---|
| A | Account Code | Matches your chart of accounts |
| B | Account Name | e.g., "Trade Receivables", "Sales", "Salaries" |
| C | Debit (₹) | Closing debit balance, or blank |
| D | Credit (₹) | Closing credit balance, or blank |
Keep one row per account. Do not mix debit and credit in the same cell using signs — keep them in separate columns. The check row at the bottom should show SUM(C) = SUM(D).
Step-by-step: pulling balances with SUMIF
Assume your general ledger is on a sheet named Ledger with columns: Account (account name), Debit, Credit.
Step 1. On your TB sheet, enter all account names in column B (rows 2 onwards).
Step 2. In C2 (Debit balance for the first account), enter:
=SUMIF(Ledger[Account],B2,Ledger[Debit])
This sums every debit posting in the ledger that belongs to the account named in B2.
Step 3. In D2 (Credit balance), enter:
=SUMIF(Ledger[Account],B2,Ledger[Credit])
Step 4. Copy both formulas down for every account row.
Step 5. At the bottom, add a check row:
=SUM(C:C)-SUM(D:D)
If this returns zero, your books balance. If it returns any other number, there is a posting error to investigate.
Fact box. Excel Tables (Insert → Table) make ledger references dynamic — new rows added to the ledger are automatically picked up by
SUMIFformulae referencing the Table column headers. Name your TableLedgerfor the formulas above to work without modification.
Net balance vs gross columns
Some accountants prefer a single "Net Balance" column with debits as positive and credits as negative (or vice versa). The two-column layout shown above is more transparent and is the standard format for Indian trial balances submitted to auditors or tax consultants.
Errors a trial balance catches vs errors it does not
Errors it catches
- Arithmetic errors — a debit posted as ₹1,000 when it should be ₹10,000 will throw the columns out of balance.
- One-sided entries — a debit posted without its matching credit, or vice versa.
- Transposition errors — posting ₹5,430 instead of ₹5,340 changes the total and breaks the balance.
Errors it does NOT catch
| Error type | Why the TB still balances |
|---|---|
| Error of omission | A transaction was never recorded; both debit and credit are missing, so totals still agree. |
| Error of commission | Correct amount, but posted to the wrong account within the same side (e.g., debit to Rent instead of Repairs). |
| Compensating errors | Two separate errors of equal and opposite amounts cancel each other out. |
| Error of principle | Revenue posted as a liability — the double-entry is complete but the classification is wrong. |
This is why a balanced trial balance is a necessary, not sufficient, condition for correct accounts.
Unadjusted vs adjusted trial balance
The unadjusted trial balance reflects only the transactions actually posted during the period — no accruals, no prepayments, no depreciation.
The adjusted trial balance adds the period-end adjusting entries:
- Accrued expenses (e.g., salary payable for the last week of March not yet paid)
- Prepaid expenses (e.g., insurance paid in advance for the next year)
- Depreciation for the period
- Provision for bad debts
- Advance income received but not yet earned
Once adjusted, the trial balance becomes the direct input for both the Profit & Loss statement and the Balance Sheet.
Common Indian SMB accounts in a trial balance
Typical accounts you will see in an Indian SMB trial balance:
- Debit balances: Fixed Assets, Inventories, Trade Receivables, Cash & Bank, Advance Tax, Input GST Credit, Salaries, Rent, Depreciation
- Credit balances: Share Capital, Reserves, Trade Payables, GST Payable (Output), TDS Payable, Bank Loans, Sales/Revenue
Fact box — GST treatment. GST collected from customers (Output Tax) and GST paid on purchases (Input Tax Credit) are both balance sheet items — they do not appear as income or expense in the P&L. Only the net GST payable appears as a current liability. Revenue in the P&L is always GST-exclusive.
How Ankeshan helps
Ankeshan's Excel accounting module maintains a running ledger and generates a trial balance automatically — no manual SUMIF setup required. Debit/credit totals update in real time as entries are posted, and the adjusted trial balance feeds directly into the P&L and Schedule III balance sheet templates. If you want to replace manual spreadsheets with a structured system built for Indian SMBs, join the waitlist.
Frequently asked questions
What is the difference between a trial balance and a balance sheet? A trial balance lists every ledger account (including revenue and expense accounts) as an arithmetic check. A balance sheet is a formal financial statement showing only assets, liabilities, and equity as at a date — revenue and expense accounts are closed off into retained earnings before the balance sheet is prepared.
How often should I run a trial balance? At minimum, run one at each quarter-end for advance tax and GST reconciliation. Monthly is better — catching errors early is far less painful than unwinding three months of compound mistakes at year-end.
My trial balance agrees but my balance sheet does not match my P&L. Why? The most common cause is that adjusting entries (depreciation, accruals, prepayments) have been made on the balance sheet but not reflected in the trial balance, or vice versa. Always work from the adjusted trial balance when preparing final statements.
Can I use a trial balance for GST reconciliation? Partially. The trial balance will show your output GST liability and input GST credit accounts, which you can compare with GSTR-3B totals. However, a full GST reconciliation also requires comparing GSTR-1 sales with books and GSTR-2B with purchase records — the trial balance alone is not sufficient.
What should I do if my trial balance does not agree? Start with the difference amount. If it is divisible by 9, suspect a transposition error. If it equals a specific transaction amount, search the ledger for that figure. If it is exactly double a posted amount, you likely have a one-sided entry or an entry posted to the wrong side.
Do sole proprietors and partnership firms need a trial balance? There is no statutory requirement for non-company entities to prepare a formal trial balance. However, it is best practice and essential if your turnover crosses the tax audit threshold (₹1 crore for business; ₹10 crore if ≥95% of receipts and payments are digital).
Sources and disclaimer
- Institute of Chartered Accountants of India (ICAI) — Principles of Accounting
- Income Tax Act 1961, Section 44AB (tax audit threshold)
- Companies Act 2013, Schedule III
General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.