Printer Friendly

Pivot tables: a means to quick, accurate trial balances.

Technology is valuable when it allows necessary tasks to be done more quickly and accurately. Accountants have benefited from spreadsheet software that has given them the ability to sum thousands of numbers in seconds, link cells, create macros, and perform other tasks. Schools of business have added courses to prepare graduates for this new workplace. They have also introduced computer assignments into almost every course, providing the opportunity for students to hone their skills. Unfortunately, in many cases, these assignments are nothing more than preparing journal entries in a spreadsheet. Spreadsheet functions are so varied that even experienced users seldom learn the shortcuts for simplifying a task.



One example of this is the pivot table function. This tool for managing large data files can, on a simpler scale and with a few adjustments to standard practice, be used to develop trial balances from journal entries almost instantly. The pivot table eliminates the need for T-accounts; it can be used for unadjusted trial balances, pre-closing trial balances, and postclosing trial balances. It also provides a means of making corrections to journal entries and, with one click, having the trial balances adjusted to include the new information.

In classroom settings, pivot tables can be introduced within a week of teaching journal entries. By eliminating the tedium of obtaining the various trial balances, more time is available for analysis and discussion of the results.

The data require labeling two columns in a spreadsheet "Accounts" and "Amounts"; the account names are placed in one column and the amounts in a contingent column (see Exhibit 1). Use positive numbers for all debits and negative numbers for credits. There can be no open rows between the journal entries, but alternate journal entries may be highlighted to make distinguishing them easy. Other columns can be used for items such as dates and notes.

When all journal entries have been prepared, highlight the two columns, account names and amounts, including the column titles. Click on "data" and select "pivot tables" from the drop-down menu. The Pivot Table Wizard Step 1 box appears, indicating that you have chosen to use this spreadsheet to create a pivot table (see Exhibit 2). Click "next." Because you have highlighted or selected the cells you wish to include in the pivot table, the Step 2 box will note the cell addresses that have just been selected. Again, click "next." Step 3 of the Pivot Table Wizard allows you to choose where to place the table. It can be on a new worksheet or on the same worksheet as the journal entries. If you select "existing worksheet," type in the top left cell address where you'd like the table to begin, and click "finish."

Two boxes appear. The smaller one on the left will have the names of the column headings, and the larger one is a table with spaces for row, column, and data items (see Exhibit 3). Drag and drop "Account" to the row part of the table. Drag and drop "Amount" to the "data" part of the table. The result is a trial balance with the accounts in alphabetical order. The balance is reported at the bottom (see Exhibit 4). To add dollar signs, highlight the numbers and click on the dollar sign. Adjust the column size as needed.

By right-clicking on the pivot table, another menu appears that allows features such as hiding, reordering, and refreshing. The refresh option allows for changes to the accounts and amounts in the journal entries to be immediately incorporated into the pivot table/trial balance. Additions of journal entries, however, will require the creation of a new pivot table.



By linking the pivot table results to the financial statements, a pivot table allows an easy demonstration of the effect of a single transaction on the results.

See the Sidebar on page 70 for these instructions on using pivot tables.

The Classroom and Beyond

Technology can be a valuable ally by eliminating the tedium of T-accounts and subsequent trial balances. Students often dread the time-consuming nature of journal entry assignments. Pivot tables require them to input the journal entries, but the remaining steps can be done quickly and accurately. In the classroom, this leaves more time for explanations of the effects of the transactions and for analyses of how a transaction affects the financial statements. Beyond the classroom, professionals working with large databases are likely to find pivot tables useful for partitioning and analyzing multiple columns of data.

Lucille G. Montondon, PhD, is a professor in the college of business at Texas State University San Marcos, San Marcos, Texas, and Treba L. Marsh, PhD, CPA, is an associate professor in the department of Accounting at Stephen F. Austin State University, Nacogdoches, Texas.


1. To begin, label column B "Account" and column C "Amount." Other columns, such as A and D, can be used for items such as transaction dates and explanations. Be sure that "Account" and "Amount" are in adjacent columns.

Journalize transactions by typing all accounts in the B column and all dollar amounts in the C column, indicating debits as positive numbers and credits as negative numbers. There can be no skipped rows. To make it easier to read, highlight every other journal entry.

2. When all transactions/adjustments are journalized:

* Highlight the "Amount" and "Account" columns, including the headings.

* Click on "data" on the toolbar. Select "pivot tables."

* To the question "Where is the data you want to analyze?," select "Microsoft Excel."

* To the question "What sort of report do you want?," select "Pivot table." Click on the "next" button.

* To the question "What database do you want to use?," click on the "next" button because you have highlighted the two columns of accounts and amounts.

* To the question "Where do you want the table?," select "existing worksheet" and then type in the name of the upper left cell (e.g., C20), indicating where you want the table to begin. Click "finish."

A pivot table field list will appear. It has the column names "Account" and "Amount" and some spaces as well, such as "drop row field here."

Drag and drop "Account" to the "drop row fields here." Then drag and drop "Amount" to the "drop data fields here." You have a pivot table/trial balance.

Right-click on the pivot table for additional menu selections. You can change the order of the accounts (e.g., move cash to the beginning of the list) and hide accounts (e.g., those with zero balances).

3. To prepare a closing entry, copy/paste temporary account names in column B and type in "=-" and then click on the amount for each account in the pivot table. Complete the closing entries by entering the name of the appropriate account. The numerical amount can be calculated by typing "=-sum" and highlighting the other accounts in the closing entry.

4. To create a postclosing trial balance, repeat step 3 above, highlighting all journal entries, including the closing entry.
COPYRIGHT 2006 New York State Society of Certified Public Accountants
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2006 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Author:Montondon, Lucille G.; Marsh, Treba L.
Publication:The CPA Journal
Geographic Code:1USA
Date:Apr 1, 2006
Previous Article:How to make an ethics program work.
Next Article:Website of the month: FindLaw's Tax Law guide.

Terms of use | Privacy policy | Copyright © 2020 Farlex, Inc. | Feedback | For webmasters