A while ago I prepared a post here to show that it is possible to create an income statement and balance sheet, for example, using a Pivot Table. I didn’t expand on the technique and I didn’t include my spreadsheet. In this post I do provide an example and a spreadsheet to show you how to create a Trial Balance from a simple list and again using a Pivot Table. There is a link to my Excel file at the end of the part that you can download.
This post demonstrates the creation of a Trial Balance from a list of payments and receipts by using a Pivot Table. You will see that setting up the PT take a BIT of time but once you have done it, it’s done! After that, you would only need to change anything if you add a new account or account code.
Please note: as with many of my examples, I create simple data sets or situations. I do this so that you appreciate the process without getting lost in the data. However, if you expand this example, I recommend you move the Trial Balance to a separate sheet otherwise your PT could expand and interfere with it.
Setting up the Data Area
I did not use an Excel Table for this part of the exercise for no real reason except that I want you to concentrate on the PT and Trial Balance (TB). If your own example is complex, or lengthy then using an Excel Table could be a good idea.
Create a List
The first step in this process is to create a list of payments and receipts. Don’t worry about the list being sorted in date or time order, the Pivot Table will sort that out for you.
To help with the Pivot Table, I have set up a VLOOKUP() function in columns E and G to convert account names into account numbers. If you don’t do this, your PT will sort alphabetically rather than numerically and it may be more difficult to manage. In any case, accountants use charts of accounts that are often based on numbers so this is consistent with that.
I have created the range name accountlookup for use with the VLOOKUP() function.
Once your list is complete, create a Pivot Table … see the pivot_and_TB tab …
Setting up the Pivot Table
The way this works is that we have to create a Pivot Table (PT) as a matrix, as you can see to the left:
- Dr Account Number and
- Dr Account Name
- are put in the Row Labels area
- Cr Account Number and
- Cr Account Name
- are put in the Column Labels area
Make sure the Values are shown as Sum of … and not Count of … or Average of …
Include Grand Totals for rows and columns
PLEASE NOTE: let me repeat my advice, if you expand this example, I recommend you move the Trial Balance to a separate sheet otherwise your PT could expand and interfere with it.
Trial Balance Creation
Hard code the Account numbers which are entered in the range J22:J34 in this example
Use VLOOKUP() to find the account name from the account number: it uses the range name accountlookup2 although it could use an INDEX … MATCH combination with the range name accountlookup.
For the Debit balances I have used VLOOKUP() again but notice the range it uses: B6:H14 which EXCLUDES the first column and the last row. That is, it is working from the account names and it included the grand totals in column H.
For the Credit balances I have used HLOOKUP() but notice the range it uses: C5:G15 which EXCLUDES the first two rows and the last column. That is, it is working from the account names and it included the grand totals in row 15.
All trial balances must balance so create a SUM() formula to check that the total Debits = the total Credits … Cells L35 and M35 and put a check in cells L36 and M36 to make sure they agree with the value in the final overall grand total in the PT, in cell H15 in this example.
My check is just about the simplest there can be, which is, for the Debit column, =L35=H15 … if they agree, Excel shows the word TRUE otherwise we would see the word FALSE there. You can change this to hide the word TRUE and show only the word FALSE, of course, by changing that formula. For example, =IF(L35<>H15,”ERROR”,””) … I have put this in cell L37 for demonstration and you can do the same for the Credit column in cell M37.
Download the file from here trial_balance_from_PT