One of the beauties of the Excel PivotTable reports is that they are not limited to using data located in a workbook. In fact, it is fairly common to have your data in a database instead. Depending on the configuration of your system and the database drivers installed, you can access data in a wide variety of database formats. In this section I will show you how to create a PivotTable report based on data in a Microsoft Access database. Although some of the details will be different for other database formats, the general principles are the same. For this section I will use the Northwind database. This is a sample database that is installed as part of most Microsoft Office installations. Its file name is Northwind.mdb and it is typically located in the Samples folder under the Microsoft Office installation in \Program Files. If you are not able to find it, you can use the Windows search feature to determine its location. (If you cannot find the file, it may not have been installed. You can get it from your Office installation CD and copy it to your hard disk.)
Before creating a PivotTable based on external data, be sure that you know which element in the external database you need. Typically the external database contains a variety of tables and queries and all of these will be available to you. For this demonstration I will use a query named Product Sales for 1997 that is defined in the Northwind database. This query is shown in Access in Figure 2-7; you can see that it contains data on product sales by category, product name, and quarter. Note that you do not have to have Access open to create the PivotTable.
Figure 2-7: The PivotTable will be based on the Northwind database query named Product Sales for 1997.
After you have Excel open with a blank worksheet displayed, follow these steps:
- Click the PivotTable button on the Insert ribbon to open the Create PivotTable dialog box.
- Select the Use an External Data Source option.
- Click the Choose Connection button to open the Existing Connections dialog box.
- Click the Browse for More button to open the Select Data Source dialog box.
- Navigate to and select the file Northwind.mdb as shown in Figure 2-8.
- Click OK to display the Select Table dialog box. (See Figure 2-9.)
- In the list, select Product Sales for 1997.
- Click OK to return to the Create PivotTable dialog box.
- Click OK to close the Create PivotTable dialog box and create the blank PivotTable, which is shown in Figure 2-10.
Figure 2-8: Navigate to and select Northwind.mdb.
Figure 2-9: Selecting the table to use for the PivotTable.
Figure 2-10: The blank PivotTable based on the Access query.
You can see that the four columns that were present in the Access query are listed in the Field List. Next you will add these fields to the blank PivotTable to achieve the data display you want. Because there are four fields, you have several ways to display these data. Follow these steps to create a PivotTable that displays total sales by category for each quarter:
- Select the CategoryName field to add it to the Row Labels area.
- Select the ShippedQuarter field to add it to the Row Labels area.
- In the Row Labels area, click the arrow next to the ShippedQuarter field and select Move to Column Labels to move this field to the Column Labels area.
- Select the ProductSales field to move it to the Values area.
- In the Values area, click the down arrow next to the Sum of ProductSales field and select Value Field Settings from the popup menu to display the Value Field Setting dialog box.
- 6. Click the Number Format button to open the Format Cells dialog box.
- Select Currency format with no decimal places.
- Click OK twice to close all dialog boxes.
The resulting PivotTable report is shown in Figure 2-11.
Figure 2-11: The final PivotTable after you have placed fields and applied basic formatting.
