Pivot Tables & Power Pivot – Advanced Pivot Techniques and Dashboards

  • Pivot Tables for Reporting & Summarization: Different reports & layouts, Automating & updating pivots on data modification
  • Data Modelling: Creating relationships between different data tables.
  • Creating Calculated Columns and Measures: Using DAX (Data Analysis Expressions).
  • PivotTables with Power Pivot: Creating advanced PivotTables using the Power Pivot model.
  • Data visualization: Advanced charting techniques, Slicers, Sparklines and Timelines
  • Use Case Example: Building a dynamic sales performance dashboard.

Power Query – Advanced Data Cleaning & Automation

  • Data Import: Importing data from various sources (Excel, CSV, Folders, PDF, databases, web).
  • Data Transformation: Cleaning, reshaping, and preparing data with Power Query.
  • Merging and Appending Data: Combining & Consolidation data from different sources.
  • Use Case Example: Automating data preparation for monthly reports.

Data Analysis – Advanced Analysis & Forecasting techniques

Scenario Planning and Analysis

  • Creating Scenarios: creating and managing different scenarios.
  • Analyzing Scenarios: Techniques for comparing and analyzing multiple scenarios to understand potential outcomes.

Solver Tool for Advanced Analysis techniques

  • Setting Up Solver: Define and set up problem statements, constraints, and objectives in Solver.
  • Running Solver: Executing Solver to find optimal solutions and interpreting the results.
  • Practical Applications: Using Solver for resource allocation, cost minimization, and other common research-related problems.

Automation with Macros and VBA

  • Automating repetitive tasks and data processes.

ChatGPT and its integration with Excel

  • Practical applications of ChatGPT in various Excel scenarios

Advanced Excel Formulas & Functions 

Logical Functions, Statistical Functions, Financial Functions, Date and Time Functions, Lookup Functions, Text Functions , Array Formulas, Dynamic Array Functions (Excel 365)

Timings:10:00 am – 5:00 pm, Registration begins at 9:30 am