EMFADCourses
Basket
Instant access by email once payment is confirmed14-day refundSecure checkoutSupport by email, phone and WhatsApp

Data Science and Machine Learning

Advanced Excel: Dynamic Arrays, Power Query and Data Models

Build self-updating Excel reports with dynamic array formulas, LET and LAMBDA, refreshable Power Query transformations and a related Data Model with DAX measures.

Level
Intermediate
Format
Written, self-paced
Length
About 6 hours
Lessons
15
Modules
4
Certificate
Yes, verifiable
Last updated
October 2026

About this course

Go beyond lookups and PivotTables with dynamic array formulas, LET and LAMBDA, repeatable Power Query transformations and the Excel Data Model.

Self-paced written course: text lessons, practical exercises, module quizzes and a final project, delivered online. No video and no live tutor.

What you will learn

  • Write dynamic array formulas with FILTER, SORT, UNIQUE, SEQUENCE, CHOOSECOLS and TAKE
  • Simplify complex formulas with LET and create reusable custom functions with LAMBDA
  • Split, combine and clean text with TEXTSPLIT, TEXTBEFORE, TEXTAFTER and TEXTJOIN
  • Combine, merge, append and unpivot data in refreshable Power Query workflows
  • Model related tables in the Excel Data Model and write introductory DAX measures
  • Build and test an automated monthly reporting workbook with built-in checks

Curriculum

Module 1Dynamic Array Formulas4 lessons
  1. 1How Spill Ranges Work3 min read Free preview
  2. 2FILTER, SORT and UNIQUE in Practice3 min read
  3. 3SEQUENCE, CHOOSECOLS and TAKE for Reshaping Data3 min read
  4. 4Building Live Reports with Dynamic Arrays3 min read
  5. QuizModule quiz, 4 questions
Module 2Formula Engineering4 lessons
  1. 5Cleaner Formulas with LET3 min read
  2. 6Reusable Custom Functions with LAMBDA3 min read
  3. 7Text Splitting and Combining with TEXTSPLIT and TEXTJOIN3 min read
  4. 8Error Handling and Formula Auditing3 min read
  5. QuizModule quiz, 4 questions
Module 3Power Query Beyond the Basics4 lessons
  1. 9Combining Files from a Folder3 min read
  2. 10Merging and Appending Queries3 min read
  3. 11Unpivoting and Reshaping Messy Data3 min read
  4. 12Parameters and Refreshable Workflows3 min read
  5. QuizModule quiz, 4 questions
Module 4Data Models and Automation3 lessons
  1. 13Relationships and the Excel Data Model3 min read
  2. 14Introductory DAX Measures in Power Pivot3 min read
  3. 15Course Project: Automated Monthly Reporting Workbook3 min read
  4. QuizModule quiz, 4 questions

Final project

Automated Monthly Reporting Workbook. A practical brief with ordered steps, what to produce and how to check your own work. You confirm it in your learner area.

Who it is for

  • Analysts who already use lookups and PivotTables every day
  • Accountants and finance officers who prepare recurring reports
  • Administrators who consolidate data from several files or systems

Not for you if you are completely new to the subject. Start with a beginner course in data science and machine learning first.

Requirements

  • Microsoft 365 Excel; Excel for Windows is needed for the Power Pivot steps
  • Comfort with XLOOKUP or VLOOKUP, SUMIFS and PivotTables
  • A computer where you can create folders and save practice files

What is included

  • 15 written lessons
  • 15 practical exercises
  • 4 module quizzes
  • A final project
  • Certificate of completion with a verification code
  • Lifetime access to what you buy

Questions about this course

What do I need before I start?

Microsoft 365 Excel; Excel for Windows is needed for the Power Pivot steps. Comfort with XLOOKUP or VLOOKUP, SUMIFS and PivotTables. A computer where you can create folders and save practice files.

What device do I need?

A phone, tablet or computer with a modern web browser and an internet connection. Lessons are written, so they work on slow connections.

How long do I have access?

Lifetime access to the full course or to any module you buy. All Access and Teams give 12 months of access to every course.

Can I get a refund?

Yes: a full refund within 14 days of purchase if you have completed less than 30% of the course (or of the module, for a single module). Read the refund policy.

Should I buy a module or the full course?

A module (USD 10) unlocks that module's lessons and quiz. The full course (USD 59) unlocks everything and leads to the certificate. If you buy modules first, each one you own is credited at USD 10 when you buy the full course.

Which currency will I pay in?

US dollars (USD). Prices include all taxes where applicable. Your bank may apply its own conversion fee. Card payment is being set up with our bank. After you place your order we email you payment instructions within one working day; your access starts as soon as payment is confirmed.

Data Science and Machine Learning learning path

All 12 open courses in this subject for USD 319. Bought separately they cost USD 538, so you save USD 219.

See the path

More in Data Science and Machine Learning.

All data science and machine learning courses

USD 59Full course