Modern Approaches to Excel Formulas

Access this expert-led webinar instantly, available anytime on-demand.

4.4
Included in All-Access Membership
Live Webinar - no upcoming date
Customer Satisfaction Guarantee Learn with confidence. If you're not happy, we'll make it right. That's our guarantee.

Purchase Options

Select an attendee quantity to add to cart.

Recorded Webinar Only

$219.00
or

All Access Membership

The Aurora All Access Membership is designed to provide you with the training that you want when you want it. You will have 100% access to every live webinar, on demand webinar, professional alert, and podcast that Aurora Training Advantage offers with no additional cost.

Learn More About Our All Access Membership
$599.00
All Access Membership

Are you still inputting Excel formulas one cell at a time? If you haven’t explored what’s new in Excel over the past few years, you’re missing out on powerful, time-saving features that can dramatically improve your workflow. Modern Excel offers a suite of dynamic functions designed to simplify your tasks, enhance accuracy, and make your spreadsheets more intelligent.

In this webinar, you’ll discover how to leverage Excel’s new dynamic array functions, which can automatically spill results across multiple cells—eliminating the need for repetitive formulas. You’ll also learn updated methods for referencing cells, explore underused but powerful functions, and even create your own custom functions to solve more complex problems. Whether you're a seasoned Excel user or looking to expand your capabilities, this session will unlock new efficiencies and possibilities in your spreadsheets.

Your Benefits for Attending:
  • Learn how to correctly use modern referencing symbols in Excel for more efficient formula creation
  • Discover powerful but often overlooked Excel functions that can simplify your work
  • Understand and apply dynamic array formulas to reduce redundancy and enhance your data analysis
  • Gain practical tips on creating custom functions to handle advanced or unique spreadsheet needs
  • Explore how dynamic functions can evolve with your dataset, minimizing manual updates

This session is ideal for professionals who want to streamline their Excel processes, reduce errors, and gain more control over how their data behaves. If you work with spreadsheets regularly, these new functions can transform the way you work.

Level: Intermediate
Format: Live webcast
Instructional Method: Group: Internet-based
NASBA Field of Study: Computer Software & Applications (2 hours)
Program Prerequisites: None
Advance Preparation: None

  • Derek Henry, CPA, CFE

    Derek Henry, CPA, CFE

    Derek is an accomplished speaker, trainer, consultant, and developer with over 20 years of experience in public accounting, corporate accounting/finance, and software pre-sales. Over that time, he has taught, managed, and mentored hundreds of people on how to work with more efficiency, accountabilit [...]

    View Full Profile

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or [email protected].

ATATX Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.

ATAAA Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in administrative.

ATAOP Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in operations.

IAAP Credit

Aurora Training Advantage has reviewed the content of this program and believes it aligns with the CAP Body of Knowledge. We estimate this program may qualify for 1 recertification point(s), based on 1 point per 1 hour of eligible training. Final determination of applicability rests with the CAP designee, who is responsible for documenting alignment for recertification purposes.

ISM Credit

Institute of Supply Management

This program may be used for Continuing Education Hours (CEH) toward recertification for programs offered by the Institute for Supply Management®, including the Certified Professional in Supply Management® and Certified Professional in Supplier Diversity®.

Customer Satisfaction Guarantee
Invest in your future with confidence! Our Customer Satisfaction Guarantee eliminates all risk, letting you focus purely on mastering new skills and advancing your career. If you're not completely satisfied, we'll ensure you are. Your satisfaction is not just a promise; it's our guarantee.

Webinar Survey Overall Rating

This webinar received a total of 4 survey responses. Attendees have given an average rating of 4.4 stars out of a possible 5, reflecting the quality and value of the content presented.

Average rating

4.4 / 5
Webinar Presentation
How many of the objectives of the event were met?
4.0 Stars
How useful was the information presented at this event?
4.5 Stars
Overall, how satisfied were you with this event?
4.3 Stars
Speaker Performance
Overall, how satisfied were you with this presenter?
4.5 Stars
How closely did the presenter follow the schedule?
4.8 Stars

Reviews From Webinar Survey

Our webinars are crafted to deliver exceptional value and insight to business professionals. Below, you'll find genuine feedback from attendees.

Allison C.
December 18, 2025
4.6 / 5
Webinar Rating:
4.3 Stars
Speaker Rating:
5.0 Stars
Do you have any other comments, questions or concerns?
I felt it went a little to quick which made some of it hard to follow and keep up
Chad F.
December 18, 2025
5.0 / 5
Webinar Rating:
5.0 Stars
Speaker Rating:
5.0 Stars
Do you have any other comments, questions or concerns?
d
Ania F.
December 18, 2025
3.8 / 5
Webinar Rating:
3.7 Stars
Speaker Rating:
4.0 Stars
Do you have any other comments, questions or concerns?
I wish I can follow instructor instructions on my own. I hope there is recording and I can redo it. I was distracted so many times between...I got lost
Tonya D.
December 18, 2025
4.2 / 5
Webinar Rating:
4.0 Stars
Speaker Rating:
4.5 Stars
Do you have any other comments, questions or concerns?
Could not see the excel view very well. needs to be larger so someone can see what is being done.

