Microsoft Excel 2016 Intermediate

Quorse - Your Training Wholesaler

  • Enjoy discounts up to 60% off!
  • Chat with us Live to get a Quotation to see it for yourself!
  • All courses are HDRF Claimable

Private Class from


(2 Days)


Upon completion of this program, participants should be able to:

  • Create simple to complex formulas and functions, like:
  • Database Function
  • COUNTIF Function
  • COUNTIFS Function
  • AVERAGEIF Function
  • AVERAGEIFS Function
  • SUMIF Function
  • SUMIFS Function
  • IF Function
  • AND Function
  • VLOOKUP Function
  • Validate data in a Worksheet
  • Filter data using Auto & Advanced Filters
  • Create Chart
  • Clean Duplicate Records
  • Apply Conditional Formatting
  • Protect Worksheet & Workbook


This course is designed for Clerks, Officers, Executives, Supervisors, Administrators, Managers of all levels; and personnel who already know and understand and want to further enhance their knowledge and practical uses of Microsoft Excel.



Basic knowledge of Microsoft Excel is essential with the following pre-requisites:

  • Have attended Microsoft Excel – Foundation Level; OR
  • Able to switch between task applications
  • Able to create a spreadsheet with simple formatting
  • Able to create a basic chart
  • Able to print a spreadsheet with headers and footers added
  • Able to apply Freeze Pane command
  • Able to create basic formulas – Addition, Subtraction, Multiplication and Division
  • Able to use basic functions – AutoSum, Count, Max, Min and Average functions.


This program will be conducted with interactive lectures, PowerPoint presentation, discussions and practical exercise

Lesson 1 – Performing Calculations

Topic A: Reuse Formulas

  • Relative References
  • Absolute References
  • Mixed References
Lesson 2 – Creating Advanced Formulas

Topic A: Apply Range Names

  • Adding Range Names Using the Name Box
  • Adding Range Names Using the New Name Dialog Box
  • Editing a Range Name and Deleting a Range Name
  • Using Range Names in Formulas
  • The Go to Command

Topic B: Use Specialized Functions

  • Showing and Hiding Formulas
Lesson 3 – Organizing Worksheet Data with Tables

Topic A: Create and Modify Tables

  • Tables
  • Table Components
  • The Create Table Dialog Box
  • The Table Tools – Design Contextual Tab
  • Styles and Quick Style Sets
  • Customizing Row Display
  • Table Modification Options

Topic B: Sort and Filter Data

  • The Difference Between Sorting and Filtering
  • Sorting Data
  • Advanced Filtering
  • Filter Operators
  • Removing Duplicate Values

Topic C: Use Subtotal and Database Functions to Calculate Data

  • SUBTOTAL Functions
  • The Subtotal Dialog Box
  • Summary Functions in Tables
  • Database Functions

Topic D: Validating your Data

  • Data Validation using Lists
  • Data Validation using Date
  • Data Validation using Formulas
Lesson 4 – Working with Statistical, Mathematical, Logical and Lookup Functions

Topic A: Statistical Functions

  • COUNTIF Function
  • COUNTIFS Function
  • AVERAGEIF Function
  • AVERAGEIFS Function

Topic B: Mathematical Function

  • SUMIF Function
  • SUMIFS Function

Topic C: Use Logical Functions

  • The IF Function
  • The AND Function
  • The IFERROR Function

Topic D: Use Lookup Functions

  • Understanding VLOOKUP
  • Using VLOOKUP to Find Data
  • How to Find an Exact Match with VLOOKUP
  • Finding an Approximate Match with VLOOKUP
  • Using VLOOKUP as an Array Formula
Lesson 5 - Visualizing Data with Charts

Topic A: Create Charts

  • Charts
  • Chart Types
  • Chart Insertion Methods
  • Resizing and Moving the Chart
  • Adding Additional Data
  • Switching Between Rows and Columns

Topic B: Modify and Format Charts

  • The Difference Between Modifying and formatting
  • Chart Elements
  • Minimize Extraneous Chart Elements
  • The Chart Tools Contextual Tabs
  • Formatting the Chart with a style
  • Adding a Legend to the chart

Topic C: Sparkline

  • What are Sparklines?
  • Adding Sparklines
  • Editing Sparkline Data
  • Removing Sparklines
  • Showing and Hiding Data
  • Changing the Style
  • Changing the Sparkline and Marker Color
  • Setting Sparkline Options
Lesson 6 - Showing Data as Graphic

Topic A: Conditional Formatting

  • Highlight Cell Rules
  • Using Data Bars
  • Using Icon Sets
  • Creating New Rules with Formula
Lesson 7 - Finalize Workbook

Topic A: Worksheet and Workbook Protection

  • The Protect Worksheet Option
  • The Protect Workbook Option
  • Allow users to Edit Ranges

Topic B: Managing Multiple Windows

  • The Arrange All Command
  • The Arrange Windows Dialog Box
  • The View Side by Side Command
  • The New Windows Command