1 / 26

Excel Lesson 12 Creating PivotTables and PivotCharts

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.

Download Presentation

Excel Lesson 12 Creating PivotTables and PivotCharts

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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.

More Related