Description

Training Annexe
32 Houndsfield Lane
Shirley
Solihull
West Midlands
B90 1PR

Microsoft® Office Specialist 2016 Series

Microsoft®2016Expert Certification Guide
Courseware 3264 Exam 77-728 ISBN 978-1-55332-499-7

Course Description

Microsoft® Excel Expert teaches students how to use a variety of intermediate and advanced features to merge workbooks, perform data analysis, and audit formulas in spreadsheets. Students also use summary functions, create PivotTables and PivotCharts, and work with macros.Students who complete this course will have reviewed all the exam objectives to prepare for: Microsoft Excel 2016 Expert Exam #77-728.

Successful completion of the certification exam provides a competitive advantage by validating the knowledge and skill sets for individuals who may be seeking employment or further job opportunities in their careers.

Course Prerequisites

Suggested Course Length: 25-30 Hours

This course is designed for students who are familiar with personal computers, using a keyboard and using a mouse. The course assumes that students have completed the Microsoft Windows course or have equivalent Microsoft Windows knowledge and experience.

• start and run Windows
• use the taskbar
• use the Start button
• use the Help feature
• use Minimize, Restore Down/Maximize, or Close
• use the left and right mouse buttons appropriately
  understand file management techniques
• navigate between files, folders, or drives

Microsoft Excel 2016 Expert                                                                                                     Outline About This Courseware

Courseware Description

Course Design

Course Objectives Conventions and Graphics

Lesson 1: Advanced Formatting

• Lesson Objectives
• Conditional Formatting
• Basic Conditional Formatting
• Manage Conditional Formatting Rules
• Custom Conditional Formatting Using a Formula
• Using Custom Cell Formats
• Using Custom Number Formats
• Using Custom Accounting Formats
• Using Custom Date and Time Formats
• Internationalization
• Using International Currency and Number Formats
• Using International Currency Symbols
• Using Custom and International Date and Time Formats
• User Defined Styles
• Creating and Modifying Cell Styles
• Custom Color Formats Custom Themes
• +Body and +Heading Fonts
• Lesson Summary Review Questions

Lesson 2: Advanced Functions and Formulas

• Lesson Objectives
• Working with Named Ranges
• Creating Named Ranges Naming Tables
• Modifying and Deleting Named Ranges
• What are Functions?
• Using the Correct Syntax Inserting Functions
• Using Lookup Functions
• CHOOSE Function
• INDEX Function
• MATCH Function
• LOOKUP Function
• HLOOKUP & VLOOKUP Functions
• Date and Time Functions
• Checking for Formula Errors
• Using the Error Checking Tool
• Tracing Formula Errors
• Evaluate Formulas
• Manually Checking and Displaying Formulas
• Lesson Summary Review Questions

Lesson 3: Data Analysis Using Pivot Tables and Business Intelligence

• Lesson Objectives
• Creating & Managing Pivot Tables
• Creating a Pivot Table
• Format Pivot Table Data
• Customizing PivotTables
• Using Data Slicers with a PivotTable
• Group Pivot Table Data
• Calculated Fields and Items
• Referencing Pivot Table Data
• Pivot Charts
• Creating a PivotChart
• Changing Pivot Chart Options
• Drilling Down a Pivot Table or Pivot Chart
• Pivot Chart Styles
• Business Intelligence
• Activating Power Pivot
• Connecting Power Pivot to a Data Source
• Power Pivot Calculated Fields
• Manage Table Relationships Using Cube Functions
• Lesson Summary Review Questions

Lesson 4: Workbook Management Features

• Lesson Objectives
• Linking External Workbooks
• Referencing Other Worksheets in Formulas
• Linking Other Workbooks
• Modifying Workbook Links
• Removing Workbook Links
• Consolidating Data Workgroup Functions
• Creating a Shared Workbook
• Tracking Changes
• Showing the History of Changes
• Removing Shared Use of Workbooks
• Mark as Final
• Using Comments
• Protecting Your Workbook Files
• Using Passwords
• Protecting the Worksheet
• Allow Ranges for Editing
• Protecting the Workbook Structure
• Password Protecting Workbook
• Lesson Summary Review Questions

Lesson 5: Advanced Charts, Functions and What-if Analysis

• Lesson Objectives
• Advanced Chart Elements
• Formatting a Simple Chart
• Add a Secondary Vertical Axis
• Custom Chart Templates
• Chart Trendline
• Using Advanced Functions and Features
• Financial Functions
• Nesting Functions
• Conditional Logic Functions
• Conditional Summary Functions
• What-If Analysis
• Manual What-If Analysis
• Using the Goal Seek Tool
• Working with Scenarios
• Using Cell Watch
• Structured References
• Lesson Summary Review Questions

Lesson 6: Data Filtering, Macros, and Forms

• Lesson Objectives
• Fill Series
• Advanced Filtering
• Using Advanced Filters
• Using Comparison Operators
• Working with Templates
• Creating a Template
• Modifying Templates
• Deleting Templates
• Accessing Hidden Ribbon Tabs
• Macros
• Creating a Macro
• Copy Macros
• Adding Form Controls
• Adding Command Buttons
• Spin Buttons and Scroll Bars
• Check Boxes and Option Buttons
• List Boxes and Combo Boxes
• Group Box
• Text Boxes
• Changing Excel Formula
• Calculation Options
• Lesson Summary Review Questions

Appendices

• Appendix A: Courseware Mapping
• Appendix B: Glossary of Terms
• Appendix C: Index

© CCI Learning Solutions Inc.