If your source data exists in two or more ranges, Excel can consolidate all the ranges and then produce a PivotTable report based on the consolidated data.

Many businesses create worksheets for a specific task and then distribute them to various departments. The most common example is budgeting. Accounting might create a generic "budget" template that each department or division in the company must fill out and return. Similarly, you often see worksheets distributed for inventory requirements, sales forecasting, survey data, experiment results, and more.

Creating these worksheets, distributing them, and filling them in are all straightforward operations. The tricky part, however, comes when the sheets are returned to the originating department where all the new data must be combined into a summary report showing company-wide totals. This task is called consolidating the data, and it is often difficult and time-consuming, especially for large worksheets. However, Excel has a powerful PivotTable feature that can make it easy to consolidate the data and summarize it into a simple report.

Create a PivotTable from Multiple Consolidation Ranges

Note

This chapter uses the spreadsheets Division_I_Budget.xls, Division_II_Budget.xls, Division_III_Budget.xls, and PivotTables.xls, and the Web page Orders.htm, available at www.wiley.com/go/pivottablesvb. You can also create your own sample database.

The PivotTable and PivotChart Wizard appears.

The next PivotTable and PivotChart Wizard dialog box appears.

The next PivotTable and PivotChart Wizard dialog box appears.

Excel collapses the dialog box to show just the Range input box.

Apply It

Another way to summarize data that exists in multiple ranges is to use Excel's Consolidate feature. With this feature, Excel can consolidate your data using one of two methods:

• Consolidate by position — Excel consolidates the data from several worksheets using the same range coordinates on each sheet. You can use this method if the worksheets you are consolidating have an identical layout.

• Consolidate by category — Excel consolidates the data by looking for identical row and column labels in each sheet. So, for example, if one worksheet lists monthly Gizmo sales in row 1 and another lists monthly Gizmo sales in row 5, you can still consolidate as long as both sheets have a "Gizmo" label at the beginning of these rows.

To use this feature, select Data→Consolidate to display the Consolidate dialog box. Click the summary Function you want to use, such as Sum or Count, enter the range references for the source data, and then click OK.

In a PivotTable based on multiple consolidation ranges, Excel only offers a limited layout: a row field, a column field, a value (data) field, and up to four page fields. The items in the row field come from the leftmost columns of the source data ranges; the items in the column field come from the topmost row in the source data ranges; and the items in the value field come from the rest of the source data ranges.

For the page field, Excel sets up the report so that you can display the data from all the ranges or just the data from one of the ranges. In other words, Excel enables you to filter the PivotTable report based on the source ranges, and it uses the page field to do this.

However, if you let Excel set up this page field for you, it uses generic item names such as Item1, Item2, and Item3. To avoid the hassle of renaming these items after you create the PivotTable, you can specify them as you work with the PivotTable and PivotChart Wizard. That is why you selected the "I will create the page fields" option in Step 5. In Steps 12 to 15 of this task, you define the page field item names yourself.

Excel restores the PivotTable and PivotChart Wizard.

The final PivotTable and PivotChart Wizard dialog box appears.

Excel consolidates the ranges and creates the PivotTable.

Extra

When you complete the wizard, the PivotTable Field List shows only four items: Row, Column, Value, and Page1. You can add up to three more page fields, but other than that you cannot add any other fields to the Field List. However, you are free to rename the fields to provide more meaningful names; in Chapter 5, see the task "Rename a PivotTable Field." You can also perform most other PivotTable tasks, including pivoting the fields and changing the summary calculation.

To create more page fields, click PivotTable→PivotTable Wizard and then click Back to display the PivotTable and PivotChart Wizard's Step 2b of 3 dialog box. Select the number of page fields you want: 2, 3, or 4 (

If you allowed Excel to create the page field automatically, you need to rename the page field items to give them more meaningful names. Pivot the page field into the row area so that the items are visible. Rename each item, and then pivot the field back into the page area.

You can save time and effort by creating a new PivotTable based on the data in an existing PivotTable.

