Use Landscape to see Search/Filter
Item Types:
Field of Study:
CPE Hours:
Course Details

Excel Magic: Building Dynamic Formulas (Video) (Course Id 1776)

QAS / Registry
  Add to Cart 
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 - NASBA QAS - NASBA Registry
Primary Subject-Field Of Study:

Computer Software & Applications - Computer Software & Applications for Course Id 1776

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:

  • Dynamic range formula
  • Table formula
  • Conditional formula,
  • Array formula

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

  • SORT()

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 : 0
Release : 2021
Version : 1.0
Prerequisites :
  • Basic Excel knowledge
  • Example: be able to open one Excel file and connect to external data files, etc.
  • Experience Level : Overview
    Additional Contents : Complete, no additional material needed.
    Additional Links :
    Advance Preparation :
  • Basic Excel knowledge
  • Example: be able to open one Excel file and connect to external data files, etc.
  • Delivery Method : QAS Self Study
    Intended Participants : Anyone needing Continuing Professional Education (CPE).
    Revision Date : 21-Jan-2021
    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.
    Approved Audience :

    Video - NASBA QAS - NASBA Registry - 1776

    Keywords : Computer Software & Applications, Excel, Magic, Building, Dynamic, Formulas, Video, cpe, cpa, online course
    Learning Objectives :

    Course Learning Objectives

    After this course, you will be able to:
    • 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

    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


    CPE Computer Software & Applications Course: https://www.cpethink.com/excel-cpa-courses
    Thank you for taking one of our free courses. We would like to be able to let you know when we add free courses or have special offers and will never spam you or share your address with anyone. If you are Ok with that please reply with "Ok" or if not please reply "No Thanks". Either way enjoy your free CPE course.
    Exam completed on .

    Do you want to add the course again?