Add Perspective to Spreadsheets


For years most accountants functioned as information gatherers and financial historians. But today, with computers automating many of those tedious, manual jobs, CPAs are swiftly evolving into information analysts. The timing of this transformation is not any too soon: Because of the now ubiquitous personal computer, business managers are drowning in mountains of data and seeking ways to transform those raw numbers into business strategy.

Although computers were quick to create the data avalanche, they lagged in ways to eliminate, or at least shrink, it even though they were touted as number-crunchers. Spreadsheet software applications available before the early 1990s, for example, lacked the industrial strength to perform the kind of analysis that database software could do with the click of a button. To effectively analyze huge blocks of financial information generally required teaming the spreadsheet with a database application, a marriage of necessity that at the time was both clumsy and mismatched.

A Panoramic View

This Technology Workshop focuses on how to use spreadsheet software to analyze data from multiple perspectives.

While all spreadsheet applications are superb at calculating huge amounts of data, it was only relatively recently, with the introduction of the Excel PivotTable function, that a spreadsheet program could analyze that data in greater depth.

Since the PivotTable function is such a useful tool not well known in accounting circles, the Journal invited John F. Lacher, CPA, an Excel specialist, to demonstrate how the PivotTable works and show how it can enhance financial work.

The advent of the software suite in which several key applications from the same vendor work relatively seamlessly together, made the marriage in some cases somewhat less disharmonious but still not very user-friendly to anyone with less than an expert knowledge of database software. Often the user had to turn to a database programmer to forge the complex links between the two applications. It wasn't until Excel version 5 came along in 1994 that a spreadsheet application could tackle such a project single-handedly.

The solution was provided by a new function called a PivotTable, which does not only perform powerful data analysis but is easy to use. Without any special programming training, a CPA can execute some fancy financial spreadsheet analysis without turning to a database programmer for help or, in many cases, without even linking the spreadsheet to a database (see the sidebar The Competition Steams Up, below).

This article focuses on Excel rather than its competitors because it contains a more powerful data analyzer than either Lotus 1-2-3 or Quattro Pro, and because Excel is a more widely used spreadsheet application. The article walks you through several examples that illustrate how the PivotTable function can enhance an accountant's work.

The Competition Steams Up

Competition among the three spreadsheet contenders, Excel, Lotus 1-2-3 and Quattro Pro, is heating up. That's terrific news for users because each vendor is working hard to provide smarter, faster, friendlier applications.

Lotus 1-2-3, for example, can create a Dynamic Crosstab using the Lotus SmartSuites database Approach. This feature allows a user to specify a spreadsheet range and then create an Approach database crosstab. While the crosstab actually is in Approach, not 1-2-3, it can be displayed on the worksheet; this is similar to Excels ability to convert data from Excel to its database suitemate Access.

The latest Quattro Pro spreadsheet provides a wizard that assists the user in creating crosstabs on the worksheet. This is the first version of the spreadsheet to have this feature. It is similar in concept to the Excel PivotTable but lags in features.

When Microsoft introduces its Office 2000 suite, an upgrade to Office 97, Excel 2000 will provide new features to make the PivotTable function even easier to create and use. The new version will allow fields to be added to and removed from the worksheet directly without the PivotTable wizard dialog. Clear drop zones will provide cues to help position the fields, and a tree view (like an outline) will let users expand and collapse specific data items. Another significant improvement will be the addition of banded reports, which will allow users to focus on and see details of PivotTable data while still providing a summary at the bottom of the table. The reports also will provide PivotChart dynamic views to refresh charts automatically as fields in the PivotTable are moved and managed.

And for organizations that employ data warehouses, Excel 2000 also will provide PivotTable features for reporting from data warehouses built on the soon-to-be-released SQL Server 7.0. The actual processing of the data will occur in the warehouse computer, and only the results will be relayed to the desktop, a step that will dramatically speed a data manipulation exercise.

As shown in the examples, a PivotTable automatically reformats tables of data in a spreadsheet. What makes it so handy is that you don't have to enter new formulas. Thus, you can analyze and compare different sets of totals by rotating and rearranging the rows and columns of the PivotTable with a few mouse movements. PivotTable also can cross-tabulate data from another Excel worksheet. And it can even import and analyze large amounts of data stored in an external database. If you've ever had to do some of these chores manually, you know how tedious and time-consuming they can be.

Here are some examples of where the PivotTable function can solve typical financial problems.

Example 1: As a Budgeting Tool
During a typical budgeting process, the job of consolidating and summarizing forecasted revenue and expense is labor-intensive. With standard spreadsheet techniques, hundreds of cells of data must be entered and organized and formulas added to total the data. Last-minute additions and revisions of data, which are typical, often create havoc with the inflexible formulas in the cells that are designed to calculate totals. Reorganizing data and totals after entering the formulas becomes a complex and confusing task and it invites errors. On top of that, the final spreadsheet usually is so large it doesn't lend itself to answering the typical what-if questions that often arise.

Exhibit 1 is an example of a budget worksheet for XYZ Co. As you can see, subtotal formulas are unnecessary. Because the budget totals are generated automatically, the detail data can be stored separately on another worksheet. This arrangement makes it easy to add new data categories to the detail sheet, and thus the revised totals adjust automatically.

