Microsoft

Course 20779-B: Analyzing Data with Excel

Course 20779-B: Analyzing Data with Excel

Share This Learning Path

Need more info? Contact us

The main purpose of the course is to give 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.

Prerequisites

  • 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.

Course outline

Module 1: Data Analysis in Excel
Module Overview

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 Sessions

Building a Classic Excel Dashboard

Lab Lessons

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

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
Module Overview

This module looks at the classic Excel data model and at ways to extend it.

Lessons

Using an Excel Data Model
DAX

Lab Sessions

Explore an Excel Data Model

Lab Lessons

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
Module Overview

This module looks at pre-formatting and importing CSV files.

Lessons

Importing Data into Excel
Shaping and Transforming Data
Loading Data

Lab Sessions

Importing Data from a CSV File

Lab Lessons

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
Module Overview

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 Sessions

Import Data from Multiple Sources

Lab Lessons

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
Module Overview

This module describes how to import data from a report.

Lessons

Importing Data from Excel Reports
Transforming Excel report Data

Lab Sessions

Importing Data from a Report

Lab Lessons

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
Module Overview

This module describes how to create and format measures.

Lessons

DAX
Measures
Advanced DAX Functions

Lab Sessions

Creating Measures using Advanced DAX Functions

Lab Lessons

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
Module Overview

This module describes how to visualize data in Excel.

Lessons

Pivot Charts
Cube Functions
Charts for Cube Functions

Lab Sessions

Data Visualization in Excel

Lab Lessons

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
Module Overview

This module describes how to use Excel with Power BI.

Lessons

Power BI
Uploading Excel Data to Power BI
Power BI Mobile App

Lab Sessions

Creating a Power BI Dashboard with Excel

Lab Lessons

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

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.

Book Your Seat​

Find Learning Paths​

  • Search Paths

  • Vendors

Latest Learning Paths​

Join our community of certified professionals

Sign Up to our newsletter, and stay always up to date with latest IT certifications

About Us

ICTC is the leader in technical certification courses and exams. Our labs consist of a latest tech PCs and our instructors are certified from each vendor

Policy for the Prevention and Combating of Violence and Harassment in the Workplace.

Facebook Linkedin

Learn

View all the provided certifications and there relevant courses. Book online for a certification exam.

Explore

Contact Us

International Computer Training Center

Copyright reserved to ICTC. Proudly Crafted by GTP Works

Choose how to get more info...

Give as a call

211 500 2 900

Let us, call you

Send us an email