You learned in several places throughout this book that Excel maintains a pivot cache for each PivotTable. This pivot cache is a memory location that holds the source data and other information relating to the PivotTable. Keeping this data in memory means that your PivotTable recalculates quickly when you change the layout, grouping, filtering, or summary calculation. The price you pay for having the pivot cache is extra workbook size and less memory available for other tasks, but the tradeoff is usually worth it.

You can minimize the downside of the pivot cache by building new PivotTables based on existing PivotTables wherever possible. If you build a new PivotTable using the same data source as an existing PivotTable, Excel asks if you want to base the new report on the existing report. However, you can also build a new PivotTable from an existing PivotTable on purpose. Doing this not only means that the two PivotTables share the same pivot cache, but it also takes less time and effort to build the new PivotTable.

Create a PivotTable from an Existing PivotTable

The PivotTable and PivotChart Wizard appears.

The second PivotTable and PivotChart Wizard dialog box appears.

Excel creates an empty PivotTable.

Apply It

If you know the name of the PivotTable report from which you want to create your new PivotTable, you can use the Worksheet object's PivotTableWizard method to create the new PivotTable. Set the SourceType parameter to xlPivotTable and set the SourceData parameter to the name of the existing PivotTable, including the workbook name — enclosed in square brackets [] — worksheet name, and PivotTable name, as shown in the following example:

Example:

ActiveSheet.PivotTableWizard _
    SourceType:=xlPivotTable, _
    SourceData:="[Pivot Tables.xls]Orders Pivot Table!PivotTable4"

You can create a PivotTable using an external data source, which enables you to build reports from extremely large datasets and from relational database systems.

So far in this book you have learned about data sources that reside on Excel worksheets as ranges or lists. This is a convenient way to work with PivotTables because you have access to the source data, enabling you to easily change field names, add and delete fields, insert records, and so on. However, working with data in Excel suffers from two major drawbacks:

To overcome these limitations, you need to use a relational database management system (RDBMS) such as Microsoft Access or SQL Server. With these programs, you can set up a table, query, or other object that defines the data you want to work with. In most cases, the data object can be as complex and as large as you need. You can then build your PivotTable based on this external data source.

Create a PivotTable from External Data

The PivotTable and PivotChart Wizard appears.

The second PivotTable and PivotChart Wizard dialog box appears.

The Choose Data Source dialog box appears.

Note

To learn how to create data sources, see the Appendix B task "Define a Data Source."

The Query Wizard's Choose Columns dialog box appears.

The Query Wizard's Filter Data dialog box appears.

Extra

You can reduce the size of the new PivotTable's pivot cache by including only those fields that you need for your PivotTable. In the Query Wizard's Choose Columns dialog box, each table has a plus sign (+) beside it. Click a table's plus sign to display a list of that object's fields, or columns, as the Query Wizard calls them. You can then click a field and click the > button to add it to the list of fields to be used with your PivotTable. You can also double-click the field.

If you are not sure what items a field contains, click the field in either list and then click Preview Now. The Query Wizard displays the field's items in the "Preview of data in selected column" list.

If you add a table, query, or field by mistake, click the item in the "Columns in your query" list, and then click the < button to remove it. If you want to start over, click the << button to remove everything from the "Columns in your query" list.

The Choose Data Source dialog box and the various Query Wizard dialog boxes are not part of Excel. Instead, they are components of a program called Microsoft Query. You can use this program to work with external data. For more detail on how this program works, see Appendix B. For the purposes of this task, I assume that you have already defined the appropriate data source, as shown in the Appendix B task "Define a Data Source," and that you do not want to work with Microsoft Query directly; see the Appendix B task "Start Microsoft Query." Note, too, that Steps 10 and 11 essentially skip over the Query Wizard dialog boxes that enable you to filter and sort the external data, because this is not usually pertinent for a PivotTable report. For the details of these steps, in Appendix B, see the task "Define a Data Source."

