Back

New in Excel: Routine Tasks Optimization

$
1800.00
Register
2 days | 16 academic hours


Training objectives
Training program Day 1
Training program Day 2

Training objectives

  • Master Modern Excel Tools: Learn to effectively use Power Query, Power Pivot, DAF (Dynamic Arrays), XLOOKUP, and other recent updates
  • Optimize Workflows: Reduce manual operations and increase data processing speed through automation (Python scripts, new Excel functions)
  • Improve Analytical Quality: Learn to build data models, create calculations using DAX, and generate more accurate and visually appealing reports
  • Expand Team Collaboration: Understand how to use hybrid approaches (Python, macros, DAF) and share developed solutions with colleagues

Training program Day 1

Introduction and Overview of New Features in Excel
  • Brief overview of recent Excel updates
  • Discussion of data analytics trends and the impact of new tools on workflow
  • The Importance of Automation and Integration of Various Data Sources
  • How to determine which new tool to apply for a task
Power Query
  • Importing Data from Various Sources: How to connect to CSV, databases, web pages, and other formats
  • Data Transformation: Automated cleaning, filtering, table merging, and other preprocessing methods
  • Recording Step Sequences: Saving and reusing transformation operations without coding
  • Optimization and Updating: How to work with large data volumes and automatically update results
Power Pivot
  • Creating Data Models: Using multiple tables and establishing relationships between them for further analysis
  • Advanced DAX Language: Key functions that enable complex calculations and real-time computations
  • Hierarchies and KPIs: Visual representation of metrics and building hierarchical structures for analytics
  • Visualization and Reporting: Interacting with PivotTable and PivotChart for clear data presentation
Python Script vs. Macros
  • Python Capabilities in Excel: Overview of the newly added Python script support (Microsoft 365 Insider/Future Plans)
  • Python Use Cases: When it is beneficial to integrate scripts for data analysis, visualization, and automation
  • Comparison with VBA Macros: Pros and cons of each approach, compatibility issues, and scalability of solutions
  • Examples of Simple Python Scripts: Loading packages (Pandas, NumPy) and basic data transformations in Excel

Training program Day 2

DAF (Dynamic Array Functions)
  • Key Dynamic Functions: How they work and why they are more convenient than traditional formulas
  • Automatic Expansion Capabilities: Simplifying work with ranges without manually adjusting formulas
  • Advanced Examples: Combining UNIQUE, SORT, and FILTER for quick data analysis and transformation
XLOOKUP vs. VLOOKUP and Other Table Lookup Features
  • Comparison of XLOOKUP and VLOOKUP: Why XLOOKUP is more convenient, what problems it solves (left-to-right lookup, multi-condition lookup, etc.)
  • Using MATCH/INDEX and Other Functions: Classic approaches and their modern alternatives
  • Further Combination with Dynamic Arrays: Examples where XLOOKUP helps quickly generate results as a dynamic array
  • Errors and Practical Tips: How to handle missing values, zero or blank results, and adapt to various data formats