Advanced EXCEL, Macros, Pivots, ChatGPT

Course Content

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