|
[ Go to March 1997 Table of Contents ]
Swinging Spreadsheets Pivot tables are the perfect way to organize, analyze and track data. I used to think spreadsheets were the last resort for organizing and analyzing data-until I discovered Excel's pivot tables. Pivot tables are an absolute must if you need to sift through your data and examine it in different ways. With pivot tables, most data organization and summarization happens automatically. Best of all, you can quickly change a pivot table to analyze data from a different perspective. This month, we'll look at how to build basic pivot tables; next month, I'll describe some additional pivot table tips and tricks. Essentially, an Excel pivot table displays the results of a database query. Each row in the spreadsheet becomes a database record, and each column represents a field in that database. Pivot tables can also add depth to spreadsheet data, adding another dimension to the row-and-column organization with pages that provide different summary looks at the data. For instance, assume you're a billing manager (heck, let's make you vice president of billing) for an engineering firm and you use a large spreadsheet to track monthly employee time sheets. The spreadsheet includes a set of cells for every day of the month. Each set of cells lists the employee's name, the date(s) worked, project(s) the employee focused on, a code number for the project and the number of hours worked for that project. Mission: possible Your mission, should you choose to accept it, is organizing the data so it offers more than just a static list of names and numbers with limited manipulation capabilities. For example, you might need to sort the information by employee name to produce a monthly staff productivity report, or by project code for billing purposes. That's where pivot tables come in. In our example pivot table, John Doe's hours and the projects that he worked on are all summarized together. His entries would be followed by the next employee's, and so on. This view would represent a page in the pivot table, and the employee name would be the key field for that page. For billing purposes, you'd change the pivot table to make the project name the page field. Adventurous users can create the pivot table from an external data source, such as Paradox, dBASE, Access or Microsoft SQL Server database files. In this example, however, I'll show you how to create a pivot table from existing spreadsheet cells. We'll use the example described above, with a spreadsheet containing project hours for various employees of an engineering company. Initially, we'll organize the data to provide a monthly productivity report for the employees. Like many other complex tasks, Excel automates the creation of pivot tables through a wizard. To create a pivot table, first create the data that will be summarized in the table. In this case, I have a spreadsheet that contains five columns: Date, Employee, Project Name, Project Code and Hours. If you'd like to use this spreadsheet for this exercise instead of building your own, download PIVOT.XLS from www.winmag.com or the WINDOWS Magazine areas on CompuServe or America Online. If you select the first cell of the range before starting the wizard, Excel will automatically search down and to the right to find the range's extent and use that range in the pivot table. To simplify the procedure, click on the upper-leftmost cell of the range. Then, choose Data/Pivot Table. Excel displays a dialog for step 1 of the Pivot Table Wizard. In step 1, you specify the location of the source data. In this example, we'll choose the option Microsoft Excel List or Database. Make sure that option is selected, then click on Next. In step 2, you define the cells to be included in the pivot table's range. If you selected the upper-leftmost cell in the range, the Range text box will be filled in for you. If not, just click and drag in the spreadsheet to highlight the range (all cells) to be included. When you've identified the range, click on Next. Drag and drop Now for step 3. Drag and drop elements from the range into the Row, Column, Data and Page fields on the dialog box. In this example, use the Employee column as the page field, so you can view the data sorted by employee. Drag the Employee button from the right edge of the dialog box and drop it on the Page field. Next, specify which items will be used to create the rows and columns in the pivot table. You'll have complete flexibility later to modify the table as needed, so for now, drag the Project Name button into the Column field and the Date button into the Row field. Finally, drag the Hours button into the Data field. This will cause each project's hours to be summed. The result will be a pivot table with rows for each date worked and a column for each project worked on during that date, along with a cell displaying the total hours worked. When you're satisfied with step 3, click on Next. In step 4, you specify the starting cell for the pivot table and its name. With the Pivot Table Starting Cell text box selected, click on the cell in the spreadsheet where you want the table to be inserted. Highlight the default name in the Pivot Table Name text box and type a name for the table, such as Employee Productivity Report. For now, click on Finish. I'll explain more options for this step in next month's column. Perfect appearance When the pivot table appears in the spreadsheet, you'll see that it has summarized all the employee project hours in the table by project. A Query and Pivot toolbar will also appear. To view the hours and projects a specific employee worked on, select the employee's name from the Employee drop-down list (the page field) in the table. The table will change to show only the hours for that employee, with a column for each project and a row for each date, and totals. Now, assume you want to change the table to focus on individual projects, rather than people. For example, you want to see the total employee hours spent on the fictional ASD INC. project. You also want to know who worked on that project. To do this, right-click on the table and choose Pivot Table; the Pivot Table Wizard's Step 3 dialog box will open. Drag the Employee button from the Page field to the Column field. Drag the Project Name button from the Column field to the Page field. Then, click on Finish in the third and fourth wizard dialog boxes to view the modified pivot table. To view the ASD INC. project, simply select it from the Project Name drop-down list. Modification tips You can also modify a pivot table from within the spreadsheet. For example, assume you want to view the data based on both project and personnel, organized by date. In effect, this means you'll be using both the Project Name and Employee columns as Page fields. Instead of using the wizard dialog box, drag the Employee button from the table and drop it on the Project Name button in the Page field area of the table. You'll now have two drop-down lists and can tailor the table view accordingly. For example, you can select the ASD INC. company and the name John Doe to see the dates he worked on the specified project. If you change information in the original spreadsheet, click on the Refresh Data button on the Query and Pivot toolbar to update the Pivot Table. Now, what if you want to organize the data in some other way? Just drag the buttons where you want them. For example, drag the Employee button from the page field area of the pivot table and drop it on a column in the table. The pivot table will rearrange to show the data organized by employee again. Clear the table Lastly, you'll probably want to know how to remove the pivot table. Highlight the entire pivot table and choose Edit/ Clear/All. This will remove not only the data, but also the cell formatting and gridlines. It's easy to see how pivot tables can save you hours of spreadsheet work. And this example only scratches the surface. Next month, we'll look at the range of options you have for creating and modifying pivot tables. Contributing Editor Jim Boyce is the author of Upgrading PCs Illustrated (Que, 1997). Contact Jim in the "Applications" topic of WINDOWS Magazine's areas on America Online and CompuServe, or care of the editor at the e-mail addresses here. Copyright (c) 1997 CMP Media Inc.
|
