For information on course costs and booking onto a course, please click here. For any queries please contact us on business@nptcgroup.ac.uk or call: 0330 81 88 100Welcome, Guest

Excel 2021 - Expert - Advanced Functions, Consolidating and Auditing

About the course

The Excel 2021 Expert – Advanced Functions, Consolidating and Auditing course covers topics included in the MOS Expert exam. This includes 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

  • Time and Mathematical Functions
  • Lookup Functions
  • Logical and Informational Functions
  • Consolidating Workbooks
  • Auditing Tools
  • Protecting Workbooks
  • Sharing 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:

Advanced Functions Time and Mathematical Functions

  • Using Functions;
  • Insert Function Box;
  • TODAY Function;
  • Displaying Dates as Numbers;
  • DAY Function;
  • MONTH Function;
  • YEAR Function;
  • COUNT Function;
  • COUNTA Function;
  • COUNTIF Function;
  • SUMIF Function;
  • AVERAGEIF Function;
  • ROUND Function;
  • Nesting Functions;
  • Circular Functions;
  • Iterative Calculations.

Lookup Functions

  • VLOOKUP Function;
  • Creating Lookup Tables;
  • Inserting Functions;
  • VLOOKUP Arguments;
  • LOOKUP Function;
  • Modify Lookup Data;
  • HLOOKUP Function

Logical and Informational Functions

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

Consolidating and Auditing

Consolidating Workbooks

  • Consolidating Data;
  • What is Consolidation?
  • Selecting Destination Area;
  • Consolidating Workbooks;
  • Creating Links;
  • Updating Linked Data;
  • Outlining Features.

Auditing Tools

  • Using Auditing Tools;
  • Trace Precedents;
  • Trace Dependents;
  • Removing Tracer Arrows;
  • Tracing Errors in Formulas;
  • Correcting Formula Errors.
  • Viewing Formula Locations;
  • Show Formulas.

Protecting and Sharing Workbooks

  • Protecting Worksheets;
  • Removing Protection;
  • Protecting Workbooks;
  • Opening Protected Workbooks.

Sharing Workbooks

  • Sharing Methods;
  • Share with People;
  • Uploading to OneDrive