Complete Guide to Next Gen Excel Formulas for Data Analysts (Video)

Description

Master the future of Excel with powerful, dynamic formulas that reduce errors, perform faster, and unlock single-cell solutions.

Excel has evolved and so has the way we write formulas. This course introduces the next generation of Excel formulas, from dynamic arrays and structured references to LET, LAMBDA, and trimmed ranges. Learners will discover how to build smarter, cleaner, and more flexible Excel formulas. These modern techniques reduce the risk of errors, and are easier to read, maintain, and scale.

This course will help you move beyond traditional formulas and embrace a more elegant, powerful approach. Youll learn how to write single-cell formula solutions that respond to data changes automatically, create your own custom functions, and build reliable, maintainable formulas that do more with less.

Skill Level:

• Beginner/Intermediate
• Some Excel experience is expected.

Learn How To:

• Use Excel Tables and structured references for dynamic, resilient models
• Replace traditional formulas with spill-enabled functions like UNIQUE, FILTER, and SORT
• Shape and trim ranges using new functions like TAKE, DROP, CHOOSECOLS, and VSTACK
• Write cleaner, more maintainable formulas using LET
• Build custom functions with LAMBDA and use them across workbooks
• Perform row-wise calculations using MAP, BYROW, and SCAN
• Create single-cell reports that are robust and automatically update when data changes

Course requirement:

• Basic to intermediate knowledge of Excel
• Some familiarity with Excel functions and formulas

Who Should Take This Course:

• Job titles: Data Analysts, Business Analysts, Accountants.

About Pearson Video Training:

Pearson publishes expert-led video tutorials covering a wide selection of technology topics designed to teach you the skills you need to succeed. These professional and personal technology videos feature world-leading author instructors published by your trusted technology brands: Addison-Wesley, Cisco Press, Pearson IT Certification, Prentice Hall, Sams, and Que Topics include: IT Certification, Network Security, Cisco Technology, Programming, Web Development, Mobile Development, and more.  Learn more about Pearson Video training at  http://www.informit.com/video.

Video Lessons are available for download for offline viewing within the streaming format. Look for the green arrow in each lesson.

Downloads

Downloads

Download the resources available for this product

Sample Content

Table of Contents

Introduction

Lesson 1: Build Structured Models with Excel Tables

Learning objectives

1.1 Understand why Tables are the foundation of dynamic models

1.2 Create and manage Excel Tables

1.3 Write smarter formulas with structured references

1.4 Reference Table data like a pro

1.5 Filter faster with Slicers

1.6 Exercise: Build and analyze with Tables

Lesson 2: Master Dynamic Arrays

Learning objectives

2.1 Explore the concept of dynamic array formulas

2.2 Manage spill ranges effectively

2.3 Build solutions with dynamic array formulas

2.4 Filter data dynamically with the FILTER function

2.5 Sort data in arrays

2.6 Lookup values with XLOOKUP

2.7 Return arrays using XLOOKUP

2.8 Exercise: Extract, sort, and return

Lesson 3: Shape and Slice Arrays

Learning objectives

3.1 Use CHOOSE to specify columns in an array

3.2 Extract columns with CHOOSECOLS

3.3 Retrieve columns dynamically with MATCH

3.4 Target rows dynamically

3.5 Explore XMATCH for next-level matching

3.6 TAKE what you need & DROP what you dont

3.7 Exercise: Pick the right pieces

Lesson 4: Trim and Stack Ranges

Learning objectives

4.1 Apply the TRIMRANGE function

4.2 Use the dot operator in formulas

4.3 Stack Tables and trimmed ranges vertically

4.4 Combine data with HSTACK

4.5 Exercise: Clean and combine ranges

Lesson 5: Perform Iterative Calculations with BYROW, MAP & SCAN

Learning objectives

5.1 Aggregate values by column

5.2 Make calculations dynamic with the dot operator

5.3 Iterate down a column using BYROW

5.4 Apply the MAP function

5.5 Choose when to use BYROW/BYCOL or MAP

5.6 Format spill ranges effectively

5.7 Generate running totals with SCAN

5.8 REDUCE iterations to a single result

5.9 Exercise: Iterate and calculate

Lesson 6: Optimize Formulas with LET and Names

Learning objectives

6.1 Recognize why LET is so useful

6.2 Simplify a formula with the LET function

6.3 Apply good practices for names

6.4 Extend LET to the next level

6.5 Troubleshoot complex formulas with LET

6.6 Understand why to name a formula

6.7 Reuse calculations by naming a formula

6.8 Avoid pitfalls with named formulas

6.9 Exercise: Simplify with LET and names

Lesson 7: Create Single Cell Reports with GROUPBY & PIVOTBY

Learning objectives

7.1 Summarize data with GROUPBY

7.2 Build formula-based pivot tables with PIVOTBY

7.3 Compare GROUPBY and PIVOTBY

7.4 Add subtotals to your reports

7.5 Format GROUPBY/PIVOTBY results

7.6 Stack multiple aggregation functions

7.7 Filter report results dynamically

7.8 Use Slicers with GROUPBY and PIVOTBY

7.9 Exercise: Build a formula-driven report

Lesson 8: Create Custom Functions with LAMBDA

Learning objectives

8.1 Explore the LAMBDA function with an example

8.2 Convert a formula into a custom LAMBDA

8.3 Save and manage named LAMBDA functions

8.4 Use your custom LAMBDA function

8.5 Modify an existing LAMBDA

8.6 Share LAMBDA functions across workbooks

8.7 Exercise: Create and reuse your own function

Summary

Updates

Submit Errata