Download our guide to end user applications

Microsoft Excel Specialist - Analysis and Dashboards

Length 1 day

Course overview

View dates &
book now

Why study this course

Microsoft Excel’s ability to integrate and visualise data is the focus of this one-day course, using the latest features in Excel. Work through multiple exercises to learn to master the tools of data modelling, analysis, and building visuals for effective dashboards.

Participants will create a data model with a range of data sources using Power Pivot and use Get and Transform to connect and manipulate a range of data sources including cloud databases, websites and Facebook. Learn how to fully utilise Excel to create interactive Dashboards, Charting tools and Visualisation techniques.

We complete the course by working through a Case Study, pulling together all the aspects taught on the day.

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

Note: Excel 2016, Excel 2019, Excel 2021, and Excel 365 are all desktop application versions of Microsoft Excel. Excel 2016, Excel 2019, and Excel 2021 are the perpetual, bought-outright, stand-alone versions of the software; Excel 365 is the subscription-based version. Anyone working with any of these versions will be able to successfully complete this course. Throughout this course you will be using the desktop app; the course does not cover use of the web/online app.

Nexacu Public Schedule

Nexacu is part of the Lumify Group, offering you the largest public schedule of end user applications and professional development training in Australia, New Zealand, and the Philippines. You can now access the schedule of courses and book, by clicking on the button below.

Book now!

Request Course Information

What you’ll learn

At the completion of this course you should be able to:

Review Pivot Tables and Charts within a data model
Extend Pivot Table and Chart technical skills and features
Use data from web for live share price connection
Manipulate data sources using query editor
Connect to cloud databases and websites
Create relationships using a Power Pivot data model
Effectively analyse data in Excel
Create interactive visualisations

Microsoft Apps at Lumify Work

Lumify Work has been delivering effective training across all Microsoft products for over 30 years. We are proud to be both Australia's and New Zealand’s first Microsoft Gold Learning Solutions Partner and the winner of the Microsoft MCT Superstars Award for FY24, which formally recognises us as having the highest quality Microsoft Certified Trainers (MCTs) in ANZ. Join more than 5,000 students who attend our quality Microsoft courses every year.

View all Microsoft Apps at Lumify Work courses

Who is the course for?

The course is aimed at those who want to use the latest features of Microsoft Excel to their full capability.

This course is not recommended for MAC users.

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 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 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 PivotTable
Formatting PivotTable Values
Working with Grand Total and 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

PivotCharts

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

Prerequisites

This course assumes the user already has an intermediate level knowledge of Microsoft Excel. Attendance of our other courses is not a pre-requisite for this course.

Terms & Conditions

The supply of this course by Lumify Work is governed by the booking terms and conditions. Please read the terms and conditions carefully before enrolling in this course, as enrolment in the course is conditional on acceptance of these terms and conditions.