EVENT DESCRIPTION

Summer Sale Until September 22

This newly revised course has shifted online into two, half-day, live, instructor-led sessions. We recognize that online learning a vastly different experience from an in-person seminar and our instructors have adapted the interactive sessions to include regularly scheduled breaks and digital breakout rooms for group discussions. Our goal is to make your experience more convenient as we navigate the new reality of virtual learning.

SCHEDULE

  • Thurs, Aug 6 @ 8:30am - 12:30pm
  • Fri, Aug 7 @ 8:30am - 12:30pm
  • The wide range of Functions available in Microsoft Excel provides wonderful avenues for solving complex analytical, planning and reporting tasks dynamically. Using functions well can enhance output, improve productivity greatly, reduce manual activity make short work of data-intensive tasks. Participants will work hands-on along with the facilitator on a practice data-file with supplied examples that include single variable lookups, two-variable lookups, dynamic formulas and rolling reports, conditionality and data slicing, error trapping in lookup-based formulas, single cell array formulas, multi-cell array formulas, using array constants in formulas, and critical formula concepts like naming, using complex criteria in logical expressions, and other concepts for building large and complex formulas easily.

    TOPICS INCLUDE:

    • Understand how to use names and explore useful techniques with names for building complex formulas easily and effectively
    • Introduction to Array formulas and Array constants for flexibility in complex analysis and summarizations of data that use multiple calculations in one formula
    • Go beyond traditional lookup formulas and explore two-variable lookups, going beyond limitations of vlookup with functions such as INDEX and MATCH, error trapping using information and logical functions and more
    • Explore vector-based single-variable lookups and how it can work where traditional lookups are cumbersome
    • Understand Data Slicing and complex calculations using functions like SUMPRODUCT with complex criteria
    • Explore logic and logic expressions and how to craft combined logical expressions and use them for data analysis within Math functions for data analysis
    • Exploring new and important formula functions – get an introduction to some of the powerful new functions in version 2013 & 2016 including IFS, TEXTJOIN, CONCAT, MAXIFS, MINIFS and more
    • Advanced Formulas Tips and Tricks that include dynamic referencing, dynamic arrays (if available), named formulas and more

    WHO WILL BENEFIT:

    Those using large datasets and need to build complex formulas for data analysis, advanced lookup calculations, bring dynamism into their spreadsheets and those whose calculations span multiple steps.