Course Code: excelwew
Duration: 16 hours
Prerequisites:

Audience

Overview:

Format of the Course

Course Customization Options

Course Outline:

Prologue

 

 

  • Know the version of your Excel application (Excel 365 Main Versions: Family, Business and Enterprise)

 

  • Keyboard shortcuts for Excel

 

  • How Excel handles Date and Time in sheets/tables

 

Module 1 Data preparation

 

 

  • Import data from MS SQL Server or Azure SQL Database

 

  - Essential T-SQL Statements

 

 

  • Import data from SharePoint Online, Power BI semantic model and Dataverse

 

  • Import other external data sources (from multiple Excel files in folder, Web page, Text file and PDF file)

 

  • Use Power Query to clean data (recommended Extract, Transform and Load tool)

 

  • Use Excel functions to clean data (Text, Date & Time, Lookup & Reference and Information functions)

 

Module 2 Data modelling

 

 

  • Model the data with Power Pivot

 

  • DAX (Data Analysis Expressions) Quick Guide

 

  • Use DAX to build Measures and Calculated Column/Table

 

Module 3 Data Visualization

 

 

  • Essential and extended Charts (Introduce the features of traditional and newer charts, Sparklines and SmartArt) (e.g. the settings of charts, custom combo chart, trendline and so on)

 

  • Conditional Formatting

 

  • PivotTable and PivotChart (Slicer and Timeline included)

 

  • Simple dynamic charts

 

Module 4 Analysis Tools

 

 

  • Use Analysis ToolPak to perform complex data analysis (Linear Regression)

 

  • Financial functions (not all functions, selective)

 

  • Other Advanced functions (Logical, Math and Statistical functions) 

 

  • Create a forecast in Excel

 

  • Sensitivity analysis with data tables

 

  • Using the Scenario Manager for sensitivity analysis

 

  • The Goal Seek command

 

Module 5 Visual Basic for Application (VBA)

 

 

  • Simplified Excel Object Model

 

  • Basic functional structures (Sub and Function)

 

  • Variables, data types, condition and loop statements

 

  • Object Oriented Programming Basis (class, property, method and event)

 

  • Write functional modules with AI tools

 

  • Create User Define Function (UDF, includes Excel Add-in creating)

 

  • Create and apply Macros in Excel Interface

 

  • Some useful Subs/Functions

 

Epilogue

 

 

To solve different problems, we have multiple tools to use. Please choose the more efficient one!