Microsoft® Office - Excel 2016 Advanced Level

Advanced Level. Maximum of 8 Delegates. Spearhead will provide laptops for all delegates attending this course.

Microsoft® Office – Excel 2016 Advanced Level

2 day(s)

From: AED 2,600.00


Who Should Attend?

This course is ideal for those who are comfortable with Microsoft® Excel and would like to explore the advanced features offered in Microsoft® Excel 2016. Users who have a good working knowledge of Microsoft® Excel, and those who are keen to further their working knowledge of Microsoft® Excel, would benefit greatly from this course.

About The Programme

This two-day programme, covers the more advanced levels of Microsoft® Excel 2016. Delegates who feel they already have a very good working knowledge of Microsoft® Excel, or are familiar with some Day One topics, should view Day One as a ‘refresher’.

Course Objectives

  • To understand the advanced functions of Microsoft® Excel 2016, and how it is different from the previous versions
  • To be able to use Date and Time, Logical, Statistical, and Lookup and Reference functions
  • To be able to create more complex formulas
  • To proficiently use Advanced Charting and the new charts introduced in Excel 2016
  • To understand how to create Power Maps
  • To be able to forecast using the Forecast Sheet tool
  • To be able to consolidate data
  • To be able to gather and shape data for analysis using Get and Transform
  • To be able to create relationships between multiple datasheets and analyse the combined data using PivotTables and PivotCharts
  • To understand and utilise advanced data management tools like What-If Analysis
  • To be able to secure, protect, and customise your workbook
  • To understand how to create and run macros

What You Will Gain

  • An advanced working knowledge of Microsoft® Excel 2016
  • An in-depth understanding of how to work with complex and unfamiliar Functions
  • An understanding of how to create charts and graphs which are available in Microsoft® Excel 2016
  • An awareness of the various analysis tools available in Excel and how to use them
  • A list of keyboard shortcuts for frequently used tools, and how to use them to increase speed
  • Knowledge of how to protect your worksheets and data
  • Knowledge of how to record frequently used functions using Macros
  • Knowledge of how to create various reports from your data, using tools like PivotTables and PivotCharts

Programme Content


  • Defining Cell And Range Names
    > Understanding Absolute, Mixed, And Relative Cell References
    > Naming Cell Ranges
    > Creating Multiple Names From A Selection
    > Inserting Names In A Formula
    > Managing Names To Edit And Delete
  • Using Formulas And Functions
    > Working With The Order Of Operations
    > Inserting A Function
    > Understanding The Syntax Of A Function
    > Date And Time Functions
    > Statistical Functions
    > Logical Functions
    > Nested Functions
    > Lookup Functions
    > Text Functions
  • Working With Data Tools
    > What-If Analysis Tool
    > Forecast Sheet
    > Customising The Sort Function
    > Adding And Rearranging The Level To Sort By
    > Sorting According To A Custom List
    > Using Advanced Filter
    > Creating A List Of Unique Values
  • Creating And Working With Tables
    > Using Tables To Build Relationships And Update Reports
    > Connecting Data From Multiple Sources
    > Transforming The Data To Meet Your Specifications
    > Combining Data By Creating Relationships


  • Chart Basics
    > Understanding The Use Of The New Chart Types Offered In Excel 2016
    > Combination Charts
    > Using PowerMaps
    > Using Sparklines To Understand Trends
  • Consolidating Data
    > Combining Data From Various Cells Into A Single Cell
    > Consolidating By Position, By Category, By Using 3D Formulas And By Using The PivotTable Wizard
  • Inserting PivotTables And Charts Into Your Excel Sheets
    > Creating A PivotTable
    > Using The Recommended PivotTables Option
    > Applying Conditional Formatting In PivotTables
    > Adding Fields To A PivotTable
    > Changing The Data Source
    > Refreshing The Data In A PivotTable
    > Grouping Data
    > Collapsing And Expanding Fields
    > Using Slicers And Timelines To Filter PivotTable Data
    > Creating Multiple Worksheets From Report Filter Pages
    > Using The Drill Down Feature
    > Build Relationships To Analyse Multiple Tables Together
    > Inserting PivotCharts
  • Advanced Data Management
    > Defining Data Entry Rules To Prevent Incorrect Information Being Entered
    > Setting Date And List Validation Rules
    > Circling Invalid Data
  • Protecting Your Workbook
    > Customised Protection For Your Worksheet To Allow Selective Editing
    > Encrypting Your Workbook With A Password
    > Adding A Password Protection To Protect Your Workbook Structure
    > Finalising Your Workbook As ‘Read Only’
  • Macros
    > Recording Macros To Speed Up Frequently Performed Actions
    > Editing And Deleting Macros
    > Copying VBA Code
    > Assigning Shortcut Keys, Buttons Or Controls To Recorded Macros
  • Creating Forms
    > Understanding The Various Form Controls And ActiveX Controls In Excel
    > Inserting And Configuring The Properties Of The Form Controls

Before the course each delegate will be asked to complete a Pre-Course Questionnaire to determine their individual objectives for attending the course. These objectives will be used by the Tutor to give on-target training that is focused on the individual delegates.

At the end of the course each delegate will be asked to complete a Personal Development Plan that can be used as part of future appraisals, and that will also be an important tool for management reference.

Total Investment: AED 2,600/- + VAT, which includes – Training Materials, Lunch and Refreshments. Discount available for multiple bookings.

From: 8th November 2021

To: 9th November 2021

Duration: 2 day(s)

Location: Dubai

Microsoft® Office – Excel 2016 Advanced Level

2 day(s)

From: AED 2,600.00