Presentation Transcript

1. Managing Data in Excel

   Create attractive reports from complex data
2. FSRC Workshops Access workshop information and materials: http://www.csus.edu/irt/fsrc/workshops.html Please download sample files.
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 import data in other file formats into Excel, Learn to how set up data in Excel to make it usable, Learn how to create pivot table reports, Learn how to use SparkLines Learn how to save and print reports effectively.
6. Importing Data
7. Different formats of Data .csv(comma separated value file) .txt (delimited file) Data from Microsoft Access Demonstration: Open a .CSV data file.
8. Importing Data from Access Most users are unfamiliar with using Access Easier to manipulate data in Excel Use the “From Access” command in Excel Specify what to Import Table, Query Specify how data should display: Table, Pivot table Report Demonstration: Import sample access data as a table into excel
9. Importing Delimited Data Data is separated by specific delimiter characters e.g. tabs, commas, semi-colons .csv and .txt most common .csv opens automatically, .txt requires extra steps Demonstration + Activity: Open sample .txt file in Notepad and import into Excel
10. Setting up Data in Excel
11. Create Worksheet Copy 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: Make a copy of imported data worksheet into existing workbook
12. 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
13. Cleaning up Data Delete any duplicate data using “Remove Duplicates” command Sort data by different criteria if needed (order A to Z or column value) Filter data to only display data for specific cell values Demonstration: Use the Remove duplicates, Sort and Filter options under “Data” group
14. 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

15. Generating Reports

    Pivot Tables
16. Pivot Table Defined “Is a summary of some data, created to allow easy analysis of said data”
17. What type of Data? Raw Data: un-processed, un-summarized Organized as a list with labeled columns List of anything Employee Contact Details Financial Transactions Book collection Music downloads from iTunes Activity: Open raw data sales sample
18. 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
19. Creating Pivot Table Report Steps to create a pivot table include: Define source data, must be Raw data Specify location in workbook, Layout the fields Report that provides concise and exact insight into the data you need to present. Attractive online or printed reports Demonstration + Activity Create a Pivot Table for Sample file using Raw data
20. How it Works PivotTable 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
21. 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.)
22. Additional Formatting Group Dates Value Field formatting Filter options Apply a Design to Pivot Table
23. Sparklines
24. Sparklines Tiny chart inside a cell that provides a visual representation of data Use to show trends in series of values, or to highlight maximum and minimum values Place next to or near underlying data
25. 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 Sales Sample file on PivotTable sheet
26. Saving and Printing
27. 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
28. Saving Options Save as Adobe PDF Save as a .csv or .txt Save as .xlsx Demonstration + Activity: Save the report as a PDF
29. Printing Options Print the Active Sheets Print the Entire Workbook Print Selection Demonstration + Activity Print selection only
30. Summary Import Data into Excel Set up data in excel Converted data into Table Created a Pivot Table/Report Added Sparklines Saving and Printing Tips Saved as PDF
31. 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
32. Workshop Evaluation Please provide your feedback by going to: http://www.csus.edu/irt/FSRC/surveys.html Click the link to Workshop title attended Complete Survey
33. 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