LOOKING FOR RATES AND SCHEDULES
​TO JOIN A PUBLIC CLASS?
CLICK HERE.

NEED A PROPOSAL FOR COMPANY?
EMAIL [email protected]

This training is designed for Excel users who want to elevate their skills by mastering advanced functions, troubleshooting errors, and optimizing formulas for efficiency. Participants will explore logical structures, advanced lookup techniques, and powerful new MS365 functions. Additionally, they will learn how to work with PowerPivot and PowerQuery to handle complex datasets, create dynamic visualizations with advanced charts, and automate tasks using macros.

Objectives  1.  Enhance problem-solving skills in Excel by learning advanced logical structures, troubleshooting error messages, and utilizing arrays to optimize formulas.
2.  Improve data analysis and reporting capabilities through advanced lookup scenarios, PowerPivot, PowerQuery, and the latest MS365 functions.  

3.  Automate repetitive tasks and enhance efficiency by exploring macro security, recording macros, and implementing automation strategies in Excel.


Review of Excel Functions
  • Quick Review of Excel Functions
  • Troubleshooting Excel Error Messages
  • Solving Complex Problems in Excel
  • Advanced Logical Structures
    • Combining AND and OR in One Formula
    • Complex Nested IFs
    • Advanced Conditional Formatting (Formula-Based)
  • Advanced Lookup Scenarios
    • Looking Up Matrices
    • Using INDIRECT Function to Lookup Several Tabs
    • Creating Advanced Dropdowns
  • Advanced Criteria in SUMIFS, COUNTIFS, etc.
  • Dealing with Intermediate to Advanced Date Problems (Tenure, Ageing, etc.)
  • Using Arrays to Shorten Formulas
    • Array 1: Merging Ranges within a Formula
    • Array 2: Merging Formulas into one Formula
    • Array 3: Combining Criteria in SUMIFS
  • Using Wildcards to Deal with Inconsistent Arguments/Lookup Values

Using MS365 Functions in Excel
  • Shortening Formulas using LET Function
  • Using New Functions: TEXTSPLIT, IFS, XLOOKUP, etc.

Using PowerPivot to Combine Tables into One Pivot Table
  • Using PowerQuery to Extract and Fix Data from Other Sources
  • Creating Advanced Calculations in PivotTables
  • Customizing the Slicer

Exploring New Charts in Excel
  • Map Charts
  • Waterfall Charts

Introduction to Macros
  • Understanding Macro Security:
    • Exploring Macro Security Settings
  • Macro Recording:
    • Recording and Implementing Macros for Task Automation and Efficiency.

Related Courses

Advanced Excel Level 1

Extensive MS Excel

Basic MS Excel

​Macros and VBA Programming

​Excel for Data Analytics