You can use data from an Excel worksheet as the basis for a report. The data should be in list format, with column labels in the first row, the rest of the rows having similar items in the same column, and no blank rows or columns within the range of data. Excel uses your column labels for the field names in the report.
Selecting the data automatically Click a cell in the list or in the row below the list before running the PivotTable and PivotChart Wizard. The wizard fills in a reference to the entire list for you.
Using a named range To make the report easier to update, name the source range and use the name when you create the report. If the named range expands to include more data, you can refresh the report to include the new data.
Using filtered data Excel ignores any filters you have applied to a list with the commands from the Filter submenu of the Data menu. To create a report that includes only the filtered data, use the Advanced Filter command to extract the data you want to another worksheet location, and then base the report on the extracted range.
Including totals Excel automatically creates subtotals and grand totals in a PivotTable report. If the source list contains automatic subtotals and grand totals created with the Subtotals command on the Data menu, use that command to remove them before you create the report.
To summarize and analyze data from outside Microsoft Excel, such as your company's sales records in a database, you can retrieve data from external sources including databases, text files, and sites on the Internet.
Prerequisites for retrieving data To retrieve data from a Web site, you need to be connected to your intranet or the Internet.
To retrieve other types of external data, you must install Microsoft Query and the appropriate open database connectivity (ODBC) drivers or data source drivers. Query provides drivers for many types of external data, including Microsoft SQL Server, Microsoft Access, and text file databases.
Using the PivotTable and PivotChart Wizard You can usually retrieve the data you want from within the wizard. From the wizard, you can create data sources, run saved queries, and create new queries.
In step 2 of the wizard, when you get the external data, you're launching Microsoft Query. You have access to all of Query's features: you can select an existing data source, query, or OLAP cube data source, or define a new one. You can follow the Query Wizard steps to select the data you want, and then return to the PivotTable and PivotChart Wizard in the last step of the Query Wizard, or customize your query directly in Microsoft Query. When you finish working directly in Query and return the data to Excel, you're returned to the PivotTable and PivotChart Wizard.
Using other Excel methods In the following situations, retrieve the data into Excel first, before you create your PivotTable or PivotChart report:
- Web queries To use a Web query to get data from the Internet, create or run the web query, and then base the report on the worksheet range from the query.
- Parameter queries To use a parameter query to get the data, create or run the parameter query, and then base the report on the worksheet range that the query retrieves.
- Report templates and query files To open a query (.dqy) file, or use a report template (.xlt) that does not already include a PivotTable report, first open the query file or template in Excel. Base your report on the worksheet range retrieved by the query or template.
- Office Data Connection files To use an .odc file to get data from a table in an external database, open the .odc file in Excel. You then have two choices: you can import the data directly into a PivotTable report, or you can import the data into a worksheet and base your report on the worksheet range.
OLAP source data When you retrieve source data from an OLAP database or cube file, the data can be returned to Excel only as a PivotTable report. You can then create PivotChart reports from this PivotTable report. You use Microsoft Query to set up a data source to access the database.
When you have several Microsoft Excel lists with similar categories of data and you want to summarize the data from the lists together on one worksheet, one option is to use a PivotTable or PivotChart report. Excel also provides other ways to consolidate, including formulas with 3D references and the Consolidate command on the Data menu, that work with lists in all kinds of formats and layouts.
Setting up the source data Each range of data should be in list format, with column labels in the first row, row labels in the first column, the rest of the rows having similar items in the same row and column, and no blank rows or columns within the range. The lists or worksheets must have matching row and column names for items that you want to summarize together. Do not include any total rows or total columns from the source data when you specify the data for the report.
Using named ranges To make the report easier to update, name each source range and use the names when you create the PivotTable or PivotChart report. If a named range expands to include more data, you can refresh the report to include the new data.
Page fields in consolidations A consolidation uses custom page fields that contain items representing one or more of the source ranges. For example, if you're consolidating budget data from the Marketing, Sales, and Manufacturing departments, a page field could include one item to display the data for each department plus an item to show the combined data.
After you click Multiple consolidation ranges in step 1 of the PivotTable and PivotChart Wizard, you can choose the kind of page fields you want.
- A single page field To include one page field with an item for each source range plus an item that consolidates all of the ranges, click Create a single page field for me in step 2a of the wizard.
- Multiple page fields You can create as many as four page fields and assign your own item names for each source range, allowing you to create partial or full consolidations. For example, one page field could consolidate Marketing and Sales apart from Manufacturing, and another page field could consolidate all three departments. Alternatively, you can simply combine the data from all the ranges, creating a consolidation that doesn't have page fields. Click I will create the page fields in step 2a of the wizard, and then use step 2b to create the page fields and assign the ranges to them as items.
Each time you create a new PivotTable or PivotChart report, Microsoft Excel stores a copy of the data for the report in memory, and saves this storage area as part of the workbook file. Thus each new report requires additional memory and disk space. However, when you use an existing PivotTable report as the source for a new report in the same workbook, both reports share the same copy of the data. Because you reuse the same storage area, the size of the workbook file is reduced and less data is kept in memory.
Location requirements To use a PivotTable report as the source for another report, both reports must be in the same workbook. If the source PivotTable report is in a different workbook, copy the source report to the workbook where you want the new report to appear. PivotTable and PivotChart reports in different workbooks are separate, each with their own copy of the data in memory and in the workbook files.
Page field settings The source PivotTable report cannot contain any page fields that are set to query for external data as you select each item. Reports with this setting don't appear in step 2 of the wizard. To check the setting, double-click each page field, click Advanced, and make sure Retrieve external data for all page field items is selected.
Changes affect both reports When you refresh the data in the new report, Excel also updates the data in the source report, and vice versa. When you group or ungroup items in one report, both are affected. When you create calculated fields or calculated items in one report, both reports are affected.
PivotChart reports You can base a new PivotTable or PivotChart report on another PivotTable report, but not directly on another PivotChart report. However, Excel creates an associated PivotTable report from the same data whenever you create a PivotChart report, so you can base a new report on the associated report.
Changes to a PivotChart report affect the associated PivotTable report, and vice versa. If you want to be able to change the layout or display different data without these changes affecting both reports, create a new PivotTable report based on the same source data as the PivotChart report, rather than basing it on the associated PivotTable report.
PivotTable lists from Web pages You can export a PivotTable list from your Web browser to Excel and view and save the list as a PivotTable report. The new PivotTable report and the PivotTable list both use the same source data, but no link is maintained between the list and the report.
Changing an existing report's source data
Changes in the source data can result in different data being available for analysis. You can update a PivotTable or PivotChart report with new data that falls within your original source data specification by refreshing the report.
To include additional data or different data, you can redefine the source data for the report. If the data is substantially different with many new or additional fields, it may be easier to create a new report.
Displaying new data brought in by refresh Refreshing a report can also change the data available for display. For reports based on worksheet lists, Microsoft Excel retrieves new fields within the source range or named range that you specified. For reports based on external data, Excel retrieves new data that meets the criteria for the underlying query or becomes available in an OLAP cube. You can view any new fields in the field list and add them to the report.
Including different Excel source data For reports based on Excel data, you can run the PivotTable and PivotChart wizard, return to step 2, and redefine the source range to include different data.
Querying for different external data When new fields become available in an external database, or you want to select different records for the report, you can run the PivotTable and PivotChart wizard, return to step 2, and use the Get Data button to start Microsoft Query. In Query, you can change the query to select different data.
Changing OLAP cubes that you create Reports based on OLAP data always have access to all data in the cube. If you created an offline cube file containing a subset of the data in a server cube, you can use the Offline OLAP command on the PivotTable menu to edit your cube file so that it contains different data from the server. If you used the OLAP Cube Wizard in Microsoft Query to define a cube from another database, you can't add more fields from the original database, but you can use the wizard to delete fields or reorganize your cube. You can update both types of cubes with the most recent data from the server cube or the original database by refreshing a report based on the cube.