Dates and location
Pricing
Hours
Dates and location
Pricing
Hours
Description
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.
Schedule
- June 23, 2025, 8:30 a.m. - 12:30 p.m.
- June 24, 2025, 8:30 a.m. - 12:30 p.m.
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.
Prerequisite(s)
This is a hands-on workshop and to be able to follow along, participants will need to bring their laptop computers with a working copy of Microsoft Excel. Microsoft Excel for PC, Version 2013 or Version 2016/Office 365 will be used in the demonstration of techniques of this workshop. Participants may use version 2010 as well with minor loss of functionality or older versions with some loss of functionality. Starter Edition will NOT be adequate for this workshop.
How to Access the Course
This course is a live webinar. You must attend the live course to receive verifiable CPD hours. We recommend you join five minutes prior to the scheduled starting time. To get the full experience of this interactive course, use a computer that has video and microphone capabilities.
Electronic material can be accessed one week prior to the course and should be downloaded in advance.
Registration, cancellation, withdrawal and all other CPA Ontario PD policies can be found here.
Speaker(s)
Paul Mascarenhas, BSc. MBA., President of Avancer Learning Inc., is a specialist in Strategic Analysis, and Communications. Paul has conducted his much sought after brand of hands-on IT Skills workshops, to globally renowned names as the Canadian Forces, Canadian Tire, Carlson Wagonlit, SKF Bearings (I) Ltd, CGI Inc, Colgate Palmolive GSSO, Tetra Pak (I), ICICI Prudential, to name a few.