4 Advanced

Intermediate Excel

Our intermediate course allows delegates already exposed to Excel to the required fundamentals that should be established in any Excel user.

On the 2nd day the delegates can continue at a comfortable pace using Excel with the teachers support to work through typical examples. This helps instils the valuable lessons learned.

Purpose of Excel

  • The fundamental goals of Excel
  • Initial insight into Excel’s “Brain”
  • File formats and file extensions

Navigation and structure

  • Navigation and helpful shortcuts
  • Worksheet grid and an initial look at Named Ranges
  • Merged cells and the respective risk

Data Management within Excel

  • Data types
  • Operators and Operandums
  • Coercion vs. functions
  • Implement balancing checks and flags
    1. Using conditional formatting
    2. Number formatting
    3. Error tracking

Data Management with files outside of Excel

  • Sharing a workbook and the risks
  • Automate data import using queries
  • First look at Power Query

Adjusting & Customizing

  • The effects of altering the Dependency tree
  • Inserting objects such as Microsoft Word documents or shapes
  • Simple use of Named ranges in Data Validation to deliver drop down lists
  • Styles

Excel’s Table objects

  • Understanding the table object
  • Table Formulae syntax

Intermediate Formula

  • Explore a collection of useful functions
  • Learn a structured approach to breaking-down and understanding complex formulas
  • Locate and handle formula errors; and discover the value of unhandled formula errors such as #N/A
  • Using Boolean logic to deliver digital results
  • Lookups: Binary lookup vs. Linear Search in VLookups and become able to improve lookup performance by an average of 400%. Also, discover a variety of other useful applications of binary searches.

Pivot Tables

  • Creating Pivot Tables instead of formulae-based summaries
  • Drill down feature
  • Pivot Table Slicers & Reporting Connections
  • Interactive Pivot Charts

Output & Protection

  • Setting print ranges and printing options
  • Understand the concepts of good charting principles and learn how to determine the appropriate chart type best suited to represent the data
  • Create mix chart types and use a secondary axis
  • Sparklines
  • An initial look at cell, worksheet and workbook protection

Advanced Formula

  • Index & Match
  • Dynamic ranges
  • Array formulae

Initial look at VBA

  • Recording a macro
  • Basics to the coding environment
  • Understanding classes, modules, objects & properties.

Basic VBA scripting

  • Setting a variable
  • Setting an object
  • Destroying an object
  • Displaying a message


Comments are closed.