Using Multiple Consolidation Ranges

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:

You can obtain the original data used to create this summary. This seems like a good choice, but in most cases, you could find another solution by the time it took you to obtain the original dataif you have access to it at all.
You can manually shape the data into a proper tabular dataset and then do your analysis. In reality, this option would be the best one if you had the time to spare or you were planning to use this data on an on-going basis. However, if this is a one-time analysis or if you are in a crunch, you would not want to spend the time to manually format this data.
You can create a pivot table using multiple consolidation ranges. With this pivot table option, you can quickly and easily consolidate all the data from your selected ranges into a single pivot table. This is the best option if you only need to perform a one-time analysis on multiple ranges or if you need to analyze multiple ranges in a hurry.

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.

CAUTION

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.