Analyzing Data with Excel (20779) Classroom Live Eagan, MN November 19, 2018

Course Overview

• Module 1: Data Analysis in Excel
• This module looks at the classic Excel dashboard and at ways to extend it.
  • Lessons
    • Classic Data Analysis with Excel
    • Excel Pivot Tables
    • Limitations of Classic Data Analysis
  • Lab : Building a Classic Excel Dashboard
    • Filtering and Formatting Data
    • Building a Pivot Table
    • Building a Pivot Chart
    • Building a Dashboard
  • After completing this module, students will be able to:
    • Describe classic data analysis with Excel
    • Describe Excel pivot tables
    • Describe the limitations of classic data analysis with Excel
• Module 2: The Excel Data Model
• This module looks at the classic Excel data model and at ways to extend it.
  • Lessons
    • Using an Excel Data Model
    • DAX
  • Lab : Explore an Excel Data Model
    • Create Calculated Columns
    • Format Data Model Data
    • Create Measures
    • Analyze the Data
  • After completing this module, students will be able to:
    • Describe an Excel data model
    • View data within an Excel data table
    • Describe DAX
• Module 3: Importing Data from Files
• This module looks at pre-formatting and importing CSV files.
  • Lessons
    • Importing Data into Excel
    • Shaping and Transforming Data
    • Loading Data
  • Lab : Importing Data from a CSV File
    • Import and Transform Data from a CSV File
    • Add Data from a Folder
  • After completing this module, students will be able to:
    • Import data into excel.
    • Shape and transform data.
    • Load data.
• Module 4: Importing Data from Databases
• This module looks at how to import data into Excel from a SQL Server database.
  • Lessons
    • Available Data Sources
    • Previewing, Shaping, and Transforming Data
    • Table Relationships and Hierarchies
    • Loading Data
  • Lab : Import Data from Multiple Sources
    • Import Data from SQL Server
    • Import Data from a CSV File
    • Create a Data Table
  • After completing this module, students will be able to:
    • Identify available data sources.
    • Preview, shape, and transform data.
    • Explain table relationships and hierarchies.
    • Load data from various sources.
• Module 5: Importing Data from Excel Reports
• This module describes how to import data from a report.
  • Lessons
    • Importing Data from Excel Reports
    • 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
  • After completing this module, students will be able to:
    • Import data from Excel reports.
    • Transform Excel report data.
• Module 6: Creating and Formatting Measures
• This module describes how to create and format measures.
  • Lessons
    • DAX
    • Measures
    • Advanced DAX Functions
  • Lab : Creating Measures using Advanced DAX Functions
    • Last year comparison
    • Year to date
    • Market Share
  • After completing this module, students will be able to:
    • Explain what DAX is and when to use it.
    • Describe a measure.
    • Use some of the advanced functions within DAX.
• Module 7: Visualizing Data in Excel
• This module describes how to visualize data in Excel.
  • Lessons
    • Pivot Charts
    • Cube Functions
    • Charts for Cube Functions
  • Lab : Data Visualization in Excel
    • Create a Tabular Report
    • Create a Pivot Chart
    • Add Slicers to Charts
  • After completing this module, students will be able to:
    • Create and refine a pivot chart.
    • Describe cube functions and when to use them.
    • Describe a number of charts for use with cube functions.
• Module 8: Using Excel with Power BI
• This module describes how to use Excel with Power BI.
  • Lessons
    • Power BI
    • Uploading Excel Data to Power BI
    • Power BI Mobile App
  • Lab : Creating a Power BI Dashboard with Excel
    • Uploading Excel Data
    • Creating a Power BI Dashboard
  • After completing this module, students will be able to:
    • Describe Power Bi and the various versions available.
    • Upload Excel data to Power BI.
    • Describe the Power BI App.

Prerequisites

Before attending this course, students must have:

• Basic knowledge of the Microsoft Windows operating system and its core functionality.
• Working knowledge of relational databases.
• Extensive knowledge of Excel spreadsheets including formulas, charts, filtering, sorting, and sub-totals.
• Basic knowledge of data analytics equivalent to having attended course 10994.