Advanced Excel
This course includes:
- Downloadable Resources
- Certificate of completion
Our Intermediate Excel course is accredited by NITA
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