2.3. Returning a PivotTable to Excel

Although collecting the data through the query is great, you may want to import data in a more analyzed form. Even if you are unfamiliar with PivotTables , you may find them preferable to Subtotals or any other summarizing capabilities that you currently use. When you use PivotTables, Excel makes it very easy to use their data to create a chart called a PivotChart. While you could always create a PivotTable on your own and select the name of your Data Range as the source, there may be situations when you want to see the PivotTable.

When you have gone through the previous example and are at the Import Data dialog box, click on the hyperlink for "Create a PivotTable report." You are now in Step 3 of 3 in the PivotTable Wizard (Figure 2-11). Before the PivotTable can be created, you need to click the Layout button, which brings up the screen in Figure 2-12. If you are unfamiliar with PivotTables, you can select fields to be used for Pages, Rows, Columns, and Data. For this example, let's assume that you want to see the number of shipments with freight over $100 that were shipped to each country and the total amount of the freight. To do this, click and drag Country to the Row section, and click and drag Freight to the Data section. Notice that Freight defaults to Sum; double-click on the Freight box and change it to the summarize by count option. Then click and drag the Freight field into the Data section, which defaults to Sum again. This gives you the selections for the PivotTable (Figure 2-12). Press OK, and it brings you back to the screen in Figure 2-11. Take the default of putting it into an existing worksheet beginning in cell A3 and press Finish. Your default location may vary, depending on which cell you started in. Just keep in mind that you can change the location of where the data will be placed before pressing Finish.

To add additional functionality, in the screen shown in Figure 2-12, put Country as a Page field and City as a Row field. This allows you to limit the PivotTable to one country in which you could see the cities. You could also make a PivotChart out of these results by right-clicking on the PivotTable and selecting PivotChart from the menu. However, you will notice that the chart does not present very meaningful results because you have both the count and the sum on the same axis. You can fix this by clicking on the drop-down box for data and unselecting either the Count or Sum field.

Figure 2-11. The PivotTable and PivotChart Wizard final screen

Figure 2-12. The Layout screen of the PivotTable and PivotChart Wizard

PivotTables also provide the ability to see the underlying data simply by double-clicking on any result. To support this feature, select all of the fields that you want included in your query, even if they will not be shown in the PivotTable. The data shown by double-clicking on the result field is every column and row from the query that is represented by that result. This can be a very powerful feature for analyzing data. Imagine a situation when you have general ledger transactions on one Excel worksheet and a PivotTable that summarizes the information by expense category on another worksheet. If you were using any other summary report, you would need to individually identify the rows from the general ledger to research an expense category. This would be very time-consuming and might introduce errors, but using a PivotTable allows you to simply double-click on any number to get the related rows. It also makes producing summary reports and charts a snap.