Working professionals often have to organize and summarize mountains of data into presentable formats to identify key trends, offer comparisons and reveal patterns and relationships to answer some key questions such as sales performances for various regions, average class size per month, which factory is producing better, etc.

In this module, you will find out the available advanced data analysis tools in Excel that will develop your skills to make yourself more marketable in the field of data analysis.

In this module you will learn:

  1. Advanced data manipulation techniques to prepare your data for analysis
  2. Advanced data analysis techniques
  3. Advanced charting techniques

The learner is assumed to be well oriented with the intermediate level principles in the previous module.

  • Converting unusual date formats to proper dates in Excel -applying TEXT and DATE functions
  • Flexible lookup techniques –learn how to combine VLOOKUP with MATCH function for dynamic data analysis. No more manually typing which column in your function; Lookups involving multiple sheets
  • Nesting the MATCH function inside the INDEX function for flexible huge data analysis
  • Advanced logic in a business context (e.g. working out bonus payments linked to performance) – IF, AND, and OR functions
  • Practical applications of Pivot Tables in business
    • Grouping days invoice outstanding to segment them into ageing clusters
    • Interactive reports using Pivot Charts, slicers, and timelines; Connecting multiple reports to a single slicer
    • Creating custom-styled, professional slicers that match your brand
    • Using calculated fields to generate an income statement, compute variance analysis, calculate margins, etc.
    • Highlight pivot table results with conditional formatting
  • Conditional aggregation of data (make dynamic summary tables) –SUMIFS, COUNTIFS, AVERAGEIFS
  • What-if analysis –creating sensitivity tables
  • Highlighting the maximum or minimum points in a chart
  • Learn how to use conditional formatting to make key data stand out -apply custom formulas
  • Creating bullet charts for performance tracking
  • Custom number formats
  • Save a chart as a template

No Events on The List at This Time

Reserve your seat now

What you get:

  • 12 hours instructor-led, practical sessions

  • Comprehensive study materials

  • After-course instructor support

  • Certificate of participation

Do you have 8 or more staff who could benefit from this program?

Run this course in-house