Pivot Table and Trial Balances Function and Example
This Excel lecture suggests a fast way to create comparative trial balances using Excel pivot table function. The below technique provides essentially same results as Vlookup and Trial Balances Function and Example, which uses Excel formulas instead of pivot tables. If you are more comfortable with Excel formulas (sum, if, iserror and vlookup), then you can find lecture on using Vlookup more practicable.
Some disadvantages of using pivot tables, in my opinion, are presented below:
- they are not as flexible as formulas, and
- they require more space and thus, files created using pivot tables are usually larger.
I am sure some people have different opinions about advantages and disadvantages of pivot tables. Feel free to comment on that.
Screen prints and sample Excel files are provided so that you can practice while reading the tip.
As with Vlookup and Trial Balances Function and Example, after some practice, it may take 5-7 minutes, if not faster, to create a comparative trial balance using pivot tables.
The company name and numbers in this tip are not real and no association with a real company with a similar name, if such exists, should be made.
The steps are outlined below.
1) Obtain trial balances for required periods
This is same as in Vlookup and Trial Balances Function and Example.
Sample trial balances for two periods come from Excel Lecture #1 - 12/31/05 and 12/31/06.
2) Check to ensure debits and credits balance
Thi is the same as in Comparative trial balances with Vlookup.
3) Create a database of trial balances
Before a pivot table can be created, we need to set a database with all required information in one place. In our case, the required information is 12/31/05 and 12/31/06 trial balances. One place means one file and one tab. Note that pivot tables can work with multiple data ranges; however, for simplicity of this technique, we will create and use one data range.
First, let us create a new file called ABC Company Trial Balances Combined with Pivots.xls and copy and paste 12/31/05 trial balance into the newly created file:
Second, copy and paste the data from 12/31/06 trial balance in the newly created file underneath the data copied from 12/31/05 trial balance. We will leave a blank row between the two data sets so that we can differentiate the two trial balances:
Page 1 of 7
Location