Our advanced Microsoft Excel training course will teach you how to use the advanced features of Excel in the current version to their full extent. Excel is truly broad but with our excercises, you will be able to know what it takes to be an advanced user of Microsoft Excel. You will be able to report on data in a list using PivotTables, edit and analyse complex data using advanced features such as Solver, incorporate built-in functions into a worksheet, work with controls and record macros.
This course is instructor led, and involves lots of examples and exercises in a workshop environment. Led by highly experienced Microsoft Excel trainers with many years of experience our hands-on courses mean that you will leave with lots of practical Excel experience as well as useful tips and tricks. We train on version 2016.

Overview of Advanced Excel

Types of Referencing

Relative Referencing

Absolute Referencing

Mixed Referencing

Working with Excel List

Understanding Excel List Structure

Sorting a List Using Single Level Sort

Sorting a List Using Multi Level Sort

Using Custom Sort in An Excel List

Filter an Excel List Using the Auto Filter Tool

Creating Subtotals in A List

Format a List as a Table

Using Conditional Formatting

Removing Duplicates

Excel List Functions

Excel Function: DSUM ()

Excel Function: SUMIF ()

Excel Function: SUMIFS ()

Excel Function: DAVERAGE ()

Excel Function: AVERAGEIF ()

Excel Function: AVERAGEIFS ()

Excel Function: DCOUNT()

Excel Function: COUNTIF()

Excel Function: COUNTIFS()

Excel Function: SUBTOTAL ()

Excel Function: VLOOKUP

Excel Function: HLOOKUP

Excel Function: INDEX AND MATCH

Logical Functions


Excel Data Validation

Understanding the Need for Data Validation

Creating A Validation List

Adding A Custom Validation Error

Dynamic Formulas by Using Validation Techniques

Data Cleaning with Flash Fill and Text to Column

Importing and Exporting Data

Importing Data from Text Files

Importing Data from Microsoft Access

Exporting Data to A Text File

Excel Pivot Tables

Understanding Excel Pivot Tables

Creating an Excel Pivot Table

Modifying Excel Pivot Table Calculations

Grouping Pivot Table Data

Formatting Pivot Table Data

Drilling Down into Pivot Table Data

Creating Pivot Charts

Filtering Pivot Table Data

Filtering with The Slicer Tool

Filtering with The Timeline Tool

 Working with Excel Pivot Tools

Why PowerPivot?

Activating the Excel PowerPivot Addin

Creating Data Models with PowerPivot

Creating Pivot Tables Based on Data Models

PowerPivot Calculated Fields

Working With Large Sets of Excel Data

Using the Freeze Panes Tool

Grouping Data (Columns and/or Rows)

Print Options for Large Sets of Data

Linking Worksheets

Consolidating Data from Multiple Worksheets

Linking PowerPoint/Word Charts and tables to Excel for Automatic Update

Auditing and Automation

Auditing an Excel Worksheet

Protecting Excel Worksheets and Workbooks
Working with Solver, Scenario Manager, Goal Seek and Data Table

Excel VBA 

Understanding to Macros

Introduction to Macros

Recording Excel Macro

What are the pre-requisties?
Basic experience on Windows Operating System. It is recommended that students have experience with the fundamental use and features of Microsoft Excel including the ability to:

  • Create formulae and basic functions
  • Use Absolute Cell references
  • Edit data using formatting and layout techniques including Charting
    Work with database tools, including database filtering and functions

Who Should Attend?
Recommended for those who require knowledge on Advanced Microsoft Excel 2016.

What is included in this course?
Training materials.

What is the Duration of this course?
The course is for 4 days (Saturday Only)

How to Apply?

A confirmation Email will be sent to you. You are not fully registered, unless payment has being made.


Training Cost: N30,000