Frequently Asked Questions

Dynamic array formulas are one of the most significant advances in Excel in recent years, allowing a single formula to automatically spill results across multiple cells rather than requiring separate formulas in each cell. Introduced in Microsoft 365 Excel, these formulas use spill behavior: when a formula produces multiple values, Excel automatically populates as many cells as needed to display all results. Key dynamic array functions include FILTER (extracts rows matching criteria), SORT and SORTBY (sorts data dynamically), UNIQUE (returns distinct values), SEQUENCE (generates number sequences), and XLOOKUP (a modern VLOOKUP replacement). These functions are intelligent: they automatically expand or contract as underlying data changes, eliminating the need to update formulas when data grows. The spill range is referenced using the hash symbol, enabling other formulas to work with the entire dynamic output. For Excel professionals who still rely on VLOOKUP, manual array copying, or complex helper columns, learning dynamic array functions can dramatically simplify spreadsheet architecture and reduce formula complexity across any type of data analysis or reporting work.
XLOOKUP is Excel's modern replacement for VLOOKUP, offering greater flexibility, fewer limitations, and simpler syntax. VLOOKUP's well-known limitations include its inability to look left (the lookup column must be leftmost), its requirement to specify a column index number that breaks when columns are inserted or deleted, and its default approximate match behavior—a common source of errors. XLOOKUP addresses all of these: it can look in any direction, returns the result range directly rather than requiring a column number, defaults to exact match, and includes a built-in if-not-found argument replacing the need for IFERROR wrapping. XLOOKUP also supports returning multiple columns simultaneously and searching from last to first—features impossible with VLOOKUP. For Excel users who regularly build data retrieval formulas, transitioning to XLOOKUP reduces errors, simplifies formula maintenance, and enables more sophisticated lookups with cleaner, more readable syntax in any version of Microsoft 365 Excel.
The FILTER function is one of the most practical dynamic array functions in modern Excel, allowing users to extract a subset of data meeting specified criteria with a single formula—replacing the need for manual filtering, complex array formulas, or VBA macros for dynamic data extraction. FILTER evaluates a logical condition for each row and returns only the rows where the condition is TRUE. Multiple conditions can be combined using multiplication for AND logic or addition for OR logic. Results spill dynamically and update automatically when source data changes—eliminating the need to re-filter manually. FILTER is particularly powerful when combined with other dynamic functions: SORT and FILTER together return filtered results in a specified order; UNIQUE and FILTER extract distinct values from a filtered subset. For finance, operations, and administrative professionals who regularly extract conditional subsets of data for reporting, FILTER eliminates manual steps and creates self-updating dynamic extracts that remain accurate as source data evolves over time.
Modern Excel offers several referencing techniques that improve formula accuracy, readability, and maintainability compared to traditional cell range references. Structured references—available when data is formatted as an Excel Table—replace generic ranges like dollar-sign-column-dollar-sign-row references with descriptive names like TableName followed by the column name in brackets. Structured references automatically expand when new rows are added to the table, eliminating the most common cause of formulas missing new data. They are self-documenting: a SUM formula referencing a named Sales column is immediately understandable, while a formula referencing an arbitrary range requires investigation to interpret. The spill range operator references the entire output of a dynamic array formula, enabling other formulas to work with dynamic results without manually updating ranges. Named ranges allow complex range references to be replaced with descriptive labels throughout a workbook, reducing errors when ranges change. Excel professionals who adopt these modern referencing techniques build more robust, error-resistant spreadsheets that are easier to audit, maintain, and hand off to colleagues.
The LAMBDA function is one of Excel's most powerful modern additions, enabling users to create reusable custom functions using Excel formula syntax—without needing VBA or any programming knowledge. LAMBDA works by defining a function with named parameters and a formula that uses those parameters. Once defined, the function is saved as a named range using the Name Manager and can be called from any cell just like a built-in function. For example, a custom function to calculate a tax-inclusive price could be defined with LAMBDA, saved with a meaningful name, then called from any cell by simply typing that name with the input values. This eliminates the need to repeat complex formula logic across multiple cells, reducing redundancy and the maintenance burden when logic needs to change—update the LAMBDA definition once and all cells using it update automatically. LAMBDA can also be used with the LET function—which assigns intermediate calculation results to named variables within a formula—to build sophisticated, readable multi-step calculations without nesting dozens of functions together. For advanced Excel users who have relied on VBA for custom functionality, LAMBDA provides a more accessible, formula-based alternative.