MS Excel - Pivot Tables and Dashboards

Business Intelligence can help an organization identify cost-cutting opportunities, uncover new business opportunities, recognize changing business environments, identify data anomalies and create widely accessible reports. 


By attending the course delegates will be able to:

  • Analyse large amounts of data and report the results in a meaningful way
  • Get better visibility into data from different perspectives
  • Add interactive controls to show various views
  • Automate repetitive tasks and processes
  • Create eye-catching visualisations
  • Create impressive dashboards and What-If analyses
  • Access external data source sources to expand your message


Module 1:  Moving from spreadsheets to Dashboards

  • Introducing Dashboards
  • Dashboards vs Reports
  • Dashboard design principles
  • Building your Data Model
  • Data Model best practice
  • Excel functions for your data model e.g. VLOOKUP

Module 2:  Excel Chart Basics

  • Introducing Excel Charts
  • Understanding the different Chart types
  • Working with charts and chart series
  • Formatting and customizing charts

Module 3:  Going beyond tables and Charts

  • Using PivotTables and PivotCharts
  • Filtering your data e.g. using slicers
  • Introducing and creating Excel Sparklines
  • Chartless visualization techniques e.g. dynamic labels, text boxes, formula driven labels

Module 4:  Creating advanced Dashboard components

  • Components that show trending i.e.
    • Trending Do’s and Don’ts
    • Comparative trending
    • Emphasizing periods of time
    • Trending techniques
  • Components that group data i.e.
    • Listing top and bottom values
    • Using histograms to track relationships and frequency
    • Emphasizing top values in charts
  • Components that show performance against a target i.e.
    • Show performance with variances
    • Show performance against organizational trends
    • Create a thermometer style chart
    • Create a bullet graph

NOTE:  In this module, we create a sample dashboard using the various techniques learnt

Module 5:  Automating your Dashboards and Reports

  • Macro charged reporting
  • Adding interactive controls to your dashboard

Module 6:  Working with the outside world

  • Importing MS Access data into Excel
  • Sharing your workbook with the outside world

Want to analyse large amounts of data and report the results in a meaningful way?


Contact Details

Contact Person: 

Bianca van Wyk
Course Details

Course Delivery Method: 

In Class In Class

Presented Presented Courses

In House In-House

On Demand On Demand

Course Duration: 

1 day



Course fee Includes: 

Lunch and refreshments