0
Author: |
Lenny Wu, CPA, CGA, MBA |
Course Length: |
Pages: 24 ||| Review Questions: 5 ||| Final Exam Questions: 8 |
CPE Credits: |
1.5 |
IRS Credits: |
0 |
Price: |
$21.95 |
Passing Score: |
70% |
Course Type: |
Video |
Technical Designation: |
NonTechnical |
Field Of Study: |
Computer Software & Applications |
Approved Audience: |
NASBA QAS - NASBA Registry |
Key Takeaways: |
This video course focuses on building and understanding dynamic formulas in Excel to move users closer to full automation in Excel modeling.
|
Frequently Asked Questions: |
|
Description: |
The 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! Next, 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:
Last, the course explores latest features from Excel 365 that are considered by many to be amazing. They include:
Please Note: This course’s author is working on the providing transcripts, PDFs, and slides where applicable. Unfortunately until then we will not be able to offer them with the course but felt the course was valuable enough as it is so have chosen to include it on our site. Our sincere apologies for any inconvenience and please let us know any question using the support bubble on the bottom right of all pages on our site. |
Usage Rank: |
10000 |
Release: |
2021 |
Version: |
1.0 |
Prerequisites: |
|
Experience Level: |
Overview |
Additional Contents: |
Complete, no additional material needed. |
Additional Links: |
External: Improve Your Investing With Excel
Internal: Excel Magic: Building Dynamic Formulas (Video) - course detail page
Internal: Excel Magic: 7 Languages in Excel (Video) - related Excel‑magic series
External: Microsoft support - dynamic array formulas and spilled array behavior
|
Advance Preparation: |
|
Delivery Method: |
QAS Self Study |
Intended Participants: |
Anyone needing Continuing Professional Education (CPE). |
Revision Date: |
08-Apr-2026 |
NASBA Course Declaration: |
Participants must complete the final examination within one year of purchase and with a minimum passing grade of 70% or better to receive CPE credit unless otherwise noted on the Course History page (i.e. California Ethics must score 90% or better). After logging in click on the Course History links on your My Courses page for the Begin date and Expire date for the Final Exam. |
Keywords: |
Excel Magic: Building Dynamic Formulas (Video) - CPE course for CPAs |
Learning Objectives: |
Course Learning Objectives After this course, you will be able to:
|
Course Contents: |
Chapter 1 - Excel Magic: Building Dynamic Formulas Section 1: Introduction Lecture 1: Introduction Lecture 2: RECAP of Prior Excel Course: Top 5 Excel Skills Lecture 3: Hierarchy of Excel Modeling Techniques Lecture 4: Instructor Lecture 5: Other similar courses Lecture 6: What you will get from this course? Section 2: Dynamic Formulas - Basic Lecture 7: Intro to Dynamic Formulas Lecture 8: References: Absolute vs relative reference Lecture 9: Conditional formulas: IF() Lecture 10: Table formula Section 3: Dynamic Formulas - Intermediate Lecture 11: Lookup formula: INDEX/MATCH Lecture 12: Lookup and sum formulas: SUMPRODUCT/SUMIF/SUMIFS Lecture 13: Multi-tab formula: SUM(START:END!) Lecture 14: Link to Pivot Table formula: GETPIVOTDATA() Section 4: Dynamic Formulas - Advanced Lecture 15: Parameterized formulas: INDIRECT/ADDRESS Lecture 16: Dynamic range formula: OFFSET Lecture 17: Array formulas Section 5: New Feature – Excel 365: Dynamic Array formulas Lecture 18: Dynamic Array Formulas (Excel 365): SORT Lecture 19: Dynamic Array Formulas (Excel 365): XLOOKUP Lecture 20: Dynamic Array Formulas (Excel 365): Spill Range Section 6: Conclusion Lecture 21: Takeaways Lecture 22: Next Course and Q&A Chapter 1 Review Questions Glossary |
Login/Register