The other assumption I made in this task is that you do not want the external data imported to Excel. Rather, in this task the external data resides only in the new PivotTable's pivot cache; you do not see the data itself in your workbook. This is particularly useful if the external data contains more than 65,536 records, because otherwise Excel would not allow you to import so much data. However, you can still easily refresh and rebuild your PivotTable, just like you can with a report based on a local range or list. If you want to learn how to import external data into Excel, see Appendix C.

The Query Wizard's Sort Order dialog box appears.

The Query Wizard's Finish dialog box appears.

The second PivotTable and PivotChart Wizard dialog box appears.

Excel creates an empty PivotTable.

Extra

The most common drawback to using an external data source is that you often have no control over the external file itself. This may mean that the database login data changes, or that the file might get moved to a new location, renamed, or even deleted. If this happens and you attempt to refresh the PivotTable, Excel displays an error message. For example, if the external data source is an Access database, Excel displays an error dialog box telling you that, for example, it cannot find the external database file. In this case, click OK to close the dialog box and display the Login dialog box.

If you suspect the problem is a change to the login data, find out the correct login name and password from the database administrator, enter the new data in the Login dialog box, and then click OK.

If you suspect the problem is that the database file has been moved or renamed, click Database in the Login dialog box. You can use the Select Database dialog box to find and click the database file, and then click OK to return to the Login dialog box. Click OK to return to Excel.

If the external source contains a large amount of data, Excel may run very slowly or it may display an out of memory error. You can work around these problems by setting up a server-based page field that retrieves page data only when you request it from the server.

When you connect to an external data source, and when you refresh a PivotTable based on an external data source, Excel retrieves all the data from the external file. If you are dealing with a database on a remote server, or if the source contains a large amount of data, retrieving all the data may take a very long time.

Part of the problem is that Excel retrieves all the data even if you are currently displaying the results for just a single page field: Excel still retrieves the data for those page fields that are not currently displayed. This increases the performance of the PivotTable when you switch pages, but it greatly increases the amount of memory that Excel uses and it slows down the refreshing of the PivotTable.

To overcome these problems, you can configure the page field as a server page field. This means that Excel only retrieves the data for the currently displayed page field. When you display a different page, Excel queries the server and retrieves the new data — this is called querying page-by-page. This means it takes longer to switch pages, but it greatly reduces the amount of memory used by Excel, and it speeds up the refreshing of the PivotTable.

Note that querying page-by-page is only available for certain types of external data, such as SQL Server data sources. This technique is unreliable when used with Access databases.

Set Up a Server-Based Page Field

You can also double-click the page field button.

The PivotTable Field dialog box appears.

The PivotTable Field Advanced Options dialog box appears.

Excel returns you to the PivotTable Field dialog box.

Extra

If you find that the "Query external data source" option is unavailable, check to see if your page field items are grouped. You cannot query page-by-page using grouped pages, so ungroup the page field before following the steps in this task.

