PivotTables and Pivot Charts Reports Computer Specialist Program
PivotTables and Pivot Chart Reports
The purpose of this session is to introduce learners to the key concepts and skills surrounding; • • • • Creating a PivotTable Customizing/Editing the PivotTable Creating a Pivot Chart Report Customizing/Editing the Pivot Chart Report
Personal Notes and Application:
Page 1 of 5
Created by Tyler P. Krimmel
PivotTables and Pivot Charts Reports Computer Specialist Program
Creating a PivotTable A PivotTable is an interactive table that quickly analyzes and summarizes large amounts of data. A PivotTable is generally created based on the contents of a list or database. Example:
1 2 3 4 5 6 7 8 9 10 A Name Alton, G Bridget, R Connell, R Ferrell, J Smith, E Johns, T Johnson, M Richards, A Potty, H B Qtr. 1st 1st 2nd 1st 2nd 1st 2nd 1st 2nd C Region North South South North North South North South South D Sales 320000 875000 85000 550000 1250000 165000 380500 546000 73500 E F
Based on the information collected in this worksheet, create a PivotTable using the following steps: 1. Select the range of cells that you wish to summarize in a Pivot Table (cells A1:D10) 2. Open the Data menu 3. Select the option PivotTable and Pivot Chart Report… 4. The PivotTable and Pivot Chart Wizard dialogue box is displayed, complete the following steps to create a Pivot Table: • • • 1 of 3 – Select the option Microsoft Excel List or Database. Select create PivotTable, then Next 2 of 3 – The cell range to be included (A1:D10). Select Next 3 of 3 – The default option is to place PivotTable onto a new worksheet, select Finish
Page 2 of 5
Created by Tyler P. Krimmel
PivotTables and Pivot Charts Reports Computer Specialist Program
The PivotTable will now appear in a new worksheet. It will include a Heading Field, a Column Field, a Row Field, and a Data Entry Field. The PivotTable Toolbar and PivotTable Field List will allow you to easily insert and then “pivot” the worksheet headings and their information to quickly and easily summarize the information from the original list.
From the PivotTable Field List, you can drag and drop the item headings directly to the PivotTable to be summarized. Each item heading represents the section data from the original list. 5. 6. 7. 8. Drag Name list to Drop Page Fields Here section Drag Qtr. list to Drop Column Fields Here section Drag Region list to Drop Row Fields Here section Drag Sales list to Drop Data Items Here section
The data should now be summarized within the PivotTable. You can now easily change the way that your data is summarized (Column, Row, or Heading) and then filter the information that you require using the filter drop arrows located at the end of each title heading.
Page 3 of 5
Created by Tyler P. Krimmel
PivotTables and Pivot Charts Reports Computer Specialist Program
Editing and Formatting the PivotTable To remove headings from the PivotTable, simply select the list heading title and drag away from the PivotTable. When the red coloured X appears, simply release, and the field will no longer be included within the table. Notice that the QTR. Heading located in the Pivot Table Field List is no longer bolded, that is the indication as to which fields are not analyzed within this table
Becomes…
If you wish to change the visual appearance of the PivotTable, use the Format Report option; 1. Select the Format Report button on the PivotTable toolbar 2. Select a format style and press OK
Page 4 of 5
Created by Tyler P. Krimmel
PivotTables and Pivot Charts Reports Computer Specialist Program
Creating and Editing a Pivot Chart Report If you wish to create a chart visually summarizing the content of the PivotTable, use the Chart Wizard option; 1. Select the Chart Wizard button on the PivotTable toolbar 2. The chart should now appear as a new tab, titled: Chart1
3. Use the Chart toolbar to affect the charts appearance as desired (e.g., change the chart type)
Page 5 of 5
Created by Tyler P. Krimmel