Working with pivot tables
Working with pivot tables
|
Tip Readers often ask me how to build macros or complex formulas to rearrange and summarise worksheet data. In many cases, the best solution is to rethink the worksheet architecture instead. Jim Ellington uses Excel to track his team's contract sales. Jim sets up his workbook with a separate sheet for each salesperson. The illustration below shows a simplified version of one sheet from his workbook.  Jim's summary sheet contains formulas that gather the totals from each salesperson's page. The arrangement works, but Jim has problems creating a list of contracts summarised by client, and to add a salesperson he must rebuild formulas. A better approach would be to use a database-and-pivot table design. In this arrangement, the data for all salespeople are stored on a single sheet. A pivot table on a separate sheet displays the current contract data for each salesperson, individually or together, and a subtotal outline generates summaries. Storing all the data on one sheet allows you to rearrange and summarise in many ways, without having to move data or create complex formulas. In Step 2, creating the dynamic range name is a standard trick to ensure that the pivot table remains synchronised with any new data you enter into the database. To create the pivot table in Step 3, select any cell in the database, and select Data--PivotTable. In the second step of the Pivot Table Wizard, select Database as the pivot table range. In Step 3 of the wizard, drag the Sales Person field into the Page area, drag Client into the Row area, and drag the remaining fields into the Data area. Next, double-click Count of Start, enter Start Date in the Name box, select Max in the Summarize By list, click Number, choose a Date format, and click OK until you're in the wizard. Repeat for End and for Amount. For Amount, enter Contract Amount in the Name box, choose Currency as the number format, and click Finish. When the pivot table appears, drag the Data button to the right to align the variables, as in Step 4. To select a salesperson, click the drop-down list by cell B1. If you add contracts or a new salesperson to the database sheet, select Data--Refresh Data to include them in the pivot table. Finally, deselect Grand Totals for Columns in the resulting dialogue box, and click Finish. To create the subtotals report in Step 5, return to the database worksheet, select a cell in the database, and select Data--Subtotals. In the At Each Change box, select Client; click OK to create the subtotals, then click the second-level button on the outline to show the Client subtotals. - Richard Scoville
| Category: Spreadsheet Issue: Jun 1997 Pages: 176-177 |
These Web pages are produced by Australian PC World © 1997 IDG Communications