In the PivotTable Field Advanced Options dialog box, after you select "Query external data source"(

When you set up a server page field, Excel disables the All item in the page field drop-down list. If you have the All item displayed prior to setting up the server page field, Excel always displays the first item in the page field after you set up page-by-page querying. Note, too, that Excel also disables the Show Pages command in the PivotTable toolbar.

Excel offers several options related to PivotTables built using external data sources. You can use these options to save time and effort and to reduce the amount of memory used by these PivotTables. In the PivotTable Options dialog box, there are three check boxes related to external data sources:

Set External Data Options

The PivotTable Options dialog box appears.

Excel puts the external data source options into effect.

Apply It

You can control the external data source options using VBA. You can use the PivotTable object's PivotCache property, which is itself an object with three properties that correspond to the three check boxes: SavePassword, BackgroundQuery, and OptimizeCache. The following macro toggles these three properties on and off:

Example:

Sub ToggleExternalDataOptions()
    Dim objPT As PivotTable
    Set objPT = ActiveSheet.PivotTables(1)
    With objPT.PivotCache
        .SavePassword = Not .SavePassword
        .BackgroundQuery = Not .BackgroundQuery
        .OptimizeCache = Not .OptimizeCache
    End With
End Sub

If you are working with a PivotTable list on a Web page, you can export that PivotTable's data from the Web page to Excel.

In Chapter 3, you learned how to publish a PivotTable to a Web page; see the task "Publish a PivotTable to a Web Page." The resulting Web page-based PivotTable is displayed using an Office Web component called a PivotTable list. If you are working with a PivotTable list on a Web page, you can also perform the opposite technique and export the PivotTable list to Excel. This gives you a regular Excel PivotTable in a read-only file, which you can then save to your computer.

Export PivotTable Data from a Web Page to Excel

If the PivotTable list contains features not supported by PivotTable reports, a warning dialog box appears.

The PivotTable appears in a temporary Excel worksheet.

Extra

A PivotTable list looks and operates much like a PivotTable report, but there are a few differences that affect how the PivotTable list gets imported into Excel. This is why you see a warning dialog box if the PivotTable list contains features not supported by Excel's PivotTable reports.

For example, PivotTable lists enable you to display summary detail and underlying detail simultaneously, but PivotTable reports do not. Therefore, calculated fields in the PivotTable list detail area are not exported to Excel. Similarly, PivotTable lists support hyperlinks, but PivotTable reports do not, so hyperlinks are not exported. Finally, you can group items in a PivotTable list by text, such as the first few characters, and by week — neither of which is available in PivotTable reports, so these groupings are not exported.

You can reduce the size of your Excel workbooks by not saving the PivotTable source data in the pivot cache.

If you build a PivotTable from data that resides in a different workbook or in an external data source, Excel stores the source data in the pivot cache. This greatly reduces the time it takes to refresh and recalculate the PivotTable. The downside is that it can increase both the size of the workbook and the amount of time it takes Excel to save the workbook. If your workbook has become too large or it takes too long to save, you can tell Excel not to save the source data in the pivot cache.

You can control whether Excel uses the pivot cache via VBA. To do this, set the PivotTable object's SaveData property to True, with the pivot cache on, or to False, with the pivot cache off, as shown in the following macro:

Sub TogglePivotCache()
    Dim objPT As PivotTable
    ' Work with the first PivotTable on the active worksheet
    Set objPT = ActiveSheet.PivotTables(1)
    ' Toggle the SaveData property
    objPT.SaveData = Not objPT.SaveData
    ' Display the current state
    MsgBox "The PivotTable's pivot cache is now " & _
           IIf(obj.SaveData, "on.", "off.")
End Sub

Reduce the Size of PivotTable Workbooks

The PivotTable Options dialog box appears.

Excel puts the external data source options into effect.

When you reference a PivotTable report value in a formula, you can ensure that the reference remains accurate by using a special Excel worksheet function.

As part of your data analysis, you may need to use a value from a PivotTable report in a worksheet formula that resides outside the PivotTable. You normally reference a cell in a formula by using the cell's address. However, this does not work with PivotTables because the addresses of the report values change as you pivot, filter, group, and refresh the PivotTable.

To ensure accurate PivotTable references, use Excel's GETPIVOTDATA worksheet function. This function uses the data field, PivotTable location, and one or more (row or column) field/item pairs that specify the exact value you want to use. This way, no matter what the PivotTable layout, as long as the value remains visible in the report, your formula reference remains accurate.

If you want to reference a PivotTable value only temporarily, you might prefer that Excel not generate the GETPIVOTDATA function. To turn off this feature, click the Generate GetPivotData toolbar button (

Use a PivotTable Value in a Formula

Excel includes the PivotTable value in the formula result.

Note

The GETPIVOTDATA function looks complicated, but it really only contains a few parameters. The first parameter is the name of the data field ("Quantity" in the example); the second parameter is the location of the PivotTable ("$A$3"); subsequent parameters come in pairs: a field name ("ShipRegion") and an item in that field ("Oregon").