Try the Excel Course for Free!

Create a PivotChart in Excel- Instructions and Video

/ / Excel for Office 365, Latest, Microsoft, Office 365

Create a PivotChart in Excel: Video Lesson

            This video lesson, titled “Excel for Microsoft 365 Tutorial: How to Create a PivotChart in Excel,” shows how to create a PivotChart in Excel. This video lesson is from our complete Excel tutorial, titled Mastering Excel Made Easy™.

Overview of How to Create a PivotChart in Excel:

About PivotCharts

            You can create a PivotChart in Excel to show data the same way a PivotTable does, but graphically. You can create a PivotChart in Excel independently from or along with an associated PivotTable in Excel. You can also add a PivotChart to an existing PivotTable if you didn’t create one when you initially created the PivotTable. After adding a PivotChart, you can then manipulate it the same way you manipulate a PivotTable.

How to Select the PivotChart Location or Data Source Location

            Like PivotTables, first select a cell in your workbook to help Excel set either the data source location or the PivotChart location before inserting the PivotChart. If you intend to use a single Excel table or cell range as the data source, then either click into the table to use as the data source or click into the cell where you want to insert the upper-left corner of the PivotChart or PivotTable and PivotChart.

            Alternatively, if you intend to use an external data source or tables added to Excel’s data model, instead select the cell where you want to insert the upper-left corner of the PivotChart or PivotTable and PivotChart.

How to Create the PivotChart

            To then create a PivotChart in Excel, click the “PivotChart” drop-down button in the “Charts” button group on the “Insert” tab of the Ribbon. To insert only a PivotChart, then select the “PivotChart” choice. Alternatively, to insert a PivotTable and PivotChart, select the “PivotChart & PivotTable” choice. Excel then opens the “Create PivotChart” dialog box if only inserting a PivotChart or the “Create PivotTable” dialog box if inserting both objects. The choices in both dialog boxes are the same.

How to Select a Table or Range as the PivotChart Data Source

            First, select the data source for the PivotChart from the “Choose the data that you want to analyze” section. If you initially selected a cell in a table, then the “Select a table or range” option appears selected, and the table name or cell range reference appears in the “Table/Range” field.

            Alternatively, to select a single table or cell range as the PivotChart’s data source, if desired, select the “Select a table or cell range” option. Then click the “Collapse/Expand Dialog Box” button at the right end of the “Table/Range” field to collapse it. Then click and drag over the table or cell range to use. Then click the same button again to expand the dialog box.

How to Select an External Connection or the Data Model as the PivotChart Data Source

            Alternatively, to select an external data connection as the PivotChart’s data source, select the “Use an external data source” option. To then choose the external data connection to use, click the “Choose Connection…” button to open the “Existing Connections” dialog box. The “Connections” tab of the “Existing Connections” dialog box shows any available, external data connections for this Excel workbook, your network, or your computer.

            To show all the data connections in your workbook, network, and computer in three separate sections, use the “Show” drop-down at the top of the “Connections” tab in the “Existing Connections” dialog box to select the “All Connections” choice. Then select the external data connection to use for your PivotChart from the connections shown.

            Alternatively, to select to use the tables in the workbook’s data model, or a single Excel table from any opened Excel workbook as the PivotChart’s data source, click the “Tables” tab at the top of the “Existing Connections” dialog box. Then select either an individual table from any opened Excel workbook or select the “Tables in Workbook Data Model” choice to select all the tables in the data model. After making your data choice selection on either tab, then click the “Open” button to return to the “Create PivotChart” or “Create PivotTable” dialog box.

            Alternatively, to select the workbook’s data model as the data source for the PivotChart, select the “Use this workbook’s Data Model” option.

How to Select the PivotChart Location

            After selecting the data source for the PivotChart or PivotTable and PivotChart, then select its location within the “Choose where you want the PivotChart to be placed” section. If you selected the cell into which to insert the PivotChart when you started this process, the “Existing Worksheet” option should appear selected, and the selected cell reference should appear in the “Location” field.

            To instead place the PivotChart into a new Excel worksheet, select the “New Worksheet” option button. Alternatively, to select an existing worksheet cell, if needed, select the “Existing Worksheet” option button. Then click the adjacent “Collapse/Expand Dialog Box” button at the right end of the “Location” field to collapse the dialog box down to a single line. Then click the worksheet cell to set as the upper-left corner of the PivotChart. Then click the same button again to expand the dialog box, when finished.

Final Steps to Create a PivotChart in Excel

            If desired, depending on your data source option selection, to also add the selected data to the Excel workbook’s data model, check the “Add this data to the Data Model” checkbox. When finished, click the “OK” button to create the PivotChart or PivotTable and PivotChart. Excel also shows the “PivotChart Fields” task pane at the right side of the workbook window.

Changing the Data Shown in a PivotChart or PivotTable and Linked PivotChart

            Then add fields from your data source, which appear in the large “Choose fields to add to report” list towards the top of this task pane, to the quadrants at the bottom of the task pane, as desired. You use this task pane to add and change the data fields in the PivotChart. It works the same way as the “PivotTable Fields” task pane does, as we covered in the previous lesson, titled “Excel for Microsoft 365 Tutorial: How to Manually Create a PivotTable in Excel.”

            If you inserted both a PivotTable and a PivotChart, you can click to select either object in the worksheet to switch between the PivotTable Fields task pane and the PivotChart Fields task pane. However, note that data added or changed within one task pane is reflected in the other, as they are linked. If you select the PivotChart, you can add data fields into the “Filters,” “Legend (Series),” “Axis (Categories),” or “Values” quadrants in the “PivotChart Fields” task pane.

