< All Topics

Microsoft Excel (Dashboards & Reports)


Course Overview

No matter what business you’re in, reports have become a staple in the workplace. But what good is a report if no one reads it, or even worse, understands it. This all new edition of Excel Dashboards & Reports is here to help you make meaning of all your data and turn it into clear and actionable visualizations. Fully updated for the latest business intelligence and spreadsheet tools in Excel, this course will show you how to analyse large amounts of data, quickly slice data into various views on the fly, automate redundant reporting, create eye-catching visualizations and more.

Learn how to move beyond reporting data with simple tables, rows and columns, design high-impact reports, dashboards and visuals.

We will walk you through a wide array of technical and analytical concepts to give you the background you need to select the right tool for interpreting and displaying data

The material will include how to build a chart, work with pivot tables, group and bucket your data, represent trends, create what-if analyses and increase the value of your reports.

Target Audience 

Anyone that analyses and compiles reports using Excel data


Excel Level 3

Course Outline:

Module 1: Getting started with Excel dashboards and reports

Part 1:  Getting in the dashboard state of mind

  • Defining dashboards and reports
  • Preparing for greatness
  • A quick look at dashboard design principles

Part 2:  Building a super model

  • Data modelling best practices
  • Excel functions that really deliver
  • Using smart tables that expand with data

Module 2:  Building basic dashboard components

Part 1:  Dressing up your data tables

  • Table design principles
  • Getting fancy with custom number formatting

Part 2:  Sparking inspiration with sparklines

  • Introducing sparklines
  • Understanding sparklines
  • Customizing sparklines

Part 3:  Formatting our way to visualizations

  • Enhancing reports with conditional formatting
  • Using symbols to enhance reporting
  • The magical camera tool

Part 4:  The pivotal pivot table

  • An introduction to the pivot table
  • The four areas of a pivot table
  • Creating your first pivot table
  • Customizing your pivot table reports
  • Creating useful pivot-driven views

Module 3:  Building advanced dashboard components

Part 1:  Charts that show trending

  • Trending do’s and don’ts
  • Comparative trending
  • Emphasizing periods of time
  • Other trending techniques

Part 2:  Grouping and bucketing data

  • Creating top and bottom displays
  • Using histograms to track relationships and frequency
  • Emphasizing top values in charts

Part 3:  Displaying performance against a target

  • Showing performance with variances
  • Showing performance against organizational trends
  • Using a thermometer-style chart
  • Using a bullet graph
  • Showing performance against a target range

Module 4:  Advanced reporting techniques

Part 1:  Macro-charged dashboarding

  • Why use a macro
  • Recording your first macro
  • Running your macro
  • Enabling and trusting macros
  • Excel macro examples

Part 2:  Giving users an interactive interface

  • Getting started with form controls
  • Using the button control
  • Using the check box control
  • Using the options button control
  • Option button examples
  • Using the combo box control
  • Combo box examples
  • Using the list box control
  • List box examples

Part 3 :  Adding interactivity with pivot slicers

  • Understanding slicers
  • Creating a standard slicer
  • Formatting slicers
  • Controlling multiple pivot tables with one slicer
  • Creating a timeline slicer
  • Using slicers as form controls

Module 5:  Working with the outside world

Part 1:  Using external data for your dashboards and reports

  • Importing data from Microsoft Access
  • Importing data from SQL Server

Part 2:  Sharing your workbook with the outside world

  • Protecting your dashboards and reports
  • Linking your Excel dashboards to PowerPoint
  • Distributing your dashboards via PDF
  • Distributing your dashboards to Skydrive
  • Limitations when publishing to the web

Module 6:  Working with the outside world

Part 1:  Ten charts design principles

  • Avoid fancy formatting
  • Skip the unnecessary chart junk
  • Format large numbers where possible
  • Use data tables instead of data labels
  • Make effective use of chart titles
  • Sort your data before charting
  • Limit the use of pile charts
  • Don’t be afraid to parse data into separate charts
  • Maintain appropriate aspect ratios
  • Don’t be afraid to use something other than a chart

This form is currently undergoing maintenance. Please try again later.
Table of Contents