Excel Data Analysis & Visualisation Essentials - 1 Day course

Course Description

Excel is part of the suite of tools from Microsoft which enable data to be analysed and published from a variety of sources. This course provides a solid introduction to using Excel as a Data Analyser to connect to data, create visualisations, query the data and publish reports. It is designed to introduce users to report generation and visualisation using Data Analytic techniques and refreshing data from many data sources using Excel’s built-in M programming in Power Query.
1 day
Contact us for pricing
 

Prerequisites

Participants should have at least Intermediate Excel and database user skills. They need:
An understanding of basic data concepts
Ability to create and format chart outputs
Ability to create formulas using standard aggregate functions

Data examples - note

Note: Data Examples used can be either Healthcare specific or generic Business.

Learning Objectives

Participants will learn:
Excel data analysis concepts and main features
Data sources compatible with Excel
Connecting to data and creating data models
Exploring data visualisations
Creating new columns using DAX formula
Creating and publishing reports
Creating Time Intelligence Tables for different time periods

Getting Started with Excel Data Analysis and visualisations

An Introduction to Data Models
Working with CSV, JSON, XML and Excel Files
Connecting to external Data Sources with Excel
Creating a Report with Visualisations
Doing More with Visualisations

Power Query Features

Using the Advanced Editor
Data source settings
Creating Parameters
Creating groups
Merging Data
Transpose, Reverse, fill
Creating custom functions
Using custom columns
The M-Language

Data Connectivity

Connecting to New Data sources
Using SQL in Data Sources
DirectQuery
OData feeds
JSON and XML files

Working with Data examples

Exercise 1 - Importing data from CSV files.
Exercise 2 – Using multiple datasets imported from multiple Excel files
Exercise 3 – Automatically updating data from files in a Folder
Exercise 4 – Un-pivoting Data using Power Query
Exercise 5 – Using JSON and XML data files.

Note: Data Examples used can be either Healthcare specific or generic Business.

Relational Databases and Excel

Creating Data Tables in Excel
Creating Lookup Tables
Creating and Managing Relationships between Tables
Creating Visuals based on Linked Data

Introduction to Data Analysis Expressions - DAX

The concept of DAX
Creating new columns
Creating quick and New Measures

A Closer Look at Visualisations

Matrixes, Tables, and Charts
Maps
Cards, Gauges, and KPIs
Slicers

Time Intelligence

Simple Date Calculations
Date and Time Formatting
Calculating the Age of Cars Sold
Calculating the Difference Between Two Dates
Adding Time Intelligence to a Data Model
Creating and Applying a Date Table
Creating the Date Table
Adding Sort By Columns to the Date Table
Date Table Techniques
Adding the Date Table to the Data Model
Applying Time Intelligence
YearToDate, QuarterToDate, and MonthToDate Calculations
ExcelData VisualisationM ProgrammingPower Query