Amal Nagm

Use a PivotTable report to summarize, analyze, explore, and present summary data. Use a PivotChart report to visualize that summary data in a PivotTable report, and to easily see comparisons, patterns, and trends. Both a PivotTable report and a PivotChart report enable you to make informed decisions about critical data in your enterprise. The following sections provide an overview of PivotTable reports and PivotChart reports.

Create a PivotTable or PivotChart report

To create a PivotTable or PivotChart report, you must connect to a data source and enter the report's location.

1. Select a cell in a range of cells, or put the insertion point inside of a Microsoft Office Excel table.

   Make sure that the range of cells has column headings.

2. Select the type of report to generate by doing one of the following:
   • To create a PivotTable report, on the Insert tab, in the Tables group, click PivotTable, and then click PivotTable.

     

     Excel displays the Create PivotTable dialog box.

   • To create a PivotTable and PivotChart report, on the Insert tab, in the Tables group, click PivotTable, and then click PivotChart.

     Excel displays the Create PivotTable with PivotChart dialog box.

3. Select a data source by doing one of the following:

   Choose the data that you want to analyze

   1. Click Select a table or range.
   2. Type the range of cells or table name reference, such as =QuarterlyProfits, in the Table/Range box.

      If you selected a cell in a range of cells or if the insertion point was in a table before you started the wizard, Excel displays the range of cells or table name reference in the Table/Range box.

      Alternatively, to select a range of cells or table, click Collapse Dialog to temporarily hide the dialog box, select the range on the worksheet, and then press Expand Dialog .

      Tip Consider using a table name reference instead of a range of cells, because rows added to a table are automatically included in the PivotTable report when you refresh the data.

      Note If the range is in another worksheet in the same workbook or another workbook, type the workbook and worksheet name by using the following syntax: ([workbookname]sheetname!range).

   Use external data

   1. Click Use an external data source.
   2. Click Choose Connection.

      Excel displays the Existing Connections dialog box.

   3. In the Show drop-down list box at the top of the dialog box, select the category of connections for which you want to choose a connection or select All Existing Connections (which is the default).
   4. Select a connection from the Select a Connection list box, and then click Open.

      Note If you choose a connection from the Connections in this Workbook category, you will be reusing or sharing an existing connection. If you choose a connection from the Connection files on the network or Connection files on this computer categories, Excel copies the connection file into the workbook as a new workbook connection, and then uses that file as the new connection for the PivotTable report.

4. Specify a location by doing one of the following:
   • To place the PivotTable report in a new worksheet starting at cell A1, click New Worksheet.
   • To place the PivotTable report in an existing worksheet, select Existing Worksheet, and then specify the first cell in the range of cells where you want to position the PivotTable report.

     Alternatively, click Collapse Dialog to temporarily hide the dialog box, select the beginning cell on the worksheet, and then press Expand Dialog .

5. Click OK.

   Excel adds an empty PivotTable report to the specified location and displays the PivotTable Field List so that you can add fields, create a layout, and customize the PivotTable report.

   If you create a PivotChart report, 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.) directly beneath the PivotChart report. A PivotChart report and its accompanying PivotTable report must always be in the same workbook.

Create a PivotChart report from an existing PivotTable report

1. Click the PivotTable report of interest.
2. On the Insert tab, in the Charts group, click a chart type.

   

   You can use any chart type except xy (scatter), bubble, or stock.

Convert a PivotChart report to a static chart

1. Find the 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.) that has the same name as the PivotChart report by doing the following:
   1. Click the PivotChart report.
   2. To find the associated PivotTable report name, on the Design tab, in the Data group, click Select Data to display the Edit Data Source dialog box, and then note the associated PivotTable name, which is the text that follows the (!) exclamation point, in the Chart data range text box and then click OK.
   3. To find the associated PivotTable report, click each PivotTable report in the workbook, and then on the Options tab, in the PivotTable group, click Options until you find the same name in the Name text box.
2. Click OK.
3. On the Options tab, in the Actions group, click Select, and then click Entire PivotTable.
4. Press DELETE.

Create a static chart from the data in a PivotTable report

This procedure creates a standard, noninteractive chart instead of a PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.).

1. Select the data in the PivotTable report that you want to use in your chart. To include 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 data in the first row and column of the report, start dragging from the lower-right corner of the data that you select.
2. On the Home tab, in the Clipboard group, click Copy .
3. Click a blank cell outside of the PivotTable report.
4. On the Home tab, in the Clipboard group, click the arrow next to Paste, and then click Paste Special.
5. Click Values, and then click OK.
6. On the Insert tab, in the Charts group, click a chart type.
   Delete a PivotTable or PivotChart report

Delete a PivotTable report

1. Click the PivotTable report.
2. On the Options tab, in the Actions group, click Select, and then click Entire PivotTable.
3. Press DELETE.

Note Deleting the 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.) for a PivotChart report creates a static chart that you can no longer change.

Delete a PivotChart report

1. Select the PivotChart report.
2. Press DELETE.

   Note Deleting the PivotChart report does not automatically delete the associated PivotTable report.