Advanced Excel

This course includes:

Our Intermediate Excel course is accredited by NITA

nita-logo

Course Information

Overview

This course is tailored for those faced with the task of analyzing large, and usually unstructured data sets to provide useful insights for decision making. It is for experienced Excel users tired of VLOOKUP shortcomings while consolidating data and is looking for flexible methodologies to analyze, visualize and present reports. Managers and Executives who manage, analyze, and prepare reports for managerial discussions will find this course most beneficial.

Regular Excel users competent with our Intermediate Excel concepts.

Advanced Data Manipulation Techniques

  • Avoid typing a column index number in VLOOKUP by combining VLOOKUP + MATCH
    functions.
  • Say good-bye to VLOOKUP and hello to INDEX + MATCH for two-way (matrix) lookups, as well
    as a right- to-left lookup.

Methods of Summarizing Data

  • Data Consolidation by position.
  • Working with INDIRECT to consolidate data from multiple worksheets.
  • Conditional aggregation with multiple criteria (SUMIFS, COUNTIFS & AVERAGEIFS)

Sensitivity Analysis & Modeling in Excel

  • Sensitivity (“What-if”) analysis on models using Data Tables, Goal Seek.
  • Laying out a model in Excel

Methods of Summarizing Data

  • Maximizing on the power of PIVOT-TABLES.

Visual Reporting & Interaction

  • Make reports more insightful with formula-driven Conditional Formatting.
  • Spark lines to show trends.
  • Combine SUMIFS with pick lists, for data slicing and creating interactive charts.
  • Introduction to form controls (option buttons for report interaction).

Target Group

  • Analysts
  • Finance Professionals
  • Executives
  • HR Professionals
  • Procurement specialists

Expected Outcome

  • Apply advanced lookups on
    data extracted from external
    sources.
  • Use advanced techniques for report visualizations.
  • Leverage on various
    methodologies of
    summarizing data
  • Understand and apply basic principles of laying out Excel models for decision making

or Call us

0716 568899

Fill out your contact details below so we can get in touch with you regarding your training requirements.