Make Excel an instant know-it-all: PivotTables reorganize data to produce many custom answers.

You're sitting at your computer working on a spreadsheet that displays revenue generated by individual salespersons. Your CEO, hovering anxiously at your elbow, asks you for one employee's third-quarter sales total. You sort the Salesperson column and then the Order Date column. Then you write a formula to identify third-quarter sales. Finally, you sum the order amounts and give your boss the number he wants. Just as you congratulate yourself for coming up with the answer in less than three minutes, he says, "OK, now compare that with the results of the whole sales team"

You roll your eyes in frustration, scrap all the work you just did and re-sort the columns and rows and write a new set of formulas.

There has to be a better way, you think.

And there is. Instead of repeatedly sorting columns and rows and customizing formulas to answer each question your boss asks, you take a new tack: Immediately after you initially put the worksheet data together you can spend a minute or two using Excel's PivotTables, which will let you easily reconfigure the data with a mouse so you can produce near-instant answers to most any question about them.

I can almost hear some readers moaning, "PivotTables! Oh, no! Not PivotTables! They drive me crazy!"

Wait a minute. Stay cool. While it's true PivotTables have a reputation for being a bit tricky to set up, once you get the hang of them, you'll find they're such a powerful tool the learning curve is well worth the effort.

The best way to start developing PivotTable skills is to watch someone create one. And that's what this article is all about. Follow along as I cobble together a simple PivotTable; then, once you have the basics, I recommend you experiment with them so you can discover their true power.

Begin by setting up a worksheet with the data shown in exhibit 1, below.

[ILLUSTRATION OMITTED]

Organize your data in columns, with at least one column containing numeric information and one with text or dates. Also make certain there are no empty rows or columns within the data set. (Notice, by the way, that Smith made sales in both the United States and in the United Kingdom; you'll see how PivotTable handles that.)

Now place your cursor in any cell and click on Data and PivotTable and Pivot Chart Report. That brings up the PivotTable and PivotChart Wizard (exhibit 2, below).

[ILLUSTRATION OMITTED]

Under the heading Where is the data that you want to analyze? click on Microsoft Excel list of database and then on Next. (Note that the wizard also can import an external data source and even multiple consolidation ranges.)

Since you placed your cursor inside the data set before you evoked the wizard, clicking on Next automatically selects all the contiguous cells in the Range box (exhibit 3, below).

[ILLUSTRATION OMITTED]

If you had not followed our advice not to leave any empty rows of columns, the range selection would not have occurred automatically; you would have had to manually enter the range.

If the data range shown in the Range box is correct, click on Next. If not, click on Browse and highlight the target range with your mouse. Then click on Next. That will bring up the wizard's step 3 screen (exhibit 4, below).

[ILLUSTRATION OMITTED]

Now click on the Layout button and a new wizard screen appears (exhibit 5, below).

[ILLUSTRATION OMITTED]

You've arrived at the place in the setup sequence that often frustrates users. Don't be discouraged. If at first you don't get the results you want, start over. In fact, as you'll see, working with different layouts will help you understand how Excel is manipulating your data, and in a short time, you'll become an adept PivotTable user.

Notice that the Layout screen contains four sections: PAGE, COLUMN, ROW and DATA. On the right are the four column and row headings (buttons) from your worksheet: Country, Salesperson, Order Date and Order Amount. Each can be dragged while holding down the left mouse button and dropped into the adjacent layout scheme as a way to custom-calculate the data.

We will use the PAGE area for summarizing the highest level of data. For example, since we are analyzing sales in several countries, we will put Country in the PAGE area. By doing that you will be able to quickly pinpoint total sales from each country even though the original worksheet was not organized to produce that information without creating new formulas. To move Country, hold down the left mouse button, grab it and drop it into PAGE.

Important: You must have at least one set of text data (such as Salesperson) or date data (such as Order Date) in either the ROW or COLUMN box. You can have as many data fields in the ROW and COLUMN boxes as you like. Also, you must have at least one field in the DATA area. So, place Order Date in ROW and Salesperson in COLUMN. By doing this, you will be able to summarize data by date and person. Now drag Order Amount into DATA.

Once you made all those moves, your layout screen should resemble exhibit 6, below.

[ILLUSTRATION OMITTED]

If it's correct, click on OK, which takes you back to the wizard in exhibit 4. Now click on Finish and the Pivot-Table with all your data will appear (exhibit 7, below).

[ILLUSTRATION OMITTED]

To the right of the worksheet you'll see a PivotTable Field List screen, which gives you the opportunity to make adjustments in the placements of the data buttons.

Notice the three black arrows in the worksheet at A4, B1 and B3. If you click on any of them you'll see you can custom-filter the information under them. For example, if you click on the arrow next to Salesperson, a drop-down menu appears, allowing you to check the sales of any combination of salespersons.

If you're not satisfied with the layout of your data or the information it provides, try regrouping the data. For instance, you might want to group the Order Date by quarters--not by months and days. To do this right click on Order Date, the click on Group and Show Detail and then Group (exhibit 8, below).

[ILLUSTRATION OMITTED]

When the Grouping window appears (exhibit 9, at left) click on Quarters and deselect anything else that's highlighted.

[ILLUSTRATION OMITTED]

Now click on OK. This automatically changes your PivotTable to reflect quarter totals rather than daily ones (exhibit 10, below).

[ILLUSTRATION OMITTED]

These are the basics of PivotTables. With practice and patience you'll quickly master the function. And the data analysis rewards--in both speed and convenience--will be significant.

AICPA RESOURCE

TECH Conference May 2-5, 2004 Venetian Las Vegas 

Key to Instructions

To help readers follow the instructions in this article, we use two different typefaces.

Boldface type is used to identify the names of icons, agendas and URLs.

Sans serif type indicates commands and instructions that users should type into the computer and the names of files.

ROBERTA ANN JONES, PhD, CPA, is an assistant professor of accounting at Pittsburg State University in Kansas. Her e-mail address is rjones1@ pittstate.edu.