Data Analysis

1. Data Analysis Dr. Carolyn Rainey Professor Emeritus

2. What is Microsoft Certification? • Microsoft Office Specialist (MOS) • www.certiport.com • http://www.certiport.com/Portal/desktopdefault.aspx?page=common/pagelibrary/MOS_whatIs.html • Microsoft Office 2010 Exam Objectives • Exam voucher • http://www.measureup.com/Microsoft-Office-Specialist-MOS-200720102013-Exam-Voucher-P3195.aspx

3. Overview of Workshops • Workshop One (10/22) - Managing the Worksheet Environment • Workshop Two (10/24) - Creating Cell Data, Formatting/Revising Worksheets/Workbooks • Workshop Three (10/29) - Applying Formulas and Functions • Workshop Four (10/31) - Presenting Data Visually • Workshop Five(11/5) - Data Analysis

4. Data Analysis • Open file: WearEverInventory-04http://highered.mcgraw-hill.com/sites/0073519383/student_view0/data_files.html • Create a table • Add a calculated column • Apply number formatting to the calculated column • Format the tab with banded rows and columns

5. Data Analysis • Sort the table • Add a level to the sort • Filter the table • Place filtered data on a separate worksheet • Apply Conditional Formatting • Filter and sort by cell color

6. Data Analysis • Remove duplicate records • Add a total row • Determine sums, average, counts • Copy just the table • Paste “values with formatting” in cell A1 on a new worksheet • Reformat columns G, H, J; sort by Column A

7. Data Analysis • Create subtotals • Sum, average, counts • Collapse groups • Create a bar chart of Columns B and J • Create and manipulate a PivotTable • Add pivot fields • Add filters • Add value settings • Edit for audience

8. Data Analysis • Create and manipulate a Pivot Chart • Edit for audience • Create and manipulate Macros • Insert and manipulate form controls • Assign a Macro • Use a spin button

9. Data Analysis • Map XML data; export XML data • Backstage • Iterative calculation (MAX and Change) • Encrypt • Mark as Final

10. Data Analysis • Participate in Certiprep review questions