Microsoft Excel 2007 - Illustrated

1. Microsoft Excel 2007 -Illustrated Analyzing Data with Pivot Tables

2. Plan and design a PivotTable report Create a PivotTable report Change a PivotTable’s summary function and design Filter and sort PivotTable data Objectives Microsoft Office Excel 2007 - Illustrated

3. Update a PivotTable report Change the structure and format of a PivotTable report Create a PivotChart report Use the GETPIVOTDATA function Objectives Microsoft Office Excel 2007 - Illustrated

4. Unit Introduction Use the PivotTable feature to summarize data in a worksheet, then list and display that data in a table format The interactive quality of a PivotTable allows you to freely rearrange, or “pivot,” parts of the table structure around the data Summarize data values within the table Microsoft Office Excel 2007 - Illustrated

5. Planning and Designing a PivotTable Report Use the following guidelines to create a PivotTable: Review the source data Determine the purpose of the PivotTable and write down the names of the fields you want to include Determine which field contains the data you want to summarize and which summary function you want to use Decide how you want to arrange the data Determine the location of the PivotTable Microsoft Office Excel 2007 - Illustrated

6. Planning and Designing a PivotTable Report (cont.) Column fields Row fields Data summary field Microsoft Office Excel 2007 - Illustrated

7. Creating a PivotTable Report Click to add a field to the PivotTable Microsoft Office Excel 2007 - Illustrated

8. Creating a PivotTable Report (cont.) Column fields Row fields Data area Microsoft Office Excel 2007 - Illustrated

9. Changing a PivotTable’s Summary Function and Design A PivotTable’s summary function controls what type of calculation is applied to the table data Unless you specify otherwise, Excel applies the SUM function to numeric data and the COUNT function to data fields containing text Easily change the SUM function to different summary function Microsoft Office Excel 2007 - Illustrated

10. Changing a PivotTable’s Summary Function and Design (cont.) PivotTable showing averages Microsoft Office Excel 2007 - Illustrated

11. Filtering and Sorting PivotTable Data You can filter a PivotTable using a report filter Moving a field to the Report Filter area of a PivotTable filters the report data by that field. You can also sort PivotTable row and column data to organize it in ascending or descending order. Microsoft Office Excel 2007 - Illustrated

12. Filtering and Sorting PivotTable Data (cont.) Only the 1st quarter should e displayed Quarter is in the Report Filter area Sales for 1st quarter only Microsoft Office Excel 2007 - Illustrated

13. Updating a PivotTable Report Data in a PivotTable Report looks like typical worksheet data However, because the data is linked to a source list, the values and results in the PivotTable are read-only values Unable to modify or move part of a PivotTable To change, or refresh, PivotTable data, you must update the data in the list used to create the table, called the source list Microsoft Office Excel 2007 - Illustrated

14. Updating a PivotTable Report (cont.) New record added Total reflects new CD information Microsoft Office Excel 2007 - Illustrated

15. Changing the Structure and Format of a PivotTable Report PivotTable data can’t be changed, but its structure and appearance can be changed Change a PivotTable’s appearance by moving or adding fields Microsoft Office Excel 2007 - Illustrated

16. Changing the Structure and Format of a PivotTable Report (cont.) Category is now in the Report Filter area Microsoft Office Excel 2007 - Illustrated

17. Creating a PivotChart Report A PivotChart report is a chart that you create from data or from a PivotTable report A PivotChart has fields that you move to explore new data relationships When you create a PivotChart directly from the data, Excel automatically creates a corresponding PivotTable report Microsoft Office Excel 2007 - Illustrated

18. Creating a PivotChart Report (cont.) PivotChart Microsoft Office Excel 2007 - Illustrated

19. Using the GETPIVOTDATA Function Ordinary cell references will not work when you want to reference a PivotTable cell in another worksheet If you change the way data is displayed in a PivotTable, the data moves, rendering an ordinary cell reference incorrect To retrieve summary data from a PivotTable, you need to use the Excel GETPIVOTDATA function Microsoft Office Excel 2007 - Illustrated

20. Using the GETPIVOTDATA Function (cont.) GETPIVOTDATA Function Result of the GETPIVOTDATA Function Microsoft Office Excel 2007 - Illustrated

21. Summary Spend time planning your PivotTable Use the PivotTable dialog box to create a PivotTable Filter and sort PivotTable data Summary functions of PivotTables can be changed Microsoft Office Excel 2007 - Illustrated

22. Summary (cont.) Use the Refresh button to update PivotTables The structure of a PivotTable can be altered PivotChart reports can be created directly from PivotTables Use the GETPIVOTDATA function to extract information from a PivotTable Microsoft Office Excel 2007 - Illustrated