Event description

In today’s data-driven world, MS Excel plays a crucial role to store, sort, process, analyze and present data. It is one of the most important business skills to learn.

The OntroPlus Excel Intermediate course is designed to give you in-depth training at carefully selected topics to accelerate your confidence to work with the world’s most used business intelligence tool. You will be able to step up on your Excel Foundation skills to Excel Intermediate skills which will enable you to build models, functions and calculations for specific tasks you may want to perform.

At the end of the Intermediate Excel course, you will learn how to use cell referencing effectively, organize data, perform calculations with advance functions, work with multiple worksheets, format the appearance of data and cells, build charts, and create PivotTables. Find out how to use the IF, VLOOKUP, and COUNTIF family of functions. See how to use data analysis tools, including Goal Seek and Solver. Discover how to automate tasks with macros, manage data, and more.


Advanced Microsoft Excel Formulae

  • Named Ranges in Formulas
  • Naming a Single Cell
  • Naming a Range of Cells
  • Naming Multiple Single Cells Quickly
  • Formulas That Span Multiple Worksheets
  • Logical Functions – IF, AND, OR, NOT functions
  • Partial and Absolute cell referencing
  • Exercises

Lookup and other Functions

  • Using VLOOKUP function
  • Exercise to demonstrate VLOOKUP function
  • Using HLOOKUP function
  • Exercise to demonstrate HLLOOKUP function
  • More LOOKUP functions: VLOOKUP+Match, HLOOK+Match
  • Nested IF Functions: IF + VLOOKUP, IF + HLOOKUP
  • CONCATENATE Function
  • TRANSPOSE Function
  • PROPER, UPPER, and LOWER Functions
  • The TRIM Function
  • Using the LEFT, RIGHT, and MID Functions

Intermediate Data Techniques

  • Creating Scenarios
  • Utilize the Watch Window
  • Consolidate Data
  • Enable Iterative Calculations
  • What-If Analyses
  • Use the Scenario Manager
  • Use Financial Functions

Lists Management

  • Removing Duplicates from a List
  • Sorting Data in a List
  • Filtering Data in a List
  • Adding Subtotals to a List
  • Grouping and Ungrouping Data in a List

Working with Design Mode

  • Using Illustrations
  • Using Clip Arts
  • Using Shapes
  • Working with SmartArt

Charting and Data Visualization

  • Visualizing Your Data
  • Creating a Custom Chart Template
  • Inserting Charts: create Line, Bar and Pie charts
  • Create Scatter, Radar chart
  • Create Double Axis chart using secondary axis
  • Rotate a chart

Advanced Formatting

  • Advanced Formatting
  • Applying Conditional Formatting
  • Working with Styles
  • Applying Styles to Tables
  • Applying Styles to Cells
  • Creating and Modifying Templates
  • Modify a Custom Template
  • Microsoft Excel Features that Were New in 2016

Protecting your Microsoft Excel Data


Prior knowledge of Microsoft Excel Foundation knowledge and skills:

  • How to enter data in Excel
  • How to manage worksheets
  • How to build simple functions
  • How to create charts
  • How to format data



You will need to bring your laptop with Microsoft Excel pre-installed.



Please select and book the date and time you would like to attend the Excel Intermediate course. Afternoon sessions run from 2 pm to 6 pm.