Download Trial Balance Excel Template

Trial Balance Template is a ready-to-use template in Excel, Google Sheets, and OpenOffice Calc that enables you to make the statement of all closing balances of ledger accounts on a certain date.

What is a Trial Balance Statement?

Trial Balance is a document in which we record the closing balances of all the ledger accounts at the end of the accounting period. It is a statement with all closing balances of ledger accounts on a certain date.

Usually, it is prepared at the end of an accounting period that assists us in the easy drafting of other financial statements.

Thus, preparing this document is the first and foremost step in the preparation of financial statements. (balance sheet, Income Statement, Cash flow Statement, etc)

Types of Trial Balance

There are 3 types of Trial Balance:

Unadjusted

Unadjusted Trial Balance is the first and foremost document to be prepared for final accounts. You need to prepare it before passing any adjustment entries. In other words, it is prepared to find the unadjusted ledger balances if there are any in our ledgers.

Adjusted

Adjusted Trial Balance is prepared to see whether the adjustment entries passed are correct or not after making all adjustments.

Post-Closing

Usually, the Post-Closing Trial Balance will be the same as above. Sometimes due to adjustments or unrecorded entries, the balances of ledgers might change. It helps add the opening balances for the next accounting year.

Purpose of Making a Trial Balance

• It is the first step towards the yearly closing.
• Helps to identify, rectify, and balance debit and credit entries of ledger accounts according to the double-entry system.
• It ensures the accuracy of extraction from the account balances from the ledgers.

How to Prepare Trial Balance?

Follow the below-given steps:

1. To prepare this document, all the ledger balances are separated into debit and credit balances.
2. On the debit side, you record the Assets and expenses accounts. Whereas, on the credit side you record the liabilities, capital, and income accounts.
3. The main purpose of preparing this document is to ensure that all the entries are made according to the ruling of the double-entry system.
4. Totals of both debit and credit side should match. If the totals don’t match, we need to investigate the differences. It is mandatory to resolve before the preparation of financial statements.
5. It is mandatory to resolve before the preparation of financial statements.

If the totals of debit balances and credit balance do not match, then you need to check the ledger balances again.

Trial Balance Template

We have created a Trial Balance Template in with predefined formulas. This template helps you easily prepare trial balance and find the difference. This template can be helpful to Accounts Assistants, Accountants, and Auditors, etc.

Excel Google Sheets Open Office Calc

Click here to Download All Financial Statement Excel Templates for ₹299.

Note: To edit and customize the Google Sheet, save the file on your Google Drive by using the “Make a Copy” option from the File menu.

In addition to this, you can also download other accounting templates like Profit and Loss Statement, Bank Reconciliation, and Cash Flow Statement.

Let’s discuss the contents of the template.

Contents Of Trial Balance Template

This template consists of two sections: Header Section and Data Input Section

Header Section

This section consists of the heading of the sheet with the date of preparation. Furthermore, on the right-hand side, you can see the difference between Debit and Credit amounts.

Furthermore, the formula applied here is =SUMPRODUCT(SUM(B5: B89)-SUM(C5: C89))

Data Input Section

This section consists of all the accounting heads from the Ledger.

Insert the closing balances of all the accounting heads into this section. I have included most of them, but if required you can change it as per your need.

The accounting head is as follow:

