Excel: Pivot Tables & Charts

  • Updated

Pivot Tables are one of Excel's most powerful features. A Pivot Table allows you to extract the significance from a large, detailed data set.

Creating a PivotTable

  1. Click any cell within the data set to be analyzed
  2. Navigate to the Insert tab on the Ribbon
  3. In the Tables group, click the PivotTable button
  4. A Create PivotTable dialog box will appear

*NOTE: When creating a PivotTable without previously creating a Table, make sure your data has column headings or table headers, and that there are no blank rows in the data set

For recommendations on analyzing your data with a PivotTable, click the Recommended PivotTables button and select the desired PivotTable option

  • Excel automatically selects the data range for your PivotTable; to change it, click the box at the right of the Table/Range field and select the desired data range
  • The default location for the PivotTable is a New Worksheet; to change, click the appropriate button and indicate the desired location

Excel_Insert_PivotTable.png Excel_Recommended_Pivot_Tables.png

Excel_Create_PivotTable_Dialog_Box.png

Designing the PivotTable

After the PivotTable is created, the PivotTable Fields pane is displayed so fields may be arranged.

  • A Field Section for dragging fields to and from the layout section defaults to the top
  • A Layout Section for rearranging and repositioning fields defaults to the bottom
  • Values - contains the fields that determine which data is presented in the cells of the PivotTable –the values are summarized in its last column
  • Row Labels –contains the fields that determine the arrangement of data shown in the rows of the PivotTable
  • Column Labels –contains the fields that determine the arrangement of data shown in the columns of the PivotTable.
  • Report Filter - contains the fields that act as the filters for the report.
  • Under Choose fields to add to the report select and drag the fields to the desired section

The PivotTable Fields List disappears when you click outside of the PivotTable

Excel_PivotTable_Build.png

Excel_Pivot_Table.png

Analyze & Design 

Use the PivotTable Tools contextual tab on the Ribbon to Analyze and Design your PivotTable

Excel_PivotTable_Tools.png

Change Summary Calculation

By default, Excel summarizes your data by either summing or counting the items. To change the type of calculation you want to use:

  1. Click any cell in the Total column
  2. Right click and select Value Field Settings
  3. In the Value Field Settings dialog box, choose the type of calculation you want to use

*NOTE: Format numbers as desired by selecting the Number Format button

Excel_Value_Field_Settings.png

Excel_Value_Field_Settings_Dialog_Box.png

Creating a PivotChart

  • From an existing PivotTable
  1. Click anywhere in the PivotTable and go to PivotTable Tools contextual tab
  2. Under the Analyze tab, in the Tools group, select PivotChart and the type of chart desired
  3. The Column Labels become the Legend Fields
  4.  Use the PivotChart Tools contextual tab or the Chart Elements tool to format your PivotChart

Excel_PivotChart.png

Excel_PivotChart_Created.png

  • From scratch (without an existing PivotTable)
  1. Click anywhere in your data set
  2. Navigate to the Insert tab, Charts group
  3. Click the PivotChart drop down, select PivotChart, or PivotChart & PivotTable to create both simultaneously
  4. Indicate your data range and where you would like the PivotChart to appear, just as you did for a PivotTable

Excel_Creat_PivotChart.png

It is important to “refresh” a PivotTable to update changes to the original data that may have taken place since the creation of the Table.