|
[ Go to April 1997 Table of Contents ]
Excel Pivot Tables II: The Search for More Tips A thrilling sequel to last month's column.
The Empire Strikes Back. Indiana Jones and the Temple of Doom. Aliens. I just love a good sequel, so I decided to write one myself. Mine is this column, the second in a thrilling two-part series about Microsoft Excel pivot tables. Just to recap, pivot tables let you quickly organize and view large amounts of data; they can help transform endless rows and columns of numbers into a meaningful presentation of the data. In fact, pivot tables let you sort and organize spreadsheet data much like a database. Last month, we covered pivot table basics. This month, we'll move on to some advanced pivot table features. The fab four There are four ways to create a pivot table. In addition to using existing cells in your current spreadsheet as the source data (which I discussed last month), you can use an external data source, multiple consolidation ranges and other pivot tables. You choose from these options in the Step 1 dialog box of the Pivot Table Wizard. The Multiple Consolidation Ranges option lets you create the pivot table using multiple ranges of data on the same sheet or in different sheets. Each of the ranges must be identical in structure, with the same row and column names. For example, you might have a different spreadsheet for each month of sales and use a pivot table to organize all the data for the entire year. The option Another Pivot Table lets you create a pivot table using one that already exists in the same workbook. Both pivot tables are linked to the same source data, and updating one pivot table causes the other to be updated as well. Using the same source data for multiple pivot tables reduces the amount of data in memory. Use this option when you want multiple views of the same source data displayed simultaneously. Finally, the External Data Source option fetches data from a text file, such as one created by exporting database or spreadsheet data to the text file. Or you might have an in-house application that collates data from a mainframe and creates the text file from that data. You can also create the pivot table from data stored in a database. Before you can create the pivot table from the external data source, however, you must install Microsoft Query and the appropriate Open Database Connectivity (ODBC) drivers to read the external data. To import data from Microsoft SQL Server, for example, you must install the SQL Server ODBC driver. Microsoft Query is an add-on program that comes with Excel, which also includes the ODBC drivers. If you didn't install Microsoft Query or the required ODBC drivers when you installed Excel, you can do so through the Office Setup program. Run Setup, then choose Add/Remove. Click on Converters, Filters and Data Access, then click Change Option. Click on Data Access and click Change Option again. You'll find Microsoft Query and the various ODBC drivers in the resulting list. Select the ones you need, then click on OK and follow the prompts to complete the installation process. Distant data Let's create a pivot table from an external data source using Microsoft Office Professional, which includes Microsoft Access and a sample database for a fictional company named Northwind Traders. If you haven't already done so, run Office Setup and install Access, along with the sample database. The database contains lots of fictitious (but convincing) data, and is perfect for this example. After you install Access and the Northwind Traders database, open Excel with a new spreadsheet. Choose Data/Pivot Table. In the Step 1 dialog box, choose External Data Source, then click on Next. In the Step 2 dialog box, click on Get Data. Microsoft Query will open and display a dialog box in which you specify the data source to use. In this example, select Microsoft Access from the Available Data Sources list, then click Use. Query displays a Select Database dialog box. Look in the ACCESS\SAMPLES folder of your main Office folder. Select the file NORTHWIND. MDB and click on OK. Next, you need to add the desired tables from the database to the query. For this example, we'll use the Products table. In the Tables list, locate and click on Products, then click on Add. Click on Close to close the dialog box. The next step is to specify the data items to include in the query. For this example, we'll concern ourselves primarily with the quantity on hand and on order for each product. From the Products list, click and drag ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock and UnitsOnOrder to the data area of the Query window. Then, choose File/Return Data to Microsoft Excel. You'll be switched back to Excel, but now the Step 2 pivot table dialog box indicates that data has been retrieved from Access. Click on Next to continue. In the Step 3 dialog box, drag the ProductName button to the Page field. This will let you view the data organized by product name. Next, drag the QuantityPerUnit button to the Row field. Drag the ProductID button to the Column field. Finally, drag the UnitsInStock and UnitsOnOrder buttons to the Data field. Then, click on Next and specify the location for the pivot table. Click on Finish to insert the pivot table in the spreadsheet. Now showing When the pivot table appears, you'll see that you can quickly and easily select a specific product from the ProductName drop-down list and view the quantity on hand and on order, along with the unit size for the product (12 bottles per case, for example). Still, why go through the trouble to view this data in Excel if you can perform a query in Access and get the same result? The primary reason is familiarity. You might be very familiar with Excel, but have no experience with Access. So, you can work in familiar surroundings without getting lost. Another important reason is the flexibility you have for organizing and viewing the data. So far, we've used the sums of the data field items. But what if you want to view the quantity on order as a percentage of the quantity on hand? It's a simple process to change the field. Just right-click on the Sum of UnitsOnOrder field and choose Pivot Table Field from the context menu. Click on the Options button to expand the dialog box. From the Show Data As drop-down list, choose % of. Then, click on UnitsInStock and choose OK. In Office 95, you'll be rewarded with the #N/A value that indicates an error, thanks to a bug in Excel 7.0. In Office 97, however, you'll see the value change to indicate a percentage. To find additional options for controlling and editing the pivot table, right-click on the table and note the commands available in the context menu. For example, choose Add Data Field from the context menu to add additional items to the data field in the table. A selection of options also appears in the Step 4 dialog box of the Pivot Table Wizard, and these options help you control the table's appearance. To fetch this dialog box from an existing pivot table, right-click on the pivot table and choose Pivot Table from the context menu. Then, click on Next to view the Step 4 dialog box. Four check boxes provide control over the appearance of the table and how its data is stored in the spreadsheet. The Grand Total for Columns and Grand Total for Rows check boxes control whether or not a running total is kept for the rows and columns. The Save Data With Table Layout check box determines whether or not the pivot table's data is stored with the spreadsheet. If you clear this check box and save the file, you'll have to refresh the data (Data/Refresh Data) the next time you open the file. Although this requires an additional step, it helps keep the size of your spreadsheet files to a minimum. The fourth check box on the Step 4 dialog box controls the table's overall appearance. When the AutoFormat Table check box is enabled, Excel uses a default format for the table. To apply a different format, click in the table and choose Format/AutoFormat. You can then choose from a selection of 2-D and 3-D predefined table formats. Basic training We've still only touched on the basics of pivot tables. How you create and apply them depends a lot on the type of data you need to organize. If you spend a few hours experimenting with pivot tables and your own spreadsheet and database information, I guarantee you'll find that pivot tables give you a much better handle on your data. Those hours will be well-spent. Contributing Editor Jim Boyce is the lead author of Windows NT Advanced Technical Reference (Que, 1996). 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.
|
