You can use data from an Excel worksheet as the basis for a report. The data should be in list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) 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 (field: In a PivotTable or PivotChart report, a category of data that's derived from a field in the source data. PivotTable reports have row, column, page, and data fields. PivotChart reports have series, category, page, and data fields.) 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 (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) the source range and use the name when you create the report. If the named range expands to include more data, you can refresh (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) the report to include the new data.

Using filtered data    Excel ignores any filters (filter: To display only the rows in a list that satisfy the conditions you specify. You use the AutoFilter command to display rows that match one or more specific values, calculated values, or conditions.) 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 (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.), 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) (Open Database Connectivity (ODBC): A standard method of sharing data between databases and programs. ODBC drivers use the standard Structured Query Language (SQL) to gain access to external data.) drivers or data source drivers (data source driver: A program file used to connect to a specific database. Each database program or management system requires a different driver.). 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 (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.), run saved queries (query: In Query or Access, a means of finding the records that answer a particular question you ask about the data stored in a database.), 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 (Web query: A query that retrieves data stored on your intranet or the Internet.) 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 (parameter query: A type of query that, when you run it, prompts for values (criteria) to use to select the records for the result set so that the same query can be used to retrieve different result sets.) 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 (report template: An Excel template (.xlt file) that includes one or more queries or PivotTable reports that are based on external data. When you save a report template, Excel saves the query definition but doesn't store the queried data in the 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 (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) database or cube (cube: An OLAP data structure. A cube contains dimensions, like Country/Region/City, and data fields, like Sales Amount. Dimensions organize types of data into hierarchies with levels of detail, and data fields measure quantities.) 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 (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.) to access the database.

When you have several Microsoft Excel lists (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) 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 (3-D reference: A reference to a range that spans two or more worksheets in a workbook.) 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 (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) 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 (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) the report to include the new data.

Page fields in consolidations    A consolidation uses custom page fields (page field: A field that's assigned to a page orientation in a PivotTable or PivotChart report. You can either display a summary of all items in a page field, or display one item at a time, which filters out the data for all other items.) that contain items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) 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 (page field: A field that's assigned to a page orientation in a PivotTable or PivotChart report. You can either display a summary of all items in a page field, or display one item at a time, which filters out the data for all other items.), click Advanced, and make sure Retrieve external data for all page field items is selected.

Changes affect both reports    When you refresh (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) 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 (calculated field: A field in a PivotTable report or PivotChart report that uses a formula you create. Calculated fields can perform calculations by using the contents of other fields in the PivotTable report or PivotChart report.) or calculated items (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.) 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 (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.) 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 (PivotTable list: A Microsoft Office Web Component that allows you to create a structure similar to an Excel PivotTable report. Users can view the PivotTable list in a Web browser and change its layout in a manner similar to an Excel PivotTable report.) 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.

Changes in the source data (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.) 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 (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) 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 (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) range that you specified. For reports based on external data, Excel retrieves new data that meets the criteria for the underlying query (query: In Query or Access, a means of finding the records that answer a particular question you ask about the data stored in a database.) or becomes available in an OLAP cube (cube: An OLAP data structure. A cube contains dimensions, like Country/Region/City, and data fields, like Sales Amount. Dimensions organize types of data into hierarchies with levels of detail, and data fields measure quantities.). 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 (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.) 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.