Course Detail

Stay ahead...Enhance your technical skills through our world class training programs



Excel For Experts

This course is targeted at advanced users who require the highest level of proficiency in Microsoft Excel. Learn about creating your own custom functions, automating your spreadsheets with macros, advanced uses of Visual Basic for Applications (VBA), creating dynamic charts, working with advanced tools, etc.

Manipulating Data

  • Advanced Data Validation; including custom validation using formulas
  • Hide Formulas
  • The ‘Go to Special…’ menu
  • Database Function: DAVERAGE, DCOUNT, etc.

Formulae and Functions

  • Design Custom Reports
  • Writing Custom Formulas
  • Advanced Functions (INDEX, MATCH, SMALL, etc.)
  • VLOOKUP vs. INDEX-MATCH combination
  • Use of wildcard characters in look-up functions
  • User-Defined Functions (UDFs)
  • Work with Excel ‘Tables’
  • Wrap Formula/Expression Results

Working with Named Ranges

  • Create dynamic Named Ranges with Formulas
  • Work with Array Formulas and Functions

Advanced Charting Tools

  • Create Dynamic Charts (e.g. using one chart for varying time series, locations, etc.)
  • Use Form Controls for Charts
  • Charts with Secondary Axis
  • Link Charts to MS Word and PowerPoint, with automatic update
  • Special charts
    • Waterfall Chart
    • GANTT Chart
    • Tornado Chart

Dashboard Design

  • Dashboard Layout
  • Pre-formatting the Worksheet
  • Automating Charts with use of Form Controls:
  • Spin button
  • Option box

Formatting

  • Advanced Custom Formats
  • Apply Custom Conditional Formattingmwith Formulas
  • Create GANTT Charts with Conditional Formatting

Automation

  • Building Scenarios
  • Sensitivity Tables
  • Optimisation using Solver
  • Using Form Controls on Worksheets
    • Option Button
    • Spin Button
    • Combo Box
    • Track Changes by Multiple Users

Visual Basic for Applications

  • Introduction
  • Recording Macros
  • Assigning Macros to Form Controls
  • Editing Macros
  • Key Visual Basic commands e.g.
    • - IF…..THEN
    • DO UNTIL…….LOOP
    • DO WHILE…….LOOP
    • WITH ….END WITH
  • Working with ‘ActiveX’ Controls
  • Testing and Debugging Code
  • Designing / implementing custom Applications

We offer discounts for group bookings (i.e group of delegates attending the same course at the same time) as follows:

    3-5 participants - 5% discount
    6-9 participants - 7.5% discount
    10 or more participants - 10% discount