About the course

The Excel 2021 Expert – Advanced Formulas, Macros and External Data course covers topics included in the MOS Expert exam.  This includes custom templates, data formats and Internationalizing workbooks. You will also get external data, use macros and form controls, evaluate formulas and use the MATCH and INDEX functions.

Modules

There are 7 modules in this course

• Languages and Accessibility
• AutoSave and Templates
• Custom Formatting
• External Data and Queries
• Macros and Form Controls
• Evaluating Formulas and Watch Window
• MATCH and INDEX Function

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:

Internationalization and Accessibility

Languages and Accessibility

• Internationalization;
• Language Options;
• Adding Editing Languages;
• Language Proofing Options;
• Applying Currency Formats;
• Applying Accounting Formats;
• Applying Date Formats;
• Creating Font Themes;
• Checking for Accessibility.

AutoSave Versions and Custom Templates

AutoSave and Templates

• AutoSave;
• Restoring AutoSave Versions;
• Restoring AutoRecover Files;
• Creating New Templates;
• Opening Custom Templates.

Creating Custom Formats

Custom Formatting

• Creating Custom Formats;
• Creating Number Formats;
• Creating Date Formats;
• Creating Time Formats;
• Creating Conditional Formats;
• Applying Conditional Formats;
• Creating Custom Styles;
• Applying Custom Styles.

Data Sources and Queries

External Data and Queries

• Get External Data;
• Refreshing External Data;
• Importing Multiple Tables;
• Creating Custom Queries;
• Appending Queries;
• Merging Queries;
• Removing Fields;
• Editing Steps;
• Renaming Fields.

Using Macros and Form Controls

Macros and Form Controls

• Macros;
• Customize Ribbon Settings;
• Viewing Macros;
• Editing Macros;
• Worksheet Controls;
• Inserting Controls;
• Adding Text Boxes;
• Modifying VBA Code;
• Using Structured References;
• Using Flash Fill.

Evaluating Formulas and Watch Window

• Working with Formulas;
• Evaluating Formulas;
• Dependents and Precedents;
• Tracing Dependents;
• Tracing Precedents;
• Using the Watch Window.

Using MATCH and INDEX Functions

MATCH and INDEX Functions

• MATCH and INDEX Functions;
• Entering Validation Rules;
• Using MATCH Functions;
• Using INDEX Functions;
• Nesting Functions.