Excel Level Three

Excel 2010

Course ID: HLT 84578

Course Length: 1 day

Certification: MOS: Microsoft Office Excel 2010 Exam 77-882
This course is one of a series of courses that addresses Microsoft Office Specialist (MOS) certification skill sets. The MOS and certification program is for individuals who use Microsoft's business desktop software and who seek recognition for their expertise with specific Microsoft products.

Course Objective:
You will automate some common Excel tasks, apply advanced analysis techniques to more complex data sets, troubleshoot errors, collaborate on worksheets, and share Excel data with other applications.

Target Student:
This course was designed for students desiring to gain the skills necessary to create macros, collaborate with others, audit and analyze worksheet data, incorporate multiple data sources, and import and export data.

Prerequisites:
To ensure your success, we recommend you first have equivalent knowledge:

Delivery Method:
Instructor led, group-paced, classroom-delivery learning model with structured hands-on activities.

Performance-Based Objectives :
Upon successful completion of this course, students will be able to:

  • enhance productivity and efficiency by streamlining the workflow.
  • collaborate with other workbook users.
  • audit worksheets.
  • analyze data.
  • work with multiple workbooks.
  • import and export data.
  • integrate Excel data with the web.
  • structure workbooks with XML

Course Content

Lesson 1: Streamlining Workflow
Topic 1A: Update Workbook Properties
Topic 1B: Create a Macro
Topic 1C: Edit a Macro
Topic 1D: Apply Conditional Formatting
Topic 1E: Add Data Validation Criteria

Lesson 2: Collaborating with Other Users
Topic 2A: Protect Files
Topic 2B: Share a Workbook
Topic 2C: Set Revision Tracking
Topic 2D: Review Tracked Revisions
Topic 2E: Merge Workbooks
Topic 2F: Administer Digital Signatures
Topic 2G: Restrict Document Access

Lesson 3: Auditing Worksheets
Topic 3A: Trace Cells
Topic 3B: Troubleshoot Invalid Data and Formula Errors
Topic 3C: Watch and Evaluate Formulas
Topic 3D: Create a Data List Outline

Lesson 4: Analyzing Data
Topic 4A: Create a Trendline
Topic 4B: Create Sparklines
Topic 4C: Create Senarios
Topic 4D: Perform a What-if-Analysis
Topic 4E: Perform Statistical Analysis with the Analysis ToolPak

Lesson 5: Working with Multiple Workbooks
Topic 5A: Create a Workspace
Topic 5B: Consolidate Data
Topic 5C: Link Cells in Different Workbooks
Topic 5D: Edit Links

Lesson 6: Importing and Exporting Data
Topic 6A: Export Excel Data
Topic 6B: Import a Delimited Text File

Lesson 7: Integrating Excel Data with the Web
Topic 7A: Publish a Worksheet to the Web
Topic 7B: Import Data from the Web
Topic 7C: Create a Web Query

Lesson 8: Structuring XML Workbooks
Topic 8A: Develop XML Maps
Topic 8B: Import and Export XML Data

Excel 2013

Course ID: HLT 91053

Course Length: 1 day

Course Description: This course builds off of the foundational and intermediate knowledge presented in the Microsoft® Office Excel® 2010: Part 1 (Second Edition) and Part 2 (Second Edition) courses to help you get the most of your Excel experience. The ability to collaborate with colleagues, automate complex or repetitive tasks, and use conditional logic to construct and apply elaborate formulas and functions will put the full power of Excel right at your fingertips. The more you learn about how to get Excel to do the hard work for you, the more you'll be able to focus on getting the answers you need from the vast amounts of data your organization generates.

Prerequisites: To ensure success, students should have practical, real-world experience creating and analyzing datasets using Excel 2010. Specific tasks students should be able to perform include: creating formulas and using Excel functions; creating, sorting, and filtering datasets and tables; presenting data by using basic charts; creating and working with PivotTables, slicers, and PivotCharts; and customizing the Excel environment. To meet these prerequisites, students can take the following:

Delivery Method: Instructor led, group-paced, classroom-delivery learning model with structured hands-on activities.

Course Content

Lesson 1: Working with Multiple Worksheets and Workbooks Simultaneously
Topic A: Use 3-D References
Topic B: Use Links and External References
Topic C: Consolidate Data

Lesson 2: Sharing and Protecting Workbooks
Topic A: Collaborate on a Workbook
Topic B: Protect Worksheets and Workbooks

Lesson 3: Automating Workbook Functionality
Topic A: Apply Data Validation
Topic B: Work with Forms and Controls
Topic C: Work with Macros

Lesson 4: Applying Conditional Logic
Topic A: Use Lookup Functions
Topic B: Combine Functions
Topic C: Use Formulas and Functions to Apply Conditional Formatting

Lesson 5: Auditing Worksheets
Topic A: Trace Cells
Topic B: Search for Invalid Data and Formulas with Errors
Topic C: Watch and Evaluate Formulas

Lesson 6: Using Automated Analysis Tools
Topic A: Determine Potential Outcomes by Using Data Tables
Topic B: Determine Potential Outcomes by Using Scenarios
Topic C: Use the Goal Seek Feature
Topic D: Activate and Use the Solver Tool
Topic E: Analyze Data with Analysis ToolPak Tools

Lesson 7: Presenting Your Data Visually
Topic A: Use Advanced Chart Features
Topic B: Create Sparklines

Appendix A: Microsoft Office Excel 2013 Exam 77-420

Appendix B: Microsoft Office Excel 2013 Expert Exams 77–427 and 77-428

Appendix C: Cube Functions

Appendix D: Import and Export Data

Internationalize Workbooks

Work with PowerPivot