260 likes | 398 Views
Excel Lesson 12 Creating PivotTables and PivotCharts. Microsoft Office 2010 Advanced. Cable / Morrison. 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.
E N D
Excel Lesson 12Creating PivotTables and PivotCharts Microsoft Office 2010 Advanced Cable / Morrison
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
Objectives (continued) • Create a PivotChart. • Filter a PivotChart. • Modify a PivotChart. • Add a slicer to a PivotTable.
Vocabulary • child • parent • pivot • PivotChart • PivotTable • slicer 4 4
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
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.
Creating a PivotTable (continued) • PivotTable Field List dialog box and PivotTable pane
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.
Sorting and Filtering PivotTable Data (continued) • Sort & Filter Options list
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.
Modifying the PivotTable (continued) • PivotTable with two levels of Row Label detail
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.
Changing Formats and Value Calculation (continued) • Show Values As list • Allows you to change how the values are displayed and calculated
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.
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.
Updating Data and Refreshing the PivotTable (continued) • PivotTable with refreshed data
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.
Creating a PivotChart (continued) • PivotTable and PivotChart
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
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.
Modifying the PivotChart (continued) • Change Chart Type dialog box
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
Using the Slicer (continued) • Slicer with new style
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.
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.
Summary (continued) • After a PivotChart is created, it can be modified. • Adding a slicer to a PivotTable lets you filter data.