CPA Resource Zone

Excel Tutorials for CPA's

Pivot Table and Trial Balances Function and Example

Third, we need to ensure that debits and credits are all in one column. To do that, let us enter a header in cell E1 indicating that it's a column for debits and credits. The header may be "Debit / Credit." Then in cell E2 we enter formula: = C2+D2 and copy the formula to the last record in row 109:

Debits and credits in one column

Next, we need to indicate a trial balance period for each record in our database. Let us enter another header "TB Period" in cell F1. Such indication is required for a pivot table to be able to understand which record relates to which trial balance:

Trial balance period indicator

Once we have added the header, we enter "12/31/05" for all records from 12/31/05 trial balance and "12/31/06" for all records from 12/31/06 trial balance. The blank row we initially left between the two trial balances will now help us figure out where 12/31/05 trial balance ends and where 12/31/06 trial balance begins:

Trial balance dates entered

Finally, we do not need the blank row any more and can delete it. Select row 56, right click your mouse and use "Delete" from the popup menu:

Delete unnecessary row

Page 2 of 7