Exhibit 2 shows how a PivotTable reshapes the same data automatically, providing a different perspective: a department/product view. The new perspective is created with a few mouse clicks and by dragging and dropping row and column headings on the worksheet, a process illustrated later in the article.

Example 2: As a Sales Analyzer
Sales analysis involves capturing and analyzing data to discover trends and key relationships. Decision makers need to see many cuts of information, for example, by customer type, product and location. But when there are many variables, it's difficult, if not impossible, to see the proverbial forest for the trees. A PivotTable in effect filters the forest view so users can arrange the trees to better see the once-hidden relationships and trends.

The spreadsheets in exhibits 3 to 6 are all based on the same underlying data. But exhibit 3 shows the analysis by quarter, exhibit 4 by product, exhibit 5 by customer type and exhibit 6 by region.

Example 3: As a Database Link
Most spreadsheet users have lived through the frustrating task of manually importing data to a spreadsheet from another financial application or database. In most cases, the user must first import data into columns in a worksheet and then check to be sure the right numbers line up in the correct columns. The data must then be sorted and subtotal formulas added. In subsequent financial periods, when new data are brought in from the database, this cumbersome process has to be repeated.

A PivotTable greatly streamlines the process by calculating totals directly from data stored in a database. Because the PivotTable can even be linked to the records and fields in the database, any change in the database automatically recalculates the spreadsheet totals. If the sales analysis PivotTable in exhibits 3 through 6 were linked directly to a sales transaction database, new quarterly data could be recalculated by simply clicking on the Refresh PivotTable button.

Now that you've seen what the PivotTable can do, lets go through a step-by-step demonstration of how to implement the function.

1. Create a spreadsheet file similar to that in exhibit 1 . Highlight it. (For those who don't know how to highlight, position the cursor over one corner of the area to be calculated, hold down the left mouse button and drag the cursor over the area until its all dark.) The highlighting step is not actually necessary in this example because the only information on the page is the PivotTable target. If other data were on the page, the highlighting would be necessary so the PivotTable function would know which data it's being ordered to work on.

2. Click on Data, PivotTable Report. When the screen asks for the location of the data (exhibit 7), click on the radio button for Microsoft Excel list or database and then click on Next and a new screen appears (exhibit 8). If you had not highlighted the range in step 1, you would have had to type in that information. Then click on Next and a new screen will appear (exhibit 9).

3. Since we want to examine the sales and profit data from the division and region perspective, use the drag-and-drop feature of the mouse to drag both Sales and Profit into the box as shown in exhibit 9 . Likewise, drag Division to the ROW space and Region to the COLUMN space.

4. Click on Next and a new screen will appear (exhibit 10) that gives you the choice of where to put the PivotTable, in a new worksheet or in the existing one. (I opted for the new worksheet.) Then click on Finish.

5. The PivotTable now creates a new worksheet in your working file and switches to it (exhibit 11). Notice that now the original data are observed from the perspective of division and region. If you want the data laid out differently, use the drag-and-drop feature. For example, you can grab the word Region and drop it to the right of the Division column and the spreadsheet view (exhibit 12) will adjust accordingly. You can rearrange the three key variables (Region, Division and Data) in any way to achieve the best perspective.

Here is a three-step program to help you get up to speed with PivotTables:

1. Review Excels own help function by clicking on Help in your taskbar type in PivotTable. If you're in In Excel 95/5.0, the display topic will be Retrieving and Analyzing Data; choose the subtopic Creating a PivotTable or Analyzing Data with PivotTables. In Excel 97, click on Analyzing Data with PivotTables. You will be surprised by the amount of valuable information and examples built into the Excel help system.

2. Study examples of PivotTables developed by others. Check out my Web page for this at . The examples work with any of the versions of Excel.

3. Create a PivotTable with your data and spend some time playing with the tables. After you have taken this step, the concepts will become clear and you can concentrate on how best to use the function in your work. Remember, too, that Excel provides instructional wizard screens to help you create and modify PivotTables.

An Invitation
If you have a special how-to technology topic you would like the Journal to consider for inclusion in this series, or an application shortcut you devised and would like to share with other professionals, contact Senior Editor Stanley Zarowin at .

The advantages of the PivotTable are obvious, yet many spreadsheet users, including CPAs, shy away from new functions that appear difficult or whose usefulness is not immediately apparent. This is a function worth investing the time to learn. It will save you countless hours of building formulas in worksheets and give you the opportunity to tackle data analysis in a way you never thought possible. As a result, the PivotTable function will add value to data analysis and presentation of data.

JOHN F. LACHER, CPA, provides Excel and Access consulting services over the Internet via e-mail and remote conferencing. His e-mail address is and his Web page address is


Keeping client information safe in an age of scams and security threats

A look at the Dirty Dozen tax scams and ways to protect taxpayer information.


How to create maps in Excel 2016

Microsoft Excel 2016 has two new mapping capabilities. J. Carlton Collins, CPA, demonstrates how to make masterful 2D and 3D maps in Excel 2016.


News quiz: IRS enforcement, a hot job, and audit value

The IRS’s 2016 Data Book, a “hot job” of particular interest at this time of year, and insight into how executive and audit committees view the insights from financial statement audits received attention recently. See how much you know with this short quiz.