1. Petty Cash
2. Cash In Current A/C
3. Cash In Savings A/C
4. Accounts Receivable
5. Bad Debts Reserve
6. Stock Inventory
7. Insurance
8. Advance Paid Expenses
9. Office Supply
10. Deposits with Utility Companies
11. Notes Receivable
12. Investments
13. Organization Expense
14. Vehicles
15. Depreciation – Vehicles
16. Furniture and Fixtures
17. Depreciation – Furniture and Fixtures
18. Equipment
19. Depreciation – Equipment
20. Buildings
21. Depreciation – Buildings
22. Land
23. Goodwill
24. Other Intangible Assets
25. Accounts Payable
26. Sales Tax Payable
27. Other Taxes Payable
28. Accrued Wages
29. Unearned Revenue
30. Accrued Income Taxes
31. Credit Card
32. Bank Loan
33. Notes Payable
34. Equity Share of Owner
35. Owner’s Drawing Account
36. Common Stock
37. Additional Paid-In Capital
38. Preferred Stock
39. Retained Earnings
40. Sales
41. Revenues
42. Sales Returns and Allowances
43. Income on other Investments
44. Gain / Loss on Sale of Assets
45. Purchases
46. Freight
47. Purchase Returns and Allowances
48. Cost of Goods Sold – Materials
49. Labor  – Cost of goods sold
50. Direct Expenses – Cost of Goods Sold
51. Indirect Expenses – Cost of Goods Sold
52. Advertising
53. Amortization
54. Bad Debt Expense
55. Bank Charges
56. Charitable Contributions
57. Commissions Expense
58. Contract Labor
59. Credit Card Fees Expense
60. Delivery Expense
61. Depreciation
62. Dues and Subscriptions
63. Entertainment
64. Insurance
65. Interest Expense
66. Maintenance
67. Miscellaneous
68. Office Expense
69. Operating Supplies
70. Payroll Taxes
71. Permits and Licenses
72. Postage
73. Professional Fees
74. Property Taxes
75. Rent
76. Repairs
77. Telephone
78. Travel
79. Utilities
80. Vehicle Expenses
81. Wages
82. Income Taxes
83. Totals

The trial balance will ensure that the debits are equal to the credits.

Limitations and Undetectable Errors

This statement can easily identify any mistake in terms of mathematical or typographical errors entered in the ledger. There are some errors where it doesn’t help. They are:

• A transaction not entered in the books of account or ledger will not have entries on both sides.  Thus, the balance of debit and credit side will match.
• In case, you entered the wrong amount on both sides; debit and credit. Still, it will balance it.
• When you do an opposite entry; report the debit amount in credit and the credit amount in debit. This will also balance the statement and will not rectify this error.
• Doesn’t rectify the reporting of the amount under the wrong head.

How to Locate Errors in Trial Balance?

To locate errors in your trial balance you can follow the below-mentioned steps:

• Divide the difference between debit and credit column by 2 and see if a similar amount appears in the Trial Balance. If so, check whether it is reported in the correct column. such errors occur when the accountant records such a balance might on the wrong side that causes a difference of double amount.
• Match again the Cash balance and Bank balance properly.
• There can be transposition or transplacement errors. To find such errors divide the difference by 9. If the difference amount is evenly divisible by 9, it is due to transposition or trans-placement of figures. Transposition means recording digits in a different order. 123 is written instead of 132. The difference is 9 and is divisible by 9. Similarly, trans-placement means recording the wrong decimal places to the left or right. 1.23 reported instead of 12.3. The difference is 11.07 which his divisible by 9. Find such figures and rectify them.
• Re-confirm by checking that the opening balances are correctly brought forward.
• Individually recheck the trial balance amounts with ledger accounts.
• Check the individual totals of Sundry Debtors/Creditors.
• Recheck that the carry forward of all subsidiary books is properly done.
• In the worst-case scenario, if you cannot locate the errors then transfer the difference to Suspense Account. When the mistake is found, pass the entrance to a suspense account and close it.

Pro-Tip

Please note that just because our trial balance is balanced, it does not mean that the accounts are correct or that mistakes haven’t occurred.

Therefore, there can be entries that we have missed or items we might have entered in the wrong account.

For example, you might have increased the wrong asset account at the time of purchase. Sometimes, we decrease the wrong expense account while making a payment or the transactions are entered twice.

Thus, you need to keep in mind that the trial balance is not the final document of accounts.

Once the trial balance is prepared, the next step is to prepare other financial statements like Profit and Loss Account and Balance Sheet.

We thank our readers for liking, sharing, and following us on different social media platforms, especially Facebook.

If you have any queries or questions, share them in the comments below and I will be more than happy to help you.

Frequently Asked Questions