Microsoft Excel Advanced

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

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

Before taking this course, students are recommended to take the following courses, or have equivalent knowledge:

• Microsoft Excel Introduction
• Microsoft Excel Intermediate

This course is one of a series of courses that addresses Microsoft Certified Application Specialist (Microsoft Business Certification) skill sets. The Microsoft Certified Application Specialist program is for individuals who use Microsoft's business desktop software and who seek recognition for their expertise with specific Microsoft products. Certification candidates must pass one or more proficiency exams in order to earn Microsoft Certified Application Specialist certification.

Upon successful completion of this course, students will be able to:

• Work with multiple worksheets and workbooks simultaneously.
• Share and protect workbooks.
• Automate workbook functionality.
• Apply conditional logic.
• Audit worksheets.
• Use automated analysis tools.
• Present your data visually.

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 Using Data Tables
Topic B: Determine Potential Outcomes Using Scenarios
Topic C: Use the Goal Seek Feature
Topic D: Activate and Use the Solver Tool
Topic E: Analyse Data with Analysis ToolPak Tools

Lesson 7: Presenting Your Data Visually

Topic A: Use Advanced Chart Features
Topic B: Create Sparklines