ΠΕΡΙΓΡΑΦΗ

In many departments, critical reporting, forecasting, HR/operations tracking, and management dashboards are still built using legacy formulas, helper columns, and manual refresh steps. This creates recurring issues such as slow workbooks, formula errors, low maintainability, and dependence on a small number of “Excel experts”.

ΣΚΟΠΟΣ ΣΕΜΙΝΑΡΙΟΥ

The participants will be able to:

• How Excel’s calculation engine has changed with dynamic arrays
• The concept of functional Excel using LAMBDA
• Differences between legacy and modern Excel approaches
• When to use formulas vs Power Query vs automation
• Build fully dynamic models without helper columns
• Create custom Excel functions using LAMBDA
• Replace complex nested formulas with clean, readable logic
• Automate data preparation and reporting workflows
• Design robust, maintainable Excel solutions
• Reduce errors and manual work in expert-level spreadsheets
• Apply modern Excel features in real business scenarios
• Act as Excel “go to experts” within their organization

ΣΕ ΠΟΙΟΥΣ ΑΠΕΥΘΥΝΕΤΑΙ

Data analysts, finance professionals, HR & operations managers, consultants, power users working with complex datasets and models. 

Recommended Prerequisites

• Excellent knowledge of Excel formulas
• Confident use of LOOKUP functions, PivotTables, conditional logic
• Experience with large datasets and complex workbooks

ΠΕΡΙΣΣΟΤΕΡΕΣ ΠΛΗΡΟΦΟΡΙΕΣ

Topics

Module 1: The New Excel Engine

From legacy Excel to Modern Excel

• How dynamic arrays changed Excel fundamentally
• Spill behavior and calculation logic
• Volatile vs non volatile formulas
• Best practices for performance in large models

• Hands-on:

  • Converting legacy formulas to dynamic equivalents
    

Module 2: Core Dynamic Array
Functions Essential Functions

• FILTER() – advanced multi-condition filtering
• SORT() & SORTBY() – dynamic ordering
• UNIQUE() – deduplication logic
• SEQUENCE() – dynamic time series & IDs Advanced Combinations
• Nested dynamic arrays
• Dynamic dashboards without PivotTables
• Error safe dynamic formulas

• Hands-on:

  • Build a dynamic reporting table with zero helper columns

Module 3: Next- Generation Lookup & Text Functions Modern Lookup Logic

• XLOOKUP() vs VLOOKUP() / INDEX-MATCH
• XMATCH() for advanced matching
• Multi-return lookups with spill ranges
• New Text & Array-Shaping

• Functions

  • TEXTSPLIT()
  • TEXTBEFORE() / TEXTAFTER()
  • VSTACK() / HSTACK()
  • TAKE() / DROP()
  • TOROW() / TOCOL()

• Hands-on:

  • Clean, reshape, and merge multiple datasets dynamically

Module 4: Dynamic Charts & Visual Models

• Dynamic named ranges (modern approach)
• Charts driven by spill ranges
• Interactive models using data validation + arrays
• Replacing PivotCharts with dynamic formulas

• Hands-on:

  • Build a fully dynamic chart that updates automatically

Module 5: LET – Readable & Maintainable Formulas

• Why LET is essential for experts
• Breaking complex formulas into logical steps
• Performance and readability benefits

• Hands-on:

  • Refactor a “monster formula” using LET

Module 6: LAMBDA – Create Your Own Excel Functions

Core Concepts

• What LAMBDA is and why it matters
• Naming and storing custom functions
• Parameters, logic, and reuse Advanced Use
• Recursive LAMBDA functions
• Error handling inside
• LAMBDA

• Hands-on:

  • Create reusable business logic functions (e.g. KPI scoring, pricing rules)

Module 7: Functional Excel – MAP, REDUCE & SCAN

• New Functional Functions
• MAP() – apply logic across arrays
• BYROW() / BYCOL()
• REDUCE() – cumulative logic
• SCAN() – running totals and advanced sequences
• Use Cases
• Advanced calculations without helper columns
• Complex aggregations and transformations

• Hands-on:

  • Build a functional Excel solution replacing multiple formulas

Module 8: Automation & the Excel Ecosystem

• Power Query (Expert Perspective)
• When Power Query is the right tool
• Combining PQ with dynamic arrays Office Scripts & Copilot (Overview)
• Introduction to Office Scripts (Excel for the web)
• Automating repetitive expert tasks
• How Copilot enhances expert Excel workflows (analysis, formula generation, insights)

• Hands-on:

  • Design an end to end automated reporting workflow

Final Capstone Exercise (Integrated)

Participants will:

• Design a modern Excel solution using
• Dynamic arrays
• LET + LAMBDA
• Functional logic
• Automation elements