Working with Disparate Data Sources

Until this point, you have been working with one local table located in the worksheet within which you are operating. Indeed, it would be wonderful if every dataset you came across were neatly packed in one easy-to-use Excel table. Unfortunately, the business of data analysis does not always work out that way.

The reality is that some of the data you will encounter will come from disparate data sourcesmeaning sets of data that are from separate systems, stored in different locations, and saved in a variety of formats. In an Excel environment, disparate data sources generally fall into one of two categories: external data or multiple ranges.

External data is exactly what it sounds likedata that is not located in the Excel workbook in which you are operating. Some examples of external data sources are text files, Access tables, SQL Server tables, and other Excel workbooks.

Multiple ranges are separate datasets that are located in the same workbook, but separated either by blank cells or by different worksheets. For example, if your workbook has three tables on three different worksheets, each of your datasets covers a range of cells. You are therefore working with multiple ranges.

A pivot table can be an effective tool when you need to summarize data that is not neatly packed into one table. With a pivot table, you can quickly bring together either data found in an external source or data found in multiple tables within your workbook.

You may have noticed that when you initiate the creation of a pivot table, the PivotTable Wizard starts the process with the dialog box shown in Figure 8.1. In this dialog box, the first question you have to answer is, "Where is the data that you want to analyze?" Until this point, you have been working with a single dataset located in your local workbook. But as you can see, there are other options.

Figure 8.1. Until this point, you have been working with a single dataset located in your local workbook. However, as you can see, there are other options.

You can choose to work with a dataset from an external data source, you can choose to work with multiple ranges within your workbook, or you can choose to work with data already in a pivot table.

We will cover working with external data sources in the next chapter. As for now, we will concentrate on creating pivot tables from multiple ranges and other pivot tables.