×

# Excel Magic 1: Building Dynamic Formulas

###### 125 Enrolled
1.5 Hours (On-Demand)

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()
• 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: March 23, 2024

#### Reviews

5
This was excellent. I consider myself to be pretty adept at excel. I've had challenges understanding, indirect, index/match and sumproduct. The program spelled them out in a way that made it logical and very applicable for future use. I also liked the tips on summing among different tabs. This was a very practical course.

4
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
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
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
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.

5
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
Lenny Wu does a great job explaining more complex formulas. This course provides a lot of great tips as well as good explanations of complex formulas like Offset. I enjoy his courses.

5
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
Glad to have found this series of courses. The presentation is both detailed and easy to understand. Will be completing more of these courses.

5
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
I love all the examples. It really brings the formulas to life and helps me see how I can implement them in my work life. Thank you!

5
Great Excel Instructor, whit good slides and nice size videos that allow you to replayy when you want to refresh your memory.

5
I liked the course. I enjoyed the offset part that I think could be useful in making YTD formulas more automated.. Thanks

5
Really easy to understand and follow the examples and the practice sheets. Had to pause quite often to verify my formulas

5
Two exam questions were super confusing. Not well written. #2 and #4, other than that, was an excellent course

5
Well explained from Basics to Advanced with Excel365 new functions. Practical and relevant course.

5
Great tools for Excel 365, even though I don't have, will look into getting it.

5
A great course for in-depth Excel formulas. The instructor is great as well.

5
This is a really useful course - essential for all who regularly use excel.

5
Excellent information presented in this course. The pace was great.

5
Very informative and useful in my job. Lenny is an Excel wizard

##### Education Provider Information
Company: Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
##### Course Syllabus
INTRODUCTION AND OVERVIEW
Introduction to Excel Magic: Building Dynamic Formulas
Recap of Prior Excel Course: Top Five Excel Skills
Hierarchy of Excel Modeling Techniques
Instructor Introduction
Comparison with Other Similar Courses
What You Will Get from This Course
Dynamic Formulas - Basic
Introduction to Dynamic Formulas
References: Absolute vs relative reference
Conditional formulas: IF()
Table formula
Dynamic Formulas - Intermediate
Lookup formula: INDEX/MATCH
Lookup and sum formulas: SUMPRODUCT/SUMIF/SUMIFS
Multi-tab formula: SUM(START:END!)
Link to Pivot Table formula: GETPIVOTDATA()
Dynamic range formula: OFFSET
Array formulas
New Feature – Excel 365: Dynamic Array formulas
Dynamic Array Formulas (Excel 365): SORT
Dynamic Array Formulas (Excel 365): XLOOKUP
Dynamic Array Formulas (Excel 365): Spill Range
Conclusion
Takeaways
New Course and Q & A
CONTINUOUS PLAY
Excel Magic: Building Dynamic Formulas
SUPPORTING MATERIAL
Slides: Excel Magic: Building Dynamic Formulas
Excel Magic: Building Dynamic Formulas Glossary/ Index
Workbook: Excel Magic: Building Dynamic Formulas
REVIEW AND TEST
REVIEW QUESTIONS
FINAL EXAM