Microsoft Excel Advanced

Overview

Unlock the full power of Excel. Master advanced analytics, automate complex tasks with VBA, build dynamic dashboards, and transform raw data into predictive insights and interactive reports.

Target Audience:

Data analysts, financial modelers, business professionals, and advanced users who need to perform complex data manipulation, automation, and forecasting.

Upon completion, students will be able to:

• Perform sophisticated data analysis and create dynamic dashboards with advanced PivotTable techniques.
• Record, write, and run VBA macros to automate repetitive tasks and build custom functions.
• Create advanced, interactive charts and visualizations for dashboards.
• Utilize What-If Analysis tools (Goal Seek, Data Tables, Scenario Manager) for forecasting and modeling.
• Import, transform, and combine data from multiple sources using Power Query and build a simple Data Model.
• Implement advanced workbook, worksheet, and VBA project protection strategies.

Outline

Module 1: Advanced Data Analysis with PivotTables

• Calculated Fields & Items: Creating custom formulas within a PivotTable.
• Grouping Data: Advanced grouping of dates (by weeks, quarters) and numeric fields into bins.
• Slicers & Timelines: Using slicers and timelines for interactive filtering across multiple PivotTables and PivotCharts.

Module 2: Using Macros and VBA for Automation

• Macro Recorder Deep Dive: Recording complex macros and understanding relative references.
• Introduction to the VBA Editor: Navigating the editor, writing simple sub procedures, and using MsgBox and InputBox.
• Error Handling & Debugging: Writing robust macros that handle errors and debugging code.

Module 3: Complex Charting Techniques

• Combination Charts: Creating charts that combine different chart types (e.g., Column and Line).
• Dynamic Charting: Using named ranges with the OFFSET and COUNTA functions to make charts that automatically update with new data.
• Advanced Formatting: Using form controls (like scroll bars and spinners) to create interactive charts.

Module 4: What-If Analysis and Data Forecasting

• Goal Seek: Working backwards to find the required input to achieve a desired result.
• Data Tables: Building one-way and two-way data tables for sensitivity analysis.
• Scenario Manager & Forecast Sheet: Creating and comparing different scenarios and using the automated Forecast Sheet tool for time-based forecasting.

Module 5: Data Model and Power Query Introduction

• Power Query (Get & Transform): Importing data from files, databases, and the web. Removing errors, pivoting/unpivoting columns, and creating custom columns.
• Building a Data Model: Creating relationships between multiple tables without using VLOOKUP.
• DAX Introduction: Writing basic DAX formulas (Measures) for calculated fields in PivotTables (e.g., SUMX, CALCULATE).

Module 6: Workbook Security and Protection

• Worksheet & Workbook Protection: Applying and cracking passwords to protect structure and windows. Locking and unlocking specific cells.
• File-Level Security: Encrypting workbooks with passwords and marking as final.
• VBA Project Protection: Locking VBA code from viewing with a password.