A picture showing a PivotChart in Excel and its PivotChart Fields task pane, which appear after you create a PivotChart in Excel.

A picture showing a PivotChart in Excel and its PivotChart Fields task pane, which appear after you create a PivotChart in Excel.

How to Add a PivotChart to an Existing PivotTable in Excel

            Separately, to add a PivotChart to an existing PivotTable in Excel, click into any cell within the PivotTable to which to add an accompanying PivotChart. Then directly click the “PivotChart” button in the “Charts” button group on the “Insert” tab of the Ribbon to open the “Insert Chart” dialog box.

            In this dialog box, select the chart type from the list at the left side of the dialog box and then select the specific chart subtype to use for your PivotChart at the top of the right side of the dialog box. Then click the “OK” button to insert the selected type of PivotChart into the worksheet. Then, depending on which object is selected, use either the “PivotTable Fields” or “PivotChart Fields” task pane to manipulate the PivotChart and its associated PivotTable.

How to Create a PivotChart in Excel: Instructions

  1. First select a cell in your workbook to help Excel set either the data source location or the PivotChart location before inserting the PivotChart.
  2. If you intend to use a single Excel table or cell range as the data source, then either click into the table to use as the data source or click into the cell where you want to insert the upper-left corner of the PivotChart or PivotTable and PivotChart.
  3. Alternatively, if you intend to use an external data source or tables added to Excel’s data model, instead select the cell where you want to insert the upper-left corner of the PivotChart or PivotTable and PivotChart.
  4. To then create a PivotChart in Excel, click the “PivotChart” drop-down button in the “Charts” button group on the “Insert” tab of the Ribbon.
  5. To insert only a PivotChart, then select the “PivotChart” choice.
  6. Alternatively, to insert a PivotTable and PivotChart, select the “PivotChart & PivotTable” choice.
  7. Excel then opens the “Create PivotChart” dialog box if only inserting a PivotChart or the “Create PivotTable” dialog box if inserting both objects. The choices in both dialog boxes are the same.
  8. First, select the data source for the PivotChart from the “Choose the data that you want to analyze” section.
  9. If you initially selected a cell in a table, then the “Select a table or range” option appears selected, and the table name or cell range reference appears in the “Table/Range” field.
  10. Alternatively, to select a single table or cell range as the PivotChart’s data source, if desired, select the “Select a table or cell range” option.
  11. Then click the “Collapse/Expand Dialog Box” button at the right end of the “Table/Range” field to collapse it, click and drag over the table or cell range to use, and then click the same button again to expand the dialog box.
  12. Alternatively, to select an external data connection as the PivotChart’s data source, select the “Use an external data source” option.
  13. To then choose the external data connection to use, click the “Choose Connection…” button to open the “Existing Connections” dialog box.
  14. The “Connections” tab of the “Existing Connections” dialog box shows any available, external data connections for this Excel workbook, your network, or your computer.
  15. To show all the data connections in your workbook, network, and computer in three separate sections, use the “Show” drop-down at the top of the “Connections” tab in the “Existing Connections” dialog box to select the “All Connections” choice.
  16. Then select the external data connection to use for your PivotChart from the connections shown.
  17. Alternatively, to select to use the tables in the workbook’s data model, or a single Excel table from any opened Excel workbook as the PivotChart’s data source, click the “Tables” tab at the top of the “Existing Connections” dialog box.
  18. Then select either an individual table from any opened Excel workbook or select the “Tables in Workbook Data Model” choice to select all the tables in the data model.
  19. After making your data choice selection on either tab, then click the “Open” button to return to the “Create PivotChart” or “Create PivotTable” dialog box.
  20. Alternatively, to select the workbook’s data model as the data source for the PivotChart, select the “Use this workbook’s Data Model” option.
  21. After selecting the data source for the PivotChart or PivotTable and PivotChart, then select its location within the “Choose where you want the PivotChart to be placed” section.
  22. If you selected the cell into which to insert the PivotChart when you started this process, the “Existing Worksheet” option should appear selected, and the selected cell reference should appear in the “Location” field.
  23. To instead place the PivotChart into a new Excel worksheet, select the “New Worksheet” option.
  24. Alternatively, to select an existing worksheet cell, if needed, select the “Existing Worksheet” option.
  25. Then click the adjacent “Collapse/Expand Dialog Box” button at the right end of the “Location” field to collapse the dialog box, click the worksheet cell to set as the upper-left corner of the PivotChart, and then click the same button again to expand the dialog box again.
  26. If desired, depending on your data source option selection, to also add the selected data to the Excel workbook’s data model, check the “Add this data to the Data Model” checkbox.
  27. When finished, click the “OK” button to create the PivotChart or PivotTable and PivotChart.
  28. Excel also shows the “PivotChart Fields” task pane at the right side of the workbook window.
  29. Then add fields from your data source, which appear in the large “Choose fields to add to report” list towards the top of this task pane, to the quadrants at the bottom of the task pane, as desired.
  30. Use this task pane to add and change the PivotChart’s data fields. It works the same as the “PivotTable Fields” task pane, as covered in the previous lesson, titled “Manually Creating a PivotTable.”
  31. If you inserted both a PivotTable and a PivotChart, you can click to select either object in the worksheet to switch between the PivotTable Fields task pane and the PivotChart Fields task pane. However, data added or changed within one task pane is reflected in the other, as they are linked.
  32. If you select the PivotChart, you can add data fields into the “Filters,” “Legend (Series),” “Axis (Categories),” or “Values” quadrants in the “PivotChart Fields” task pane.
TOP