Microsoft Analyzing Data with Excel (20779)

Course Overview
This course is intended for anyone responsible for analyzing data with Excel.

Detailed Course Outline

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

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

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

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

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

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

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

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

Who should attend?

This course is intended for:

• Both novice and experienced programmers who have a minimum of three months programming experience and have basic Windows navigation skills.
• Anyone responsible for analyzing data with Excel.
• Course will likely be attended by SQL Server report creators who are interested in alternative methods of presenting data.

Prerequisites
Before attending this course, students must have:

• Basic knowledge of the Microsoft Windows operating system and its core functionality.
• Good Knowledge of Excel spreadsheets, functions, and charts.
• Working knowledge of relational databases.

Course Objectives
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.

Duration: 3 Day

Cost: £995 + Vat

Microsoft Analyzing Data with Excel Training Scotland, Glasgow, Edinburgh, Inverness, Aberdeen and onsite course available throughout the UK.