So far, you've created pivot tables from data that is stored in Excel tables. You can also create a pivot table in Excel from many kinds of external data sources. In this chapter, you'll connect to data that is outside Excel, and you'll create pivot tables from that data. You can download and work with a text file, an OLAP cube, and a Microsoft Access query to see how the pivot table creation process is slightly different depending on the source data. You'll also see some differences in pivot tables that are created from external data sources compared to pivot tables based on Excel data.
To create a pivot table from data that is outside Excel, you can import the data to a worksheet and create the pivot table from the imported data, or you can create a connection to the external data, telling Excel where the file is and how to connect to it, and then create a pivot table from the data.
You'll use both techniques to create pivot tables in the examples in this chapter:
You have been sent a text file from the human resources (HR) department, and the HR manager has asked you to create a report from this file to summarize the employee test scores by location. Each month a new version of the text file will be created and sent to you so you can create an updated report. To make the updates easier, you'll connect to the text file and create a pivot table from the external data.
The first time you use a text file as the source for a pivot table, you should check it to make sure it's set up correctly. If necessary, you can make changes to the file so it's ready to use as the source for your pivot table. You'll download and check the sample file that contains the employee test scores.
EmployeeData.txt
, and save it in one of your folders. In this example, the file was saved in the c:Data
folder.EmployeeData.txt
file to see the data it contains (see Figure 7-1).Figure 7-1. Text file opened in Notepad
After you have examined the text file, the next step is to open the workbook in which you want the pivot table or create a new workbook where you will create the pivot table. To create the report for the HR manager, you'll create a new workbook and build the pivot table there using the EmployeeData.txt
file.
As a first step, you'll import the text file onto a worksheet, and later you'll create the pivot table from the imported data:
Note You may see a single Get External button instead of several buttons. Click the Get External Data button, and then click From Text.
Figure 7-2. From Text command on the Ribbon
EmployeeData.txt
file, select the file, and click Import (see Figure 7-3).Figure 7-3. Import Text File dialog box
EmployeeData.txt
file, select Delimited (see Figure 7-4).Figure 7-4. Text Import Wizard's Step 1 of 3 page
In the preview section at the bottom of the dialog box, you can see the first few rows of data from the text file with small squares between the fields. These represent the tab characters that separate the fields in the text file.
EmployeeData.txt
file, select Tab (see Figure 7-5).Figure 7-5. Text Import Wizard's Step 2 of 3 page
Figure 7-6. Text Import Wizard's Step 3 of 3 page
Figure 7-7. Import Data dialog box
EmployeeData.txt
file appears on the worksheet, and you can create a pivot table from this imported data.EmpDataPivot.xlsx
and saved in the c:Data
folder.By importing the EmployeeData.txt
file, you created a connection in the workbook. This is a link to the EmployeeData.txt
file outside Excel, and you can change some settings for this connection to control how it works. For example, you can specify how often the data should be refreshed and what should happen to the formatting when the data is refreshed.
You'll view and test the current connection settings and then change some of the settings for the connection to the EmployeeData.txt
file:
Figure 7-8. Properties command on the Ribbon
Figure 7-9. External Data Range Properties dialog box
EmployeeData.txt
file each time you refreshed the data. This setting may be useful if the file name changes each day, for example, if there is a daily text file download. In this case, the text file will remain the same, so you won't want to be prompted during each refresh.EmployeeData.txt
file, the revised data will be available in the Excel file.You'll leave the Excel file open while you make a change in the EmployeeData.txt
file, and then you'll refresh the external data in Excel to test the settings you changed. The first name in the text file was typed incorrectly, so you'll change it.
EmployeeData.txt
file, and in the first record, change the name from Celine to Celina.EmployeeData.txt
file.EmployeeData.txt
file. The name in the first record has changed to Celina.Next, you'll close the Excel file and then make another change to the EmployeeData.txt
file. When you reopen the Excel file, the external data should automatically refresh, because you changed that setting for the external data connection. The first employee's name needs further correction, so you'll make the additional change.
EmpDataPivot.xlsx
file.EmployeeData.txt
file, and in the first record, change the last name from Bloom to Blume.EmployeeData.txt
file.EmpDataPivot.xlsx
file, and check the name in the first record to verify that it was changed from Bloom to Blume.If the name in the first record was not changed to Blume, your security settings may have prevented the update. You may be able to adjust these settings, as described in this section, or temporarily allow updates each time you open the file.
If the name in the first record was successfully changed, your security settings are allowing data connections and won't need to be changed. You can skip this section and proceed to the next section to create the pivot table.
Figure 7-10. Security warning for data connections
Figure 7-11. Microsoft Office Security Options dialog box
If the Security Warning bar was not visible but the file did not refresh, your security settings may have the message bar turned off. The content from the external data file has been blocked, but you do not see the warning. You can manually refresh the external data:
Figure 7-12. Microsoft Office Excel Security Notice dialog box
To permanently allow the connection each time you open the Excel file, you can add the source data file to a trusted location. These are folders that you or your system administrator has designated as safe locations, and all files in these folders can be opened without a security warning. To view or change these locations or to change other security settings, you can use the Trust Center, accessible through the Microsoft Office Button (click Excel Options, and then click Trust Center). If you're not sure about these settings, check with your system administrator.
Now that the text file has been imported to a worksheet, you can create a pivot table from the external data. The HR manager has asked for a summary of employee test scores by location, so you will show a list of locations with a count of employees and the average test score for each location.
Usually you would create an Excel table from the data on a worksheet and then use the Excel table as the source for the pivot table. However, this data is in an external range that has the name EmployeeData, which you saw in the External Data Range Properties dialog box. If you try to create an Excel table from this data, it will override the external data range, and you will lose your connection to the text file.
Caution Use the PivotTable command. Do not click the Table command and create an Excel table, or you will remove your connection to the text file.
Figure 7-13. PivotTable command on the Ribbon
The Create PivotTable dialog box opens and shows the address of the external data range, Sheet1!$A$1:$H$501, in the Table/Range box. You can use the external data range address as the source range, and the pivot table will automatically adjust if rows are added or removed in the source data. However, using the name of the external data range may make the data source easier to identify when working with the pivot table.
Figure 7-14. Entering the external data range name
EmployeeData.txt
file.Note If the external data has changed, that will not be reflected in the pivot table until the imported data is refreshed and then the pivot table is refreshed. Refreshing the pivot table does not refresh the imported data.
Figure 7-15. Pivot table from text file
EmpDataPivot.xlsx
file.
Another source that you can use for pivot tables is a query in a Microsoft Access file. In this example, you'll download a small database in which shipments are recorded. You do not need a copy of Microsoft Access on your computer in order to connect to an Access database from Excel.
Impressed by the report you created for the HR manager, the shipping manager has sent you a copy of the shipments database and has asked you to summarize the freight charges for each county. In the database is a query that combines data from three tables: Shipments, Contracts, and Counties. You'll connect to this query and create a pivot table to summarize the freight charges by county. When you create this report in Excel, employees who don't have Access installed will be able to view and manipulate the shipments report.
Shipments.accdb
, and save it in one of your folders. In this example, the file was saved in the c:Data
folder.Figure 7-16. ShipmentsByDate query in Access database
Tip It is best to include these line calculations in the Access query to ensure that the results are accurate, because the pivot table cannot calculate the line details.
In the following steps, you'll create a new workbook and build the pivot table there using the query in the Shipments.accdb
Access database. You'll connect to the query and create the pivot table directly from the data. Unlike the text file you used for the Employee test scores pivot table, you won't import the Access data onto a worksheet.
Shipments.accdb
file, and then click Open.Figure 7-17. Select Table dialog box
Figure 7-18. Import Data dialog box
An empty pivot table is created on Sheet1. In the PivotTable Field List pane, you can see the field names from the ShipmentsByDate query. You created a connection to the query in the Access database, and you can use its data in your pivot table.
You'll add fields to the pivot table layout to show a list of counties, with a count of shipments and the average total freight for each county:
To count the shipments, you'll add the delivery date field, DelDate, to the Values area. Since each record has a delivery date, this will be a reliable field to use for the count.
To calculate the average shipping cost, you'll add the total amount field, TotalAmt, to the Values area.
Figure 7-19. Pivot table from Access query
ShipmentPivot.xlsx
. In this example, the file was saved in the c:Data
folder.By creating a pivot table from the Access query, you created a connection in the workbook. This is a link to the Access file outside Excel, and just as you did for the text file connection earlier, you can change some settings for this connection to control how it works.
You'll view the current connection settings and then change the settings for the connection to the ShipmentsByDate query to ensure that the data is refreshed frequently while the Excel file is open. Then, if the shipping manager opens your workbook first thing in the morning, the data will be refreshed throughout the day if the workbook is left open.
Figure 7-20. Connections command on the Ribbon
Figure 7-21. Workbook Connections dialog box
In the lower half of the Workbook Connections dialog box, you can view a list of locations where a connection is used in the workbook. You'll select the Shipments connection and see where it is used.
Figure 7-22. List of locations where the selected connection is used
When the list of locations is visible, you can use it to select any location that is listed. This feature is especially helpful if you have multiple connections and pivot tables in a workbook and want to understand how things were set up.
From the Workbook Connections dialog box, you can access the Connection Properties dialog box to view or change the properties for the selected connection. You want to change the refresh settings for the connection, so you'll open the Connection Properties dialog box.
Tip You can type a description to explain what data the connection contains, why the connection is used, or other notes that will help you when using the file.
Figure 7-23. Connection Properties dialog box
Note This option is available only when using external data.
Figure 7-24. Definition tab in the Connection Properties dialog box
The Security Warning bar may appear, alerting you that the data connections have been disabled. Just as you did for the EmployeeData.txt
file connection, you can click the Options button and enable the content. You can also add the source file to a Trusted Location to prevent the Security Warning bar from reappearing.
The shipping manager likes the pivot table you created to show the shipments per county and asks whether you can create another report in the same workbook. In this report, the number of shipments per delivery truck should be listed to see whether the loads are evenly distributed among the drivers.
After you have created a connection in a workbook, you can use it again to create a pivot table or to import data to a worksheet. You'll reuse the Shipments connection to create another pivot table. On Sheet2, you'll create a pivot table to summarize the data by truck to count the shipments per county for each delivery truck:
Figure 7-25. Existing Connections command on the Ribbon
Note If the active cell is in a pivot table, the Existing Connections command won't be available.
Figure 7-26. Existing Connections dialog box
Now you can create a different pivot table from the external data. You'll show a list of delivery trucks, with a count of shipments for each truck.
Figure 7-27. Reusing a connection to create another pivot table
ShipmentPivot.xlsx
file, and then close it.Another source that you can use for pivot tables is an OLAP cube. This is aggregated data from an Online Analytic Processing (OLAP) database and can provide an efficient way for you to analyze data from SQL Server or another large database. In this example, you'll use a small OLAP cube, created from a hardware sales database. It will give you an opportunity to connect to the OLAP cube and see the differences in a pivot table that is created from this type of data source.
Using a pivot table, you can easily connect to thousands of rows of data in an Excel table or in a data source outside Excel. However, for some applications, there may be hundreds of thousands or millions of rows of data, which could be more than a pivot table is able to process with acceptable speed or could exceed the memory limits of your computer. For example, a national hardware chain might have hundreds of thousands of purchases each month. To efficiently process and store that much data, large and powerful databases are required.
The vice president of sales has asked you to create a couple of reports from transaction data that has been collected by a hardware chain. Figure 7-28 shows a small sample of the data. For each transaction, information about the product, the selling location, the date, the quantity sold, and the price is entered.
Figure 7-28. Hardware sales records
You won't be permitted to connect your pivot table directly to the transaction database. Instead, the database administrator will create a subset of the data for you in an OLAP cube. In the OLAP cube, instead of raw data, the data will be aggregated based on the information you need to analyze. In Excel, you can connect to the OLAP cube by creating a pivot table.
You send your report requirements to the database administrator so the OLAP cube can be created. The first report you'll create will show the total dollar amounts for each product group per region. The second report will show the quantity of each product sold, per year, for the East region.
To create the OLAP cube, the database administrator might use a product such as Microsoft SQL Server 2005 Analysis Services, Hyperion Essbase, Cognos PowerPlay, or Business Objects. In the OLAP cube, there are two types of data: measures and dimensions. The measures are numerical fields, such as quantity and price, that will be used in the Values area of a pivot table. The dimensions are the categories in which the data will be summarized, such as Region and ProductGroup. The dimensions are arranged in hierarchies of related fields. In the hardware sales data, Region, City, and StoreNo are in a hierarchy, as are ProductGroup and ProdName (see Figure 7-29). When the SaleDate field is added to the OLAP cube, a time hierarchy is automatically created, with Year, Quarter, Month, Week, and Day.
Figure 7-29. Dimensions in hierarchies
When you create a pivot table based on the OLAP cube, you'll see these hierarchies in the PivotTable Field List pane and in the pivot table layout. A pivot table that is based on an OLAP cube has some differences from a pivot table that is based on other data sources, and you'll see those as you create your hardware sales pivot table.
The database administrator e-mails to let you know that the OLAP cube is ready for you to use. The only way that the OLAP cube data is available in Excel is through a pivot table. You'll take advantage of that limitation and create a pivot table simply by opening the cube file in Excel. From the empty pivot table, you'll create your first report, showing the sales by product group and by region:
HardwareSales.cub
, and save it in one of your folders. In this example, the file was saved in the c:Data
folder.c:Data
folder, select the HardwareSales.cub
file, and click Open.Figure 7-30. PivotTable Field List pane for OLAP cube fields
When the data source is an OLAP cube, the PivotTable Field List pane looks different than it does for other data sources. At the top is a list of Values, and these are the only fields that can be placed in the Values area. These are the measures, which are the numeric fields from the source data, and they can be placed only in the Values area. In the transaction database, OrderPrice is the total price for each order, and Qty is the quantity sold in each order.
Below Values in the PivotTable Field List pane are the remaining fields, and these can be placed in the Row Labels, Column Labels, or Report Filter area. These are the dimensions, and they look different than fields from other data sources because they have plus and minus buttons that show the fields in a hierarchy. For example, click the plus sign beside SaleDate, and the field expands to show Year, Quarter, Month, and Day.
The vice president of sales has asked for a report on the total sales for each product group in each region, so you'll add the ProductGroup, Region, and Sum Of OrderPrice fields to the pivot table layout:
Note The summary function cannot be changed for Values in a pivot table that is based on an OLAP cube.
Figure 7-31. Pivot table based on an OLAP cube
HardwarePivot.xlsx
. In this example, the file was saved in the c:Data
folder.By opening the OLAP cube file in Excel, you created a pivot table and a connection in the workbook. You'll view the current connection settings to see the settings that are available when an OLAP data source is used:
Figure 7-32. Connection Properties dialog box for OLAP-based pivot table
Next, you'll create your second report to show the quantity of each product sold, per year, for the East region. You want to limit the data to show only one region, so you'll move that field to the Report Filter area:
Figure 7-33. Report filter for East region
Note For a pivot table based on an OLAP cube, the measures (numeric fields) can be added only to the Values area of the PivotTable Field List pane.
For this report, you're interested in the individual products, so you'll expand the field so all the product names are visible.
Figure 7-34. Expand Entire Field command
Tip Later, to hide the product names, you could select a ProductGroup label and then click the Collapse Entire Field command on the Ribbon.
The product names under each product group become visible, and you can see the total quantity for each product. Next, you'll add the SaleDate field to see the totals per year. Because the pivot table is based on an OLAP cube, you won't have to group the date field in order to see the totals per year. You'll examine the SaleDate field in the PivotTable Field List pane and then add it to the pivot table layout.
Figure 7-35. The expanded SaleDate hierarchy
Figure 7-36. The completed pivot table
Tip To see the quantities sold per quarter, select one of the Year labels, and click the Expand Entire Field command on the Ribbon. To hide the quarters, click the Collapse Entire Field command.
HardwarePivot.xlsx
fileIn this chapter, you created connections to data outside Excel, and you created pivot tables from that data. With this capability, you can reach beyond your Excel data and report on data that is stored in other formats.
To work with a text file, you imported the data to a worksheet and created a pivot table from the imported data. You changed the data in the text file and refreshed the pivot table to show the updated data. If necessary, you changed the security settings to allow the connection to the external data.
Next, you connected to a query in an Access database and built a pivot table from that data. Then you modified the connection to ensure that the data was refreshed frequently. You used the features in the Workbook Connections dialog box to list the locations where the selected connection was used and to go to that location. In the same workbook, you created a second pivot table, based on the existing connection.
Finally, you connected to an OLAP cube by simply opening the cube in Excel. This automatically created a connection to the OLAP cube and an empty pivot table. While working on this pivot table, you saw differences in the fields in the PivotTable Field List pane and collapsed and expanded the fields in the pivot table.
18.118.2.240