1 / 0

Introduction to Excel Pivot Tables

Introduction to Excel Pivot Tables. FSRC Workshops. Access workshop information and materials: http://www.csus.edu/irt/fsrc/workshops.html Click on Courses Link and download sample file(s). Announcements.

lazar
Télécharger la présentation

Introduction to Excel Pivot Tables

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. Introduction to Excel Pivot Tables

  2. FSRC Workshops Access workshop information and materials: http://www.csus.edu/irt/fsrc/workshops.html Click on Courses Link and download sample file(s).
  3. Announcements We understand scheduling conflicts. Feel free to politely exit the room early if necessary. Please log out of your workstation. We encourage you to ask questions, but if you find that you need further assistance, additional resources are available. Ask us after the workshop ends.
  4. Pre-Requisites Basic knowledge using Excel 2010
  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
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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.)
  20. Activity Drag Orders to Report Filter Drag SalesRep to Row Labels Drag Total Sales to Values Drag Date to Column Labels
  21. 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
  22. 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
  23. 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
  24. Sparklines
  25. 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)
  26. 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
  27. Activity Open the file called SampleData_Orders.xlsx Create a PivotTable and add sparklines
  28. Saving and Printing
  29. 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
  30. Saving Options Save as .xlsx Save as Adobe PDF Save as a .csv or .txt Demonstration + Activity: Save the report as a PDF
  31. Printing Options Print the Active Sheets Print the Entire Workbook Print Selection Demonstration + Activity Print selection only
  32. Summary Set up data in excel Converted data into Table Created a Pivot Table/Report Added Sparklines Saving and Printing Tips Saved as PDF
  33. Safari Books Go to library website and click advanced search Enter a Search Term: “Microsoft Word” Select “Electronic Bk” for Material Type and click submit. Click on a search result and click “Connect to this resource online” Safari Books Online will display content
  34. Workshop Evaluation Please provide your feedback by going to: http://www.csus.edu/irt/FSRC/workshops.html See Survey link under “Staff Workshop Survey” area on page Complete Survey
  35. Resources for Further Assistance Faculty Staff Resource Center www.csus.edu/irt/fsrc 916-278-6112 fsrc@csus.edu Service Desk www.csus.edu/irt/servicedesk 916-278-7337 servicedesk@csus.edu
More Related