Excel Lesson 12 Creating PivotTables and PivotCharts

1. Excel Lesson 12Creating PivotTables and PivotCharts Microsoft Office 2010 Advanced Cable / Morrison

2. Objectives • Create a PivotTable. • Sort and filter PivotTable data. • Modify a PivotTable. • Change value calculations and formats. • Update data and refresh the PivotTable. 2 2

3. Objectives (continued) • Create a PivotChart. • Filter a PivotChart. • Modify a PivotChart. • Add a slicer to a PivotTable.

4. Vocabulary • child • parent • pivot • PivotChart • PivotTable • slicer 4 4

5. Introduction • A PivotTable lets you rearrange worksheet data so you can analyze it in a variety of ways. • A PivotChart can be created to graphically display the PivotTable. • The slicer includes column headings that you click to filter data in a PivotTable. • Looks like a free-floating note pad

6. Creating a PivotTable • The data in a PivotTablecan be rearranged and summarized in different ways. • Can be viewed from various perspectives • APivotTable lets you pivotor rearrange the data. • When you create a PivotTable, you do not need to use all of the data in the worksheet.

7. Creating a PivotTable (continued) • PivotTable Field List dialog box and PivotTable pane

8. Sorting and Filtering PivotTable Data • The sort and filter options are displayed when you click: • The Column Labels down arrow or • The Row Labels down arrow • You can select a sort option to sort the data in ascending or descending order.

9. Sorting and Filtering PivotTable Data (continued) • Sort & Filter Options list

10. Modifying the PivotTable • After you create the PivotTable, you can continue to rearrange the data. • To change the location of rows and columns: • Redisplay the PivotTable Field List dialog box. • Shift the order of the fieldsin the dialog box. • You can add additional levels of detail. • When you add levels of fields, Outline buttons appear in the PivotTable.

11. Modifying the PivotTable (continued) • PivotTable with two levels of Row Label detail

12. Changing Formats and Value Calculation • You can change the format of the PivotTable data. • For example: display numbers as dollar amounts, percentages, or add commas, etc. • You can calculate the data differently.

13. Changing Formats and Value Calculation (continued) • Show Values As list • Allows you to change how the values are displayed and calculated

14. Changing Formats and Value Calculation (continued) • You can create your own calculation using any of the options that end with an ellipsis (…) • When there is more than one level of detail: • The main row heading is referred to as the parent. • The level of detail below this level is the child.

15. Updating Data and Refreshing the PivotTable • To update PivotTable data to reflect the changes to the worksheet data: • Click the Refresh button, located in the Data group on the Options tab.

16. Updating Data and Refreshing the PivotTable (continued) • PivotTable with refreshed data

17. Creating a PivotChart • A PivotChart is a visual representation of the data in a PivotTable. • When you create a PivotChart with worksheet data, a PivotTable is created at the same time. • The PivotTable and PivotChart are linked.

18. Creating a PivotChart (continued) • PivotTable and PivotChart

19. Filtering the PivotChart • When you filter the PivotChart, the same filtered data appears in the PivotTable. • Chart filters are located in the chart itself. Filtered PivotChart and PivotTable

20. Modifying the PivotChart • PivotCharts can be modified just like ordinary Excel charts. • For example: you can change the chart type and add chart styles. • When a PivotChart is selected, additional tabs appear on the Ribbon • These tabs are for making changes to the PivotChart.

21. Modifying the PivotChart (continued) • Change Chart Type dialog box

22. Using the Slicer • The sliceris a visual control that looks like a note pad. • Clicking selections on the slicer lets you filter your data in a PivotTable. Insert Slicers dialog box

23. Using the Slicer (continued) • Slicer with new style

24. Summary In this lesson, you learned: • You can create PivotTables based on worksheet data. • You can sort and filter PivotTable data. • After a PivotTable is created, it can be modified. • You can change formats and value calculations in a PivotTable.

25. Summary (continued) • After you update worksheet data that was used to create a PivotTable, you need to refresh the PivotTable to display the changes. • PivotCharts can be created with PivotTables. • PivotCharts can be filtered.

26. Summary (continued) • After a PivotChart is created, it can be modified. • Adding a slicer to a PivotTable lets you filter data.