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.
- 1. Excel Magic 1: Building Dynamic Formulas
- 2. Excel Shortcuts Training: 5 Top Productivity Boosting Shortcuts
- 3. Excel Efficiency: Database Techniques
- 4. Dynamic Array Formulas and Other New Excel Functions (Text Based Course)
- 5. ASC 842 Accounting for Leases - Deep Dive
- 6. Top 5 Excel Skills: From Excel Rookie to Excel Guru
- 7. Excel Time Value of Money Functions for CPAs (Text Based Course)
- 8. VLOOKUP vs. SUMIFS: The Battle of Two Excel Heavyweights
- 9. The One Page Financial Overview - Help Your Audience See the Forest First
- 10. Using PivotTables and PivotCharts to Prepare External Data
21 Reviews (63 ratings)
Prerequisites
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