Sunesis Consulting

  • Bring your data to life by telling a compelling story
Join the class
Microsoft Store

Advanced Excel

  • Duration - 18 hours / 3 days
  • Level - Advanced
About This Course

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.

What you will learn
  • Advanced data manipulation techniques to prepare your data for analysis
  • Advanced data analysis techniques
  • Advanced visualization/presentation techniques
Course Prerequisite
  • The learner should be an experienced Excel user and MUST be well conversant with Intermediate Excel concepts covered in the previous module

Curriculum

3 Key Lessons / Online, In-person
  • 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.
  • Applying logical functions (IF…AND/OR) to make logical business decisions and segment data.
  • 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
Share This :
Preview This Course

$39.99 / Until February 2021

Join Course

This Course Includes

  • Language - English
  • Downloadable Resources
  • Certificate of Completion

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