Microsoft Excel Specialist - Analysis and Dashboards

Course subjects

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 Pivot Table

• 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 and Error Messages

• Drop-Down Lists

• Formulas

• Customised Validation Criteria

• Creating a Number Range Validation

• Testing a Validation

• Creating an Error Message

• Creating a Drop-Down List

• Using Formulas as Validation Criteria

• Circling Invalid Data

• Removing Invalid Circles

Using Sparklines to Show Trends

• What is a Sparkline?

• Types of Sparklines

• Showing Sparklines only

• Specifying a Date Axis

• Hidden Data and Sparklines

• Sparklines and Targets

Using Conditional Formatting

• Using Conditional Formatting with a Dashboard

• Top 10 and 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

Pivot Tables

• Structure of Pivot Tables

• Using Compound Fields

• Counting in a Pivot Table

• Formatting Pivot Table Values

• Working with Grand Total and Subtotals

• Finding the Percentage of Total

• Finding the Difference From

• Grouping in Pivot Table Reports

• Creating Running Totals

• Creating Calculated Fields

• Providing Custom Names

• Creating Calculated Items

• Pivot Table Options

• Sorting in a Pivot Table

• Top and Bottom Views

• Date Grouping Options

• Hiding or Showing Data Items

• Conditional Formatting and Sparklines in Pivot Tables

• Pivot Caches and File Size

Pivot Charts

• Inserting a Pivot Chart

• Defining the Pivot Chart Structure

• Changing the Pivot Chart Type

• Using the Pivot Chart 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 the data using Power Pivot, creating relationships and the necessary calculations to build our interactive Dashboard for assessing Sales performance and profitability across various cities.