COURSE INFORMATION

The aim of the course is to enable you to understand the tools available in Excel to summarise and present data. The course covers a variety of options that Excel and its Add-ins can give you to display summarised data in a Dashboard. It takes you through the steps of preparing your data to the final visual display of the data.

  • Preparing and shaping your data
  • Introduction to Power Query
  • Power Pivot
  • Dashboard Creation with Pivot Tables, Charts and Slicers
  • Overview of 3D mapping

Preparing and shaping your data

  • Importing data
  • Organising data
  • Trimming, Cleaning, concatenating, splitting using Functions

Introduction to Power Query

  • Understand the Power Query window
  • Understand where data can be imported from
  • Create a simple query
  • Transform by moving, splitting, merging and removing
  • Create custom columns
  • Load data into a worksheet or data model
  • Generate a Date table

Power Pivot

  • Build data models
  • The importance of Tables
  • Understand and create relationships between tables
  • Building DAX (Data Analysis eXpressions) formulas

Dashboard Creation with Pivot Tables, Charts and Slicers

  • Summarise with Pivot Tables and Pivot Charts
  • Create a Pivot Table from Power Pivot data
  • Use the GETPIVOTDATA function
  • Create charts with the same settings from a chart template
  • Use Sparklines
  • Add Slicers for filters
  • Apply Conditional formatting with Icons, Data Bars or Colour scales
  • Use Form controls to make charts more dynamic
  • Using the Camera tool
  • Use the Analyse Data tool

3D mapping overview

  • What data is required?
  • Create a 3D map
  • Visualise your data
  • Change the look of your 3D map
  • A sound knowledge of Excel Formula and Functions, Named Ranges, Conditional Formatting, Sorting data, Filtering data, Tables, Subtotals and Data Consolidate is recommended.
  • A knowledge of Database relationships is beneficial.
  • £295
  • One day trainer led or virtual