Dynamic Arrays: Excel Turbo-Charged

Course

Rapid change, increased demands for what-if modelling, and not enough hours in the day? Dynamic arrays can simplify, accelerate and transform your analysis work. No more Control, Shift, Enter! No more implicit intersection! Arrays are now dynamic; they can spill into adjacent cells and be used with multiple formulas. 

Join Excel guru, Dianne Auld, for this introduction to the new dynamic array functionality introduced to Office 365/Microsoft 365 Excel users in 2020. Dianne will reveal the power of Excel right at your fingertips. Learn how to transform your dashboard with FILTER, SORT, SORTBY and UNIQUE. Discover how the new XLOOKUP is a real gamechanger and helps you avoid common VLOOKUP errors. Together, we will explore how to leverage these features to help you work easier and smarter.

This class will feature many astonishing applications of these new formulas to transform your rewards work resiliently. 

  • Learn how to use one simple INDIRECT formula to bring in an entire set of regional pay ranges based on user choice.
  • Discover the syntax of XLOOKUP and its advantages over the traditional VLOOKUP formula.
  • Find out how to use XLOOKUP to return a dynamic array of market data, looking left, right, or using wildcards, and how to add an “if not found” clause to the formula.
  • Explore how to use the new dynamic array formulas SORT, SORTBY, FILTER and UNIQUE to create dynamic rewards dashboards.
  • Learn how to use the dynamic array reference notation in formulas and data validation lists.
  • Examine how to use the new dynamic array formula SEQUENCE to easily set up a range of numbers, letters or dates for use with rewards work.

PLUS, all attendees will receive the Excel spreadsheets and detailed instructions for replication of the functions demonstrated after the presentation.

What You Will Learn:                         

  • Dynamic arrays terminology and how dynamic arrays have changed the calculation engine of Excel
  • How to use dynamic arrays together with the INDIRECT formula to bring in ranges of data and avoid cumbersome nested IF formulas
  • The syntax of the new XLOOKUP formula, how this one formula can replace VLOOKUP, HLOOKUP, LOOKUP AND IFERROR, and how it avoids three of the common VLOOKUP errors
  • How to use XLOOKUP to return a dynamic array of market data, looking left, right, or using wildcards, and how to add an “if not found” clause to the formula
  • How to use the UNIQUE, SORT, SORTBY and FILTER Formulas to create dynamic data validation lists and rewards dashboards

Learning Options

Virtual Classroom

Live online instructor-led delivery of online course materials including real-time interaction with a subject-matter expert and peers — no travel required!

Course

Member: $395 USD

$525 USD

Instructor

Dianne Auld

Reward Consultant
Auld Compensation Consulting

Dianne Auld, CCP, GRP, CSCP, WLCP owns a consulting practice, Auld Compensation Consulting, in Cape Town, South Africa. . She consults to a wide range of organizations globally in all areas of total rewards. She has developed and taught courses in Africa, the Middle East, Asia, Europe, and the United States.

Dianne is also a faculty member at WorldatWork and a reviewer for the WorldatWork Journal. Well respected and known as an expert in the compensation field and Excel, Dianne's skills and expertise are in high demand. In fact, her sessions at the WorldatWork Total Rewards Conference and Exhibition typically fill to capacity. She is the author of the popular Dianne Auld’s Excel Tips, third edition.

Dianne has partnered with WorldatWork to develop several educational products to assist compensation professionals with their day-to-day tasks in Excel, including "Working Smarter and Faster with Excel Formulas, Charts and ASAP Utilities,” “Regression Analysis Made Easy with Excel,” “Hello Excel Power Query, My New BFF”, "Excel Skills for Compensation Professionals," and "Advanced Excel Skills and Dashboards for Compensation Professionals."

Course Credits & Certifications

  • Recertification
    .25 Credits

More Information

Who Should Register

Compensation practitioners or compensation managers with at least intermediate Excel skills.

Requirements

You should have a computer with Microsoft Excel 2010 or later in order to use the modeling tools and formulas demonstrated in class.

How to Register

Policies

Click Here to view our policies on payments, returns, class schedules, registration, cancellation, and more.

Have Questions?

Phone

+1 877 951 9191

USA and Canada

+1 480 951 9191

Other Countries

Online

Email Us