First steps with dynamic arrays in Excel

This will be a walkthrough of Chapter 10 of my book Modern Data Analytics in Excel. You do NOT need to have read the book, but you're certainly welcome to! We'll be walking through the content together for an hour and I'll try taking any questions from readers about the book.

Recording & downloads

• A recording will be made available after the event.
• All downloads used during the event will come from the book's free online repository.

By the end of this free online training, you’ll understand:

• The fundamental differences between traditional static array references and dynamic array references in Excel.
• How dynamic array functions can automatically adjust and expand to accommodate data changes without the need for manual updates.
• The key advantages of using dynamic array functions for real-time data analysis and manipulation over traditional methods.
• How the FILTER(), SORTBY(), and XLOOKUP() functions provide enhanced capabilities for filtering, sorting, and looking up data dynamically in Excel.

And you’ll be able to:

• Create and manipulate dynamic arrays using functions like SORT(), FILTER(), and UNIQUE() to manage and analyze data more efficiently.
• Implement dynamic array functions to automatically update and handle array sizes based on data changes, removing the need for manual adjustments.
• Utilize the XLOOKUP() function to perform more flexible and powerful lookups compared to traditional functions like VLOOKUP().
• Apply dynamic array functions to practical scenarios in Excel, such as creating dynamic drop-downs and handling real-time data updates in dashboards.

Who this is for

Typical job titles might include:

• Data analysts
• Financial analysts
• Marketing coordinators
• Accountants
• Controllers
• Operations analysts

Prerequisites

• Please have an up-to-date version of Microsoft 365 for desktop installed prior to the workshop.
• To make the most of the workshop, you will have some experience in topics such as taking sums and counts, creating calculated columns, and using relative and absolute references in Excel.
• It is recommended to attend this workshop on a Windows machine
• No other preparation is needed.

About your instructor

George Mount is the founder of Stringfest Analytics. He has over 10 years of experience in data analytics and holds master’s degrees in both finance and information systems from Case Western Reserve University. George is the author of Advancing into Analytics: From Excel to Python and R (O’Reilly, 2021) and Modern Data Analytics in Excel: Using Power Query, Power Pivot, and More for Enhanced Data Analytics (O'Reilly, 2024). He is a recipient of the Most Valuable Professional (MVP) award from Microsoft for technical excellence & community advocacy.

I look forward to seeing you at the event!

If your team is looking for custom training in Modern Excel, check out my corporate workshops.