20779: Analyzing Data with Excel 2016

Modality: Led-Training & Virtual Training 

Location: Puerto Rico & Dominican Republic 

For More Information Please Call Us At 1(787) 766-2405 or email us at info@itg.online 

For Software Assurance Training Vouchers please click here: (SATV)


About this course

This three-day instructor-led course provides students the ability 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.

Audience Profile

 This course is intended for students who are experienced with analyzing data with Excel and who wish to add BI techniques. The course will likely be attended by SQL Server report creators who are interested in alternative methods of presenting data.

After completing this course, students will be able to:

Explore and extend a classic Excel dashboard.
Explore and extend an Excel data model.
Pre-format and import a .CSV file.
Import data from a SQL Server database
Import data from a report.
Create measures using advanced DAX functions.
Create data visualizations in Excel.
Create a Power BI dashboard with Excel.


This course requires that you meet the following prerequisites:

  • Excellent knowledge of relational databases and reporting.
  • Some basic knowledge of data warehouse schema topology (including star and snowflake schemas).
  • Some exposure to basic programming constructs (such as looping and branching).
  • An awareness of key business priorities such as revenue, profitability, and financial accounting is desirable.
  • Familiarity with Microsoft Office applications – particularly Excel.

Module 1: Data Analysis in Excel

  1.   Classic Data Analysis with Excel
  2. Excel Pivot Tables
  3. Limitations of Classic Data Analysis

Lab : Building a Classic Excel Dashboard

  •  Formatting Data
  •  Building a Pivot Table
  • Adding pivot charts and a slicer

Module 2: The Excel Data Model

  1. Using an Excel Data Model
  2. DAX

Lab : Explore an Excel Data Model

  • Create Calculated Columns
  • Format Data Model Data
  • Create Measures
  • Analyze the Data

Module 3: Importing Data from Files

  1.  Importing Data into Excel
  2.  Shaping and Transforming Data
  3.  Loading Data

Lab : Importing Data from a CSV File

  • Import and Transform Data from a CSV File
  • Add Data from a Folder
  • Import data into excel.
  • Shape and transform data.
  • Load data.

Module 4: Importing Data from Databases

  1. Available Data Sources
  2. Previewing, Shaping, and Transforming Data
  3. Table Relationships and Hierarchies
  4. Loading Data

Lab : Import Data from Multiple Sources

  • Import Data from SQL Server
  • Import Data from a CSV File
  • Create a Data Table

Module 5: Importing Data from Excel Reports

  1.  Importing Data from Excel Reports
  2. Transforming Excel report Data

Lab : Importing Data from a Report

  •  Import Data from Excel
  • Transform the Excel Data
  • Load the Data into an Excel Data Model

Module 6: Creating and Formatting Measures

  1. DAX
  2.  Advanced DAX Functions

Lab : Creating Measures using Advanced DAX Functions

  • Last year comparison
  • Year to date
  • Market Share

Module 7: Visualizing Data in Excel

  1.  Pivot Charts
  2.  Cube Functions
  3. Charts for Cube Functions

Lab : Data Visualization in Excel

  • Create a Tabular Report
  • Create a Pivot Chart
  • Add Slicers to Charts

Module 8: Using Excel with Power BI

  1.  Power BI
  2. Uploading Excel Data to Power BI
  3. Power BI Mobile App

Lab : Creating a Power BI Dashboard with Excel

  • Uploading Excel Data
  • Creating a Power BI Dashboard
  • Using Power BI desktop

Total Hours: 24