Skill Level
Advanced
Duration
18 Hours
Language
English

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 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.

Completing this course will help you with:

  • Data cleansing techniques.
  • Prepping your data for analysis.
  • Data mining and analysis techniques.
  • Visual presentation of data (charts and pre built conditional formatting).

Course Prerequisites

The learner should be an experienced Excel user and MUST be well conversant with our Intermediate Microsoft Excel concepts covered in the previous module.

Learning Path

  • Avoid typing a column index number in VLOOKUP by combining VLOOKUP + MATCH functions.
  • Say goodbye to VLOOKUP and hello to INDEX + MATCH for two-way (matrix) lookups, as well as a right-to-left lookup.
  • Applying logical functions (IF…AND/OR) to make logical business decisions and segment data.
  • Using Text functions (LEFT, RIGHT, TRIM, CONCATENATE, LEN, FIND, PROPER, UPPER, LOWER) to return the number of specified characters from a given text or join two/more text strings; Text to column feature to parse data.
  • Nested IF function for data segmentation.
  • Retrieving and reconciling data made easier by use of VLOOKUP.
  • 3D Consolidation or Data Consolidation by position.
  • Working with INDIRECT to consolidate data from multiple
    worksheets.
  • Conditional aggregation with multiple criteria using SUMIFS, COUNTIFS & AVERAGEIFS.
  • Summarizing data using PivotTables.
  • Interactive filters using Slicers and Timelines:
    ᵒ Change the calculation & display options e.g., show as %, running totals, YoY%, etc.
    ᵒ Group dates into months and quarters with a single mouse click.
    ᵒ Show top views, i.e. top/bottom performing products/sales agents/customers.
    ᵒ Build a simple but interactive dashboard using pivots.
  • Sensitivity (“What if”) analysis on models using Data Tables, Goal
    Seek.
  • Customized Actual versus Budgets charts.
  • Special charts focus on waterfall chart.
  • Make reports more insightful with formula driven conditional formatting:
    • Track performance towards a goal (green for good, amber for
      average and red for poor).
    • Highlight entire row based on a single value.
    • Highlight upcoming due dates or overdue bills/premiums.
  • Sparklines to show trends.

What people are saying

Are You Ready To Get Started?

Course Categories

Other Courses you may be interested in