About the course

The Excel 2016 - Expert - Advanced Formulas, Macros and External Data course covers topics included in the MOS Expert exam 77-728. This includes Tracking changes, using Logical, Informational, Time, Mathematical and Lookup Functions. You will also learn how to Consolidate workbooks, Share and Protect workbooks and use Auditing tools.

Modules

There are 7 modules in this course

• Tracking and Merging
• Logical and Informational Functions
• Time and Mathematical Functions
• Lookup Functions
• Consolidating Workbooks
• Auditing Tools
• Protecting and Shared Workbooks

Course features

• Simulation training replicates the software you are learning providing a realistic learning experience.
• Learn how to perform tasks at your own pace and interactively by following easy step-by-step actions.
• Reinforce your knowledge at the end of each lesson by completing interactive and multiple choice quiz questions.
• Assess your skills at any time by undertaking the Course Test.
• Lessons can be completed within 30 minutes so training can be undertaken in 'bite' size pieces.
• Bookmarking allows you to learn in multiple training sessions.
• Course navigation features allow you to jump directly to specific topics of interest quickly and easily.
• Designed for people who require initial and refresher training activities.
• Available on-line 24 hours a day.
• Version for Mobile Devices.
• Review course content with eBooks

Youll learn how to:

Tracking and Merging

• Share Workbooks;
• Track Changes in Workbooks;
• Highlight Changes;
• View Change Details;
• Hide Changes;
• Accept Changes;
• Reject Changes;
• Remove Workbook Tracking;
• Merge Workbooks.

Advanced Functions - Logical and Informational Functions

• Use the IF Function;
• Use the OR Function;
• Use the AND Function;
• Use Help for Error Messages;
• Use Nested IF Functions;
• Understand IS Functions;
• Use the ISERROR Function.

Time and Mathematical Functions

• Use the TODAY Function;
• Use DAY, MONTH and YEAR;
• Use the COUNT Function;
• Use the COUNTA Function;
• Use the COUNTIF Function;
• Use SUMIF and AVERAGEIF;
• Use the ROUND Function;
• Nest Functions;
• Enable Iterative Calculations.

Lookup Functions

• Use VLOOKUP Functions;
• Create Lookup Tables;
• Insert Functions;
• Add VLOOKUP Arguments;
• Use LOOKUP Functions;
• Modify Lookup Data;
• Use HLOOKUP Functions.

Consolidating and Auditing - Consolidating Workbooks

• Consolidate Workbooks;
• Select Destination Cells;
• Add Source Data References;
• Create Source Data Links;
• Update Linked Data;
• Use Consolidated Outlines.

Auditing Tools

• Trace Precedents;
• Trace Dependents;
• Remove Tracer Arrows;
• Trace Errors in Formulae;
• Correct Formula Errors.
• Display Formula Locations;
• Show Formulas in Worksheets

Protecting and Sharing - Protecting and Shared Workbooks

• View the User Name;
• Unlock Cells;
• Protect Worksheets;
• Remove Protection;
• Share Workbooks;
• Protect Workbooks;
• Open Protected Workbooks.