Course Code: excelwew
Duration: 16 hours
Prerequisites:
  • An understanding of ___
  • Experience with ___
  • ___ programming experience

Audience

  • ___
  • ___
  • ___
Overview:

___ is ___.

This instructor-led, live training (online or onsite) is aimed at beginner-level / intermediate-level / advanced-level ___ who wish to use ___ to ___.

By the end of this training, participants will be able to:

  • Install and configure ___.
  • ___. 
  • ___. 
  • ___. 

Format of the Course

  • Interactive lecture and discussion.
  • Lots of exercises and practice.
  • Hands-on implementation in a live-lab environment.

Course Customization Options

  • To request a customized training for this course, please contact us to arrange.
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!