Instructor for this course
more

This course is presented in four parts.

First, the course relates earlier course of Top 5 Excel Skills to drive the concept home: Dynamic formulas are your “first” step toward full automation in Excel Modeling!

Second, the course brings the topic of what is a dynamic formula and let students consider if VLOOKUP() is a dynamic formula? It is only a semi-dynamic formula.

Third, we illustrate the top 10 dynamic formulas, including:

  • Dynamic range formula
  • Table formula
  • Conditional formula
  • SUMPRODUCT()
  • INDIRECT/ADDRESS
  • Array formula

Last, the course explores latest features from Excel 365 that are considered by many to be amazing. They include:

  • SORT()
  • XLOOKUP()

Course Key Concepts: Dynamic formulas, Array formulas, Dynamic array formulas, Absolute reference, Relative reference, Spill range.

The course relates Excel Magic courses series. 'Building Dynamic formulas' is the “first” step toward full automation in Excel Modeling.

Learning Objectives

  • Discover and understand the difference between a simple formula and a dynamic formula.
  • Identify 2 elements to make a dynamic formula.
  • Recognize top 10 dynamic formulas covered in this course.
  • Discover 3 advanced dynamic formulas that make Excel modeling super easy.
  • Explore new features on dynamic array formulas that Excel 365 brings.
Last updated/reviewed: June 16, 2021

13 Reviews (32 ratings)Reviews

5
Member's Profile
I consider myself an excel power user and I found some new things in this. The only thing that bothered me was the use of CountA vs count which I think would reduce the risk of errors when he was showing the offset formula.
4
Anonymous Author
Good course overall, I definitely learned things. I feel like the same amount of time was given to some of the simpler things as was to the harder ones, though. For example, indirect/address seemed to go really fast. You could almost have a course on each of those later ones. Good job though.
5
Member's Profile
This course was very thorough in teaching someone how to not only understand the underlying principles of commonly used and more complex formulas, but also a lesson in how to construct a workbook so that less manual manipulation and user error occurs.
5
Anonymous Author
Instructor did a great job walking through the material. When the advanced formulas were covered, these a little difficult to for me to follow. I expect to go back and review those sections, but this was not the fault of the instructor.
5
Anonymous Author
I have developed my excel via Lee presentations. He is an extraordinary and i know once i repeat 3 to 4 times its becomes by own. Soon these courses will transform me to supersonic in excel
5
Anonymous Author
I am an advanced Excel user, but there is always something to learn. Lenny does a great job in explaining these more advanced features in Excel.
5
Anonymous Author
Some good tips for quicker formulas to use. Good intermediate suggestions and principles for how not to use formulas that only work one time.
5
Member's Profile
Great Excel Instructor, whit good slides and nice size videos that allow you to replayy when you want to refresh your memory.
5
Member's Profile
I liked the course. I enjoyed the offset part that I think could be useful in making YTD formulas more automated.. Thanks
5
Member's Profile
Two exam questions were super confusing. Not well written. #2 and #4, other than that, was an excellent course
5
Member's Profile
Well explained from Basics to Advanced with Excel365 new functions. Practical and relevant course.
5
Anonymous Author
Great tools for Excel 365, even though I don't have, will look into getting it.
5
Anonymous Author
Excellent information presented in this course. The pace was great.

Prerequisites

Education Provider Information

Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Syllabus
INTRODUCTION AND OVERVIEW
  0:27Introduction to Excel Magic: Building Dynamic Formulas
  0:40Recap of Prior Excel Course: Top Five Excel Skills
  1:55Hierarchy of Excel Modeling Techniques
  1:13Instructor Introduction
  1:28Comparison with Other Similar Courses
  0:38What You Will Get from This Course
Dynamic Formulas - Basic
  2:30Introduction to Dynamic Formulas
  4:26References: Absolute vs relative reference
  3:10Conditional formulas: IF()
  3:20Table formula
Dynamic Formulas - Intermediate
  4:46Lookup formula: INDEX/MATCH
  3:43Lookup and sum formulas: SUMPRODUCT/SUMIF/SUMIFS
  4:29Multi-tab formula: SUM(START:END!)
  4:00Link to Pivot Table formula: GETPIVOTDATA()
Dynamic Formulas - Advanced
  7:13Parameterized formulas: INDIRECT/ADDRESS
  9:22Dynamic range formula: OFFSET
  3:54Array formulas
New Feature – Excel 365: Dynamic Array formulas
  4:12Dynamic Array Formulas (Excel 365): SORT
  3:55Dynamic Array Formulas (Excel 365): XLOOKUP
  1:58Dynamic Array Formulas (Excel 365): Spill Range
Conclusion
  1:20Takeaways
  0:28New Course and Q & A
CONTINUOUS PLAY
  1:09:03Excel Magic: Building Dynamic Formulas
SUPPORTING MATERIAL
  PDFSlides: Excel Magic: Building Dynamic Formulas
  PDFExcel Magic: Building Dynamic Formulas Glossary/ Index
  XLSMWorkbook: Excel Magic: Building Dynamic Formulas
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM