PivotChart reports have some specialized elements in addition to the series (data series: Related data points that are plotted in a chart and originate from datasheet rows or columns. Each data series in a chart has a unique color or pattern. You can plot one or more data series in a chart. Pie charts have only one data series.), categories, data markers (data marker: A bar, area, dot, slice, or other symbol in a chart that represents a single data point or value that originates from a worksheet cell. Related data markers in a chart constitute a data series.), and axes (axis: A line bordering the chart plot area used as a frame of reference for measurement. The y axis is usually the vertical axis and contains data. The x-axis is usually the horizontal axis and contains categories.) of regular Microsoft Excel charts.

Page field

Data field

Series field

Items

Category field

Page field    A field that you use to filter data by specific items. In the example, the Region page field displays data for all regions. To display data for a single region, you can click the drop-down arrow next to (All) and select the region.

Data field    A field from the underlying 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.) that provides values to compare or measure. In the example, Sum of Sales is a data field that summarizes quarterly sales in each region for each sport. The first category data marker (Qtr1) reaches nearly 250 on the value (y) axis. This amount is the sum of Tennis, Safari, and Golf sales in the first quarter. Depending on the source data you use for the report, you can change the summary function (summary function: A type of calculation that combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.) to Average, Count, Product, or another calculation.

Series field    A field that you assign to a series orientation in a PivotChart report. The items in the field provide the individual data series. In the example, Sport is a series field with three items: Tennis, Safari, and Golf.

Item    Items represent the unique entries in a field, and appear in the drop-down lists for page fields, category fields, and series fields. In the example, Qtr1, Qtr2, Qtr3, and Qtr4 are items in the Quarter category field, while Tennis, Safari, and Golf are items in the Sport series field.

Category field    A field from the source data that is assigned to a category orientation in a PivotChart report. A category field provides the individual categories for which data points are charted. In the example, Quarter is a category field.

A PivotChart report always has 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.). Both reports have fields that correspond to each other. When you change the position of a field in one report, the corresponding field in the other report also moves.

The following example shows the correspondence between the fields in each type of report:

Page fields

Data fields

Row field, corresponds to category field

Column field, corresponds to series field

When you create a PivotChart report from a PivotTable report, the layout of the PivotChart report— that is, the position of its fields— is determined initially by the layout of the PivotTable report. When you create the PivotChart report first, you determine the chart layout by dragging fields from the PivotTable Field List window to the drop areas (drop area: An area in a PivotTable or PivotChart report where you can drop fields from the Field List dialog box to display the data in the field. The labels on each drop area indicate the types of fields you can create in the report.) on the chart sheet (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a PivotTable report.). Microsoft Excel automatically creates an associated PivotTable report that uses a corresponding layout.

With regular charts, you create one chart for each view of the data that you want to see. With PivotChart reports, you can create a single chart and view the data in different ways by changing the report layout or the detail displayed.

If you are familiar with regular charts, you will find that most operations are the same in PivotChart reports. However, there are some differences:

Chart types    The default chart type for a regular chart is a clustered column chart, which compares values across categories. The default chart type for a PivotChart report is a stacked column chart, which compares the contribution of each value to a total across categories. You can change a PivotChart report to any type except xy (scatter), stock, or bubble.

Chart location    Regular charts are embedded (embedded chart: A chart that is placed on a worksheet rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print a chart or a PivotChart report with its source data or other information in a worksheet.) on worksheets by default. PivotChart reports are created on chart sheets (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a PivotTable report.) by default. Once created, you can relocate a PivotChart report to a worksheet.

Creating the chart    To create a regular chart in Microsoft Excel, you use the Chart Wizard. To create a PivotChart report, you can use the Chart Wizard, or you can use the PivotTable and PivotChart Wizard if you already have a PivotTable report to serve as the source data for the PivotChart report.

Source data    Regular charts are linked directly to worksheet cells. PivotChart reports can be based on several different types of data, including 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.) and databases, multiple data ranges that you want to consolidate, and external sources, such as Microsoft Access databases and 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.) databases.

Chart elements    PivotChart reports contain the same elements as regular charts but also contain fields and items that can be added to, rotated, or removed to display different views of your data. Categories, series, and data in regular charts are category fields, series fields, and data fields in PivotChart reports. PivotChart reports can also contain page fields. Each of these fields contains items, which in regular charts are displayed as category labels or series names in legends (legend: A box that identifies the patterns or colors that are assigned to the data series or categories in a chart.). You can hide the field buttons (field button: Button that identifies a field in a PivotTable or PivotChart report. You can drag the field buttons to change the layout of the report, or click the arrows next to the buttons to change the level of detail displayed in the report.) and drop area (drop area: An area in a PivotTable or PivotChart report where you can drop fields from the Field List dialog box to display the data in the field. The labels on each drop area indicate the types of fields you can create in the report.) outlines for printing or publishing to the Web.

Formatting    Some types of formatting are lost after you change the layout or 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.) a PivotChart report. These types of formatting include trendlines (trendline: A graphic representation of trends in data series, such as a line sloping upward to represent increased sales over a period of months. Trendlines are used for the study of problems of prediction, also called regression analysis.) and error bars (error bars: Usually used in statistical or scientific data, error bars show potential error or degree of uncertainty relative to each data marker in a series.), changes to data labels (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell.), and changes to data series. Regular charts do not lose this formatting once applied.

Moving or resizing items    In a PivotChart report, you cannot move or resize the plot area (plot area: In a 2-D chart, the area bounded by the axes, including all data series. In a 3-D chart, the area bounded by the axes, including the data series, category names, tick-mark labels, and axis titles.), legend, chart titles, or axis titles, though you can select one of several preset positions for the legend and you can change the font size of titles. In a regular chart, you can move and resize all of these elements.

Starting with a PivotTable report    Make sure your PivotTable report has at least one row field (row field: A field that's assigned a row orientation in a PivotTable report. Items associated with a row field are displayed as row labels.), to become the category field in the PivotChart report, and a column field (column field: A field that's assigned a column orientation in a PivotTable report. Items associated with a column field are displayed as column labels.) to become the series field. If your PivotTable report is in indented format, move at least one field to the column area before you create the chart.

Starting from scratch    In the PivotTable and PivotChart Wizard, you specify the type of 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.) you want to use, and set options for how the data is used. You then lay out the PivotChart report in a manner similar to a PivotTable report. If your workbook doesn't contain a PivotTable report, Microsoft Excel creates one when you create the PivotChart report. When you change the PivotChart report, its 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.) changes, and vice versa.

Customizing the report    You then change the chart type and other options— such as the titles (titles in charts: Descriptive text that is automatically aligned to an axis or centered at the top of a chart.), the legend (legend: A box that identifies the patterns or colors that are assigned to the data series or categories in a chart.) placement, the data labels (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell.), the chart location, and so on— by using the Chart Wizard and commands on the Chart menu.

When to use page fields    Using page fields is a convenient way to summarize and quickly focus on a subset of data without having to modify your series and category information. For instance, if you're giving a presentation, you can click (All) in the Year page field to show sales for all years, and then focus on specific years by clicking one year at a time. Each page of your chart has the same category and series layout for different years, so the data for each year can be easily compared. Also, by allowing you to retrieve one page at a time from a large set of data, page fields can conserve memory when your chart uses external source data.