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 analyse
    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
    3. Click Use an external data source.
    4. Click Choose Connection.

      Excel displays the Existing Connections dialog box.
    5. 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).
    6. 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.

      For more information, see Manage connections to data in a workbook.
  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 customise the PivotTable report.

    For more information, see Create and change the layout of fields in a 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.




Share this page with your friends