Importing and Using External Data Without the PivotTable Wizard

You have just successfully built a pivot table that analyzes more than 80,000 rows of data. This would be impossible (or at least difficult) to do with local data. The reason you were able to do this is that you brought external data directly into your pivot cache using the PivotTable Wizard. By bringing external data directly into a pivot cache, you avoid the physical limitations of a spreadsheet.

The drawback to using the PivotTable Wizard to bring in external data is that you won't have immediate access to detailed records. That is, your raw dataset is not hard-coded onto a spreadsheet where you can AutoFilter it, see items not in your pivot table, or perform any number of other actions against it. Luckily, as with most actions in Excel, there is more than one way to bring in and use external data.

You have the option of importing and writing your external data directly onto your spreadsheet. You can do this by going up to the application menu and selecting Data, Import External Data, New Database Query. This will initiate the same MS Query wizards you went through in the first case study of this chapter.

NOTE

If you are using Excel 2000, you will not find Import External Data under the Data menu. Instead, this selection is called Get External Data.

After your external dataset has been imported, you can create a pivot table from it just as you would do with a local data source. The benefit of using this method is that you can create a pivot table summary of the data on one sheet and a formatted view of the details on another sheet. This method is ideal if you are distributing a report to users who need to have both summary and details in one workbook. Keep in mind though, that because you are writing data directly into a spreadsheet, you are limited to the spreadsheet's physical limitations (that is, 256 columns, 65,536 rows).