How to Create a Pivot Table Using Multiple Sources of Data

Microsoft Excel is a valuable spreadsheet program that small businesses can use to manage much of their business data. In particular, Excel's pivot tables allow you the ability to summarize large amounts of information, such as employee sales or customer purchases, to analyze such things as performance or inventory needs. With pivot tables, you can also specify which fields to include, in the event that you want to use the same sources to produce different tables for different audiences. You can also use multiple sources of data to create one pivot table. The process is fairly easy.

How to Create a Pivot Table Using Multiple Sources of Data

Step 1

Open your Excel spreadsheet and ensure that all of your data sources have matching rows and columns. Also, do not include any total rows or total columns.

Step 2

Click on a blank cell in your Excel worksheet and then hit ALT+D and then P. This brings up the PivotTable and PivotChart Wizard.

Step 3

Under the "Where is the data you want to analyze," select "Multiple consolidation ranges." Verify that the PivotTable report option is selected and then press "Next."

Step 4

Select "I will create the page fields" and then click "Next." The "Range" box will be selected. Click on the range within your worksheet that you want to include in your pivot table and then click "Add." For multiple data sources, repeat until you have selected and added all applicable ranges.

Step 5

Select the amount of page fields, up to four, and include the page field labels. If you have none, select "0." Then click "Next."

Step 6

Select where you want your pivot table to go, such as in a new worksheet or an existing worksheet and click "Finish." Your pivot table will then pop up, along with the "PivotTable Field List" pane. In the pane, select those fields that you wish to include in your report. To hide the pane, simply click on an empty cell. To bring it back, click on the pivot table.