Course Overview
This Analyzing Data with Excel training class teaches students to add BI techniques to Excel data analysis. The course goes beyond the capabilities of tables and charts and uses Pivot
Charts, the Excel Data Model, and Power BI. This course also extends the functionalities of traditional Excel using visual basic programming to make Excel fit the automation scenarios.
Course Objectives
Explore and extend a classic Excel dashboard.
Import data from files or databases.
Advanced data transformation using power query.
Excel data model.
Create measures using advanced DAX functions.
Create data visualizations in Excel.
Extend Excel functionalities using visual basic programming.
Duration: 5 Days
Outlines
--Day 1
Module 1: Data Analysis in Excel
(9:00 AM-12:00 PM)
Excel Pivot Tables (Limitations of Classic Data Analysis)
Filtering and Formatting Data
Building a Pivot Table
Lab Exercise
Lunch (12:00 PM-1:00 PM)
Module 2: Import data from other sources (1:00 PM-5:00 PM)
Importing Data from CSV and other Excel
Import Data from Database
Import Data from JSON files.
Transforming Data
Lab Exercise
--Day 2
Module 3: Advanced Data Transformation using Power Query
(9:00 AM-12:00 PM)
Data cleanup
Transpose
Unpivot
Data Combination
Lab Exercise
Lunch (12:00 PM-1:00 PM)
Module 4: Data Modelling and Relationship
(1:00 PM-5:00 PM)
Table Relationships fundamentals
Relationship cardinality
Create relationships in Excel
Use the relationship in the pivot table
Lab Exercise
--Day 3
Module 5: Advanced Analysis using DAX
(9:00 AM-12:00 PM)
DAX Measure
DAX Table
DAX Column
Time Intelligence Functions
Customise Financial Year
Lab Exercise
Lunch (12:00 PM-1:00 PM)
Module 6: Data Visualization using Pivot Chart
(1:00 PM-5:00 PM)
Pivot Charts
Cube Functions
Charts for Cube Functions
Add Slicers to Charts
Lab Exercise
--Day 4
Module 7: Introduction to Excel Macro
(9:00 AM-12:00 PM)
Macro Recorder
Different Ways to Run a Macro
Edit a Macro
The Visual Basic Environment
Module and Sup Procedures
Visual Basic Editor
Object Model
Properties
Method
Variables
Lunch (12:00 PM-1:00 PM)
(1:00 PM-5:00 PM)
Conditional Statements
Select Case
Lab Exercise
--Day 5
Module 8: Advanced Macro
(9:00 AM-12:00 PM)
Looping
For Next Loops exercise
Do while
Do until
Lab Exercise
Communicating with the User
Simple Message Boxes
Anatomy of a VBA MsgBox in Excel
Different types of buttons
Lab Exercise
Lunch (12:00 PM-1:00 PM)
(1:00 PM-5:00 PM)
Error Handling syntax error
Compile Error
Run Time Errors
Logical Errors
VBA Error Handling with ‘On Error’ Statements
Debugging the Code
Event Procedures
Workbook SelectionChange Event
Workbook Doubleclick Event
User Form
Lab Exercise
Q&A
Programme scope, delivery arrangements and suitability are confirmed during enquiry.