1.5 CPE 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: March 23, 2024
25 Reviews (73 ratings)
Reviews
Anonymous
(Inventory Accounting Manager)
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.
Anonymous
(Consultant)
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.
Rebecca Strickland
( at Alexium International)
This course covered several formulas that I did not know previously. I found the material very useful and practical. I would have appreciated a little more defining of the elements of the formula (ex. "lookup array, return array) to help me better understand what the formulas were asking for.
Harris Kirson
(Senior Analyst at ACG Professionals)
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.
Anonymous
(AVP, Corporate Tax Manager)
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.
Blaine Browning
(Director of Technical Accounting and SEC.. at ZoomInfo)
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.
Anonymous
(Head of Finance)
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
Anonymous
(Accountant)
Thank you, Lenny for another excellent course. Please consider publishing in pdf for purchase. There is so much useful information & it is easier to have printed version for a reference.
Anonymous
(Accountant)
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.
Anonymous
(Accountant)
I enjoy Lenny's personality. He shows good examples. I just need to practice in order for these to stick and to see where I have business use for some of these.
Anonymous
(Controller)
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.
Jared Slack
(VP Finance at Fortress Solutions)
Glad to have found this series of courses. The presentation is both detailed and easy to understand. Will be completing more of these courses.
Anonymous
(Accountant)
Some good tips for quicker formulas to use. Good intermediate suggestions and principles for how not to use formulas that only work one time.
Paul Francom
( at N/A)
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!
Sandra Heelan
(CPA, CFE, MBA at None)
Great Excel Instructor, whit good slides and nice size videos that allow you to replayy when you want to refresh your memory.
John Watkins
( at GSMS Inc)
I liked the course. I enjoyed the offset part that I think could be useful in making YTD formulas more automated.. Thanks
Anonymous
(Plant Controller)
Really easy to understand and follow the examples and the practice sheets. Had to pause quite often to verify my formulas
bev stenger
(CPA owner at Stenger Consulting)
Two exam questions were super confusing. Not well written. #2 and #4, other than that, was an excellent course
Anonymous
()
An array formula is a formula that can perform multiple
calculations on one or more items in an array
Ramakrishna Nalluri
(Senior Financial Analyst at LifeWorks Inc)
Well explained from Basics to Advanced with Excel365 new functions.
Practical and relevant course.
Anonymous
(Consultant)
Great tools for Excel 365, even though I don't have, will look into getting it.
Anonymous
(Sr Accountant Lead)
A great course for in-depth Excel formulas. The instructor is great as well.
Matt Parker
( at WCF Insurance)
This is a really useful course - essential for all who regularly use excel.
Anonymous
(Accounting Manager)
Excellent information presented in this course. The pace was great.
Anonymous
(Consulting CFO)
Very informative and useful in my job. Lenny is an Excel wizard
Prerequisites
Course Complexity: Foundational
Basic Excel knowledge; Example: be able to open one Excel file and connect to external data files, etc.
It is recommended to take other Excel Magic series courses by Lenny Wu.
Excel Magic 1: Building Dynamic Formulas
Excel Magic 2: Building Your Report Generation "Bot"
Excel Magic 3: Automating Data Entry with Data Feeds
Excel Magic 4: Ultimate API Reporting in Excel
Excel Magic 5: Building Interactive Budgeting Model in Excel
Excel Magic 6: Excel's 7 Languages
Excel Magic 7: Creating Excel Charts like a PRO
Excel Magic 8: Transforming a Pro-forma into a Dashboard
Excel Magic 9: Building a Project Tracking “Gantt” Chart
Basic Excel knowledge; Example: be able to open one Excel file and connect to external data files, etc.
It is recommended to take other Excel Magic series courses by Lenny Wu.
Excel Magic 1: Building Dynamic Formulas
Excel Magic 2: Building Your Report Generation "Bot"
Excel Magic 3: Automating Data Entry with Data Feeds
Excel Magic 4: Ultimate API Reporting in Excel
Excel Magic 5: Building Interactive Budgeting Model in Excel
Excel Magic 6: Excel's 7 Languages
Excel Magic 7: Creating Excel Charts like a PRO
Excel Magic 8: Transforming a Pro-forma into a Dashboard
Excel Magic 9: Building a Project Tracking “Gantt” Chart
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
.
Lenny WuManagement Reporting Analyst, Seaspan Ship Management
About Illumeo
Platform
Resources
Contact Us
- Email: info@illumeo.com
- Phone: 408-400-3993
- Adress: 1608 W Campbell
- Av #221, Campbell, CA 95008
© 2024 Illumeo, Inc.