If you need to analyze data dispersed in multiple ranges, your options are somewhat limited. For example, the data in Figure 8.2 shows you three ranges that you need to bring together to analyze as a group.
Figure 8.2. Someone passed you a file that has three ranges of data. You need to bring the three ranges together so you can analyze them as a group.
You essentially have three paths you can take to get to the point where you can analyze all three ranges together:
To start the process of bringing this data together with a pivot table, initiate the PivotTable Wizard shown in Figure 8.3 and select the Multiple Consolidation Ranges option.
Figure 8.3. Start the PivotTable Wizard and select Multiple Consolidation Ranges. Click Next to move to the next step.
Figure 8.4 shows the next step, where you will specify whether you want Excel to create one page field for you or whether you would like to create your own. You will find that the page fields that Excel creates are, more often than not, ambiguous and of no value. Therefore, in almost all cases, you will want to select the option of creating your own page fields.
Figure 8.4. Specify that you want to create your own page fields and then click Next.
Next, you need to point Excel to each of your individual datasets, one by one. As you can see in Figure 8.5, you simply enter the entire range of your first dataset into the Range input field. After your range has been defined, click Add.
Figure 8.5. Select the entire range of your first dataset and click Add.
In order for your pivot table to generate properly, the first line of each range must include column labels.
Select the rest of your ranges and then add them your list of ranges. At this point, your dialog box should look similar to the one shown in Figure 8.6.
Figure 8.6. Add the other two dataset ranges to your range list.
You will notice that each of your datasets belong to a region (North, South, or West). When your pivot table brings your three datasets together, you will need a way to parse out each region again.
To ensure you will have that capability, you need to tag each range in your list of ranges with a name identifying which dataset that range came from. The result will be the creation of a page field that allows you to filter each region as needed.
The first thing you will have to do in order to create your Region page field is specify how many page fields you want to create. In Figure 8.7, you only want to create one page field for your region identifier, so simply click on the radio button next to the number 1. This action will enable the Field One input box. As you can see, you can create up to four page fields.
Figure 8.7. Click on the radio button next to the number 1. This action will enable the Field One input box.
In the next step, shown in Figure 8.8, you will have to tag each range one by one, so click on the first range in your range list to highlight it. Enter the region name into the Field One input box.
Figure 8.8. Select the first range that represents the dataset for the North region and enter the word "North" into the page field.
Repeat the process for the other regions. When you are done, click Next and then click Finish.
You have successfully brought three data sources together into one pivot table! At first glance, your final pivot table, shown in Figure 8.9, looks just like any other pivot table. However, a closer look will reveal that this pivot table has some strange default labels.
Figure 8.9. You now have a pivot table that contains data from three data sources.