Excel Analysis and Dashboards Training

Data Modelling

Starting a dataset in Excel

Multiple Tables

Data Modelling

Get & Transform

Understanding Get & Transform

Understanding the Navigator Pane

Creating a New Query From a File

Creating a New Query From the Web

Understanding the Query Editor

Displaying the Query Editor

Managing Data Columns

Reducing Data Row

Adding a Data Column

Transforming Data

Editing Query Steps

Merging Queries

Working With Merged Queries

Saving and Sharing Queries

The Advanced Editor

Power Pivot

Understanding Relational Data

Common Sense Data Modelling

Enabling Power Pivot

Connecting to a Data Source

Working with The Data Model

Working with Data Model Fields

Changing A Power Pivot View

Creating A Data Model PivotTable

Using Related Power Pivot Fields

Creating A Calculated Field

Creating A Concatenated Field

Formatting Data Model Fields

Using Calculated Fields

Creating A Timeline

Adding Slicers

Great Functions for Analysis

Understanding Data Lookup Functions

Using CHOOSE

Using VLOOKUP

Using VLOOKUP For Exact Matches

Using HLOOKUP

Using INDEX

Using SUMIF

Using SUMIFS

Using SUMPRODUCT

Data Validation

Validation Criteria

Input Messages & Error Messages

Drop-Down Lists

Formulas

Customised Validation Criteria

Creating A Number Range Validation

Testing A Validation

Creating an Error Message

Using Formulas as Validation Criteria

Circling Invalid Data

Removing Invalid Circles

Conditional Formatting & Sparklines

Using Conditional Formatting with a Dashboard

Top 10 & Custom Formatting

Data Bars

Show data bars outside the data cell

Colour Scales

Icon Sets

Creating Rules Based Icon Set

Removing unnecessary icons

Using Symbols in Reporting

Using the Camera Tool

What is a Sparkline?

Types of Sparklines

Showing Sparklines only

Specifying a Date Axis

Hidden Data and Sparklines

Sparklines and Targets

PivotTables & PivotCharts

Structure of Pivot Tables

Using Compound Fields

Counting in A PivotTable

Formatting PivotTable Values

Working with Grand Total & Subtotals

Finding the Percentage of Total

Finding the Difference From

Grouping in PivotTable Reports

Creating Running Totals

Creating Calculated Fields

Providing Custom Names

Creating Calculated Items

PivotTable Options

Sorting in a PivotTable

Top and Bottom Views

Date Grouping Options

Hiding or Showing Data Items

Conditional Formatting and Sparklines in Pivot Tables

Pivot Caches and File Size

Inserting a PivotChart

Defining the PivotChart Structure

Changing the PivotChart Type

Using the PivotChart Filter Field Buttons

Moving Pivot Charts to Chart Sheets

Slicers in Reports

What are Slicers?

Creating Slicers

Using a Slicer on Multiple Pivot Tables

Renaming Pivot Tables

Timeline Slicer

Trending Charts

Why do we use Trending Charts?

Appropriate Chart Types for Trending

Vertical or Y-Axis Scales

Chart Titles linking to a Cell

Comparative Trending

Labelling

Using a Secondary Axis

Formatting Key Data Points

How to display Actuals and Forecasts

Averages and Data Smoothing

Other Report Charts

Top and Bottom Charts

How to show Top or Bottom in Data Labels

Waterfall Charts

Histograms

Creating Histograms using Formulas

Creating Histograms using Pivot Tables

Creating Histograms using Excel’s Statistical Charts

Charting Performance Against a Target

Performance against Targets

Creating Thermometer Chart

Bullet Graph

Defining Dashboards

Purpose of a Dashboard

Working out what is needed

What are the data sources

Will the audience need further data to drill-down to?

How often will/can the data refresh?

Does it need to be maintained?

How easy will it be to maintain?

Dashboard Design Principles

Thirteen common mistakes in dashboard design

Making an Interface

Using Macros with Dashboards

Recording a Macro

Navigation using Macros

Macros to Change Chart types

Macros and Pivots

Pulling it all together – Case Study

We start with raw sales data for a Comedy Roadshow. We model a the data using PowerPivot, creating relationships and the necessary calculations to build our interactive Dashboard for assessing Sales performance and profitability across various cities.