Course Detail

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



Intermediate/Advanced MS Excel

This course teaches participants about advanced functions, analysis tools, custom reports, professional charting techniques, automation with macros, use of form controls, etc. Participants will also learn helpful hints, tips and tricks and keyboard shortcuts.

Working with Data

  • Subtotals (including multi-level Subtotals)
  • Data Validation (including advanced Validation techniques)
  • Worksheet Protection and Security
  • Circular References
  • Creating ‘Tables’ in Excel

Formatting

  • Creating Custom Formats
    • Date and Time
    • Numbers and Text
  • Advanced Number Formats
  • Working with large numbers
  • Conditional Formatting

Formulas and Functions

  • Relative and Absolute Referencing
  • Statistical functions: COUNT, SUMIF, COUNTIF, COUNTA, SUMIFS, SMALL, etc.
  • Text functions: TEXT, LEN, TRIM, LOWER, UPPER, PROPER, etc.
  • Logical functions: IF, OR, AND, IFERROR, ISERROR, etc.
  • Date functions: TODAY, MONTH, YEAR, NETWORKDAYS, etc.
  • Look-up functions: VLOOKUP (including use of wildcard characters), LOOKUP, etc.
  • Financial functions: PMT, etc.

Introduction to Spreadsheet Modelling Techniques

  • Custom Scenario Analysis
  • Sensitivity Analysis with Data Tables

Working with Named Ranges

  • Define Named Ranges
  • Edit Named Ranges
  • Use Named Ranges in Formulas

Formulae Auditing

  • Formula Auditing (Trace, Watch, Evaluate, etc.)

Analysis Tools

  • Advanced Pivot Table techniques
  • Goal Seek
  • Data Tables

Charting Tools

  • Advanced Charting Techniques
  • Creating Custom Analysis Chart Templates
  • Creating Graphs and Charts
  • Formatting and Editing Charts

Using Form Controls on Worksheets

  • Combo Box
  • Spin Button
  • Option Button

Introduction to Macros

  • Recording
  • Editing
  • Attach Macros to Controls
  • Automation with Macros

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