Appendix 3. Importing Data for PivotTables

Understanding External Data

External data is data that resides outside of Excel in a file, database, server, or Web site. You can import external data into Excel either directly into a PivotTable or into a worksheet for additional types of data analysis.

A vast amount of data exists in the world, and most of it resides in some kind of nonworkbook format. Some data exists in simple text files, perhaps as comma‐separated lists of items. Other data resides in tables, either in Word documents or, more likely, in Access databases. There is also an increasing amount of data that resides in Web pages and in XML files.

By definition, all this data is not directly available to you via Excel. However, Excel offers a number of tools that enable you to import external data into the program. Depending on your needs and on the type of data, you can either import the data directly into a PivotTable report, or you can store the data on a worksheet and then build your PivotTable from the resulting worksheet range. In most cases, Excel also enables you to refresh the data so that you are always working with the most up‐to‐date version of the data.

External Data Types

Excel can access a wide variety of external data types. However, in this appendix you only learn about six of them: data source files, Access tables, Word tables, text files, Web pages, and XML files.

Data Source File

In Appendix B, you learned about ODBC data sources, which give you access to data ...

Get Excel® 2007 PivotTables and PivotCharts now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.