Got a question? Call 1800 853 276   |   
Contact usCheckoutContact us
End User Applications Category Banner Image

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

With the recent acquisition of Nexacu, we're pleased to now offer you the largest public schedule of end user applications training in Australia and New Zealand. As we move to consolidate our End User offering with Nexacu, as an interim measure, you can now access the schedule of the most closely aligned courses and book, by clicking on the link below. Pricing of some courses may vary when booking directly through Nexacu.

Logo: Nexacu Powered by Lumify

Request Course Information

By submitting an enquiry, you agree to our privacy policy and receiving email and other forms of communication from us. You can opt-out at any time.

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 Partner Cloud Logo

Microsoft Apps at Lumify Work

Lumify Work is your best choice for training and certification in any of Microsoft’s leading technologies and services. We’ve been delivering effective training across all Microsoft products for over 30 years, and are proud to be Australia's and New Zealand’s first and largest Microsoft Gold Learning Solutions Partner. Join more than 5,000 students who attend our quality Microsoft courses every year.

Stay ahead of the technology curve

Don’t let your tech outpace the skills of your people

Quality Instructors and Content

Expert instructors with real world experience and the latest vendor-approved in-depth course content.

Partner-Preferred Supplier

Chosen and awarded by the world's leading vendors as preferred training partner.

Ahead of the Technology Curve

No matter your chosen technologies or platforms, we can help you stay one step ahead.

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.

Request Course Information

By submitting an enquiry, you agree to our privacy policy and receiving email and other forms of communication from us. You can opt-out at any time.

Select and book a course

Can't find a date you like?

Contact sales

Stay ahead of the technology curve

Don’t let your tech outpace the skills of your people

Quality Instructors and Content

Expert instructors with real world experience and the latest vendor-approved in-depth course content.

Partner-Preferred Supplier

Chosen and awarded by the world's leading vendors as preferred training partner.

Ahead of the Technology Curve

No matter your chosen technologies or platforms, we can help you stay one step ahead.

Looking for more course options?