Introduction to Excel Pivot Tables

1. Introduction to Excel Pivot Tables

2. Pre-Requisites • Basic knowledge using Excel 2010

3. STC Workshops • Access workshop information and materials at: http://www.csus.edu/irt/STC/workshops/index.html

4. Connect to our Social Spaces facebook.com/sacstateIRT twitter.com/sacstateIRT youtube.com/sacstateIRT instagram.com/sacstateIRT

5. Objectives • Learn how to create pivot table reports, • Learn how to use SparkLines, • Learn how to save and print reports.

6. Preparing Data for PivotTable

7. Backup Your Data • Back up Raw data by creating a copy • Work from worksheet copy of raw data • Copy ensures raw data is not lost if something goes wrong • Move raw data copy to be first sheet Demonstration + Activity: Open sample excel file and make a copy of worksheet into existing workbook

8. Move/Copy Process

9. Rename worksheets • Helps keep track of worksheet contents • Limit of 31 Characters • Worksheet with Raw data should contain “Raw data” reference Demonstration + Activity: Rename the original Raw data worksheet and worksheet copy

10. Remove duplicates • Find duplicate data with conditional formatting • Filter for Unique Values • Use Remove Duplicates Command

11. Demo & Activity • Use Conditional Formatting, Remove duplicates, options • Clear rules from selected sheets when you remove duplicates • Home > Styles > conditional formatting > clear rules from entire sheet

12. Convert Data to Table • Enables data to be more usable • Apply a Table Style and other formatting • Sort and Filter options enabled automatically in table view • Generate a Pivot Table from existing table Demonstration + Activity Convert data into a table

13. Create a Pivot Table

14. Pivot Table Defined “A PivotTable provides an easy way to summarize information stored in a range, a table, or an external data source.” “…present endless rows and columns of numbers in a variety of meaningful ways” Source: Master Visually Excel 2010 Elaine Marmel

16. What type of Data? • Raw Data: un-processed, un-summarized • Numeric and Non-Numeric Data • Organized as a list with labeled columns • At least 3 columns of data • Data should be entered correctly • No gaps or blank spaces • List of anything • Employee Contact Details • Financial Transactions • Academic Year Course Information

17. Pivot vs. manual Report Pivot Table/Report Manual Report/Table Static Summary of Data Manually created (can take min - hrs) Difficult/tedious to change summary Create formulas to display data a certain way No automatic process to rotate data (manual) • Interactive Summary of Data • Created with a few mouse clicks (takes a few sec) • Easily Change summary • Formulas not required • Couple of clicks for summary to be “pivoted” (rotated) switch row to be column data

18. Creating Pivot Table Report • Steps to create a pivot table include: • Identify source data, must be Raw data • Identify where pivot table will go in workbook • Layout the fields • Report that provides concise and exact insight into the data you need to present. Demonstration + Activity Create a Pivot Table for Sample file using Raw data

19. Pivot Table Components • PivotTable Area • PivotTable Field List • Appears any time we click on PivotTable • Displays Column headings from Raw data • 4 boxes (report, column, row, values) • Choose way PivotTable summarizes data • Drag and drop fields to 4 drop zones

20. Drop Zones • Report Filter:Filter out data • Column Labels:determine the arrangement of data shown in the columns of the pivot table. • Row Labels:determine the arrangement of data shown in the rows of the pivot table. • Values:data that will appear in cells. Values are summarized (summing, avg. etc.)

21. Activity • Drag Orders to Report Filter • Drag SalesRep to Row Labels • Drag Total Sales to Values • Drag Date to Column Labels

22. Additional Formatting • Group Dates • Group by Month & Year • Value Field formatting • Number to Currency • Filter options • Filter by Order # • Apply a Design to Pivot Table • Design Tab • Change Layout and apply a style

23. Pivot Table Options • Options Tab Appears on Ribbon • Change Name for PivotTable • Hide/Unhide Grand Totals • Insert Slicers • Options > Sort & Filter > Insert Slicer • Visual way of filtering • Refresh • Change a value in source data • Options > Data Group > Refresh

24. PivotTable Options cont… • Change Data Source • Use to add extra column or change source • Recapture data without Date Info • Pivot Table Selection • Options > Actions: Useful if need to format sections of PT at a time • Enable Selection Arrow (Black arrow ) > select region & Edit • Move Pivot Table • Insert a PivotChart

25. Sparklines

26. Sparklines • Tiny chart inside a cell that provides a visual representation of data • Use to show trends at a glance for range of data. • Placed next to or near underlying data (in context)

27. Creating a Sparkline • Changes done to underlying data automatically transfer to sparkline • Sparklines are printed for worksheets that contain them • Use the “Sparklines” group (Line, column, win/loss) Demonstration + Activity: Create a line Sparkline for Sample file on PivotTable sheet

28. Activity • Open the file called SampleData_Orders.xlsx • Create a PivotTable and add sparklines

29. Saving and Printing

30. Before you Save & Print • Set Print Area • Specify Orientation/Margins • Scale to Fit on Page • Gridlines • Page Layout Tab and Page Set Up (file menu) Demonstration + Activity Set print area and page set up properties

31. Saving Options • Save as .xlsx • Save as Adobe PDF • Save as a .csv or .txt Demonstration + Activity: Save the report as a PDF

32. Printing Options • Print the Active Sheets • Print the Entire Workbook • Print Selection Demonstration + Activity Print selection only

33. Summary • Set up data in excel • Converted data into Table • Created a Pivot Table/Report • Added Sparklines • Saving and Printing Tips • Saved as PDF

34. Safari e-Books @ Library • Go to http://proquest.safaribooksonline.com/ • Search for Microsoft Office content

35. Workshop Survey • Please provide your feedback by going to: http://www.csus.edu/irt/STC/workshops/index.html • Find the Workshop Survey Link • Click the link to Workshop title attended • Complete Survey

36. Resources for Further Assistance Student Tech Center www.csus.edu/irt/stc 916-278-2364 stc@csus.edu Service Desk www.csus.edu/irt/servicedesk 916-278-7337 servicedesk@csus.edu