DURATION: 2 DAYS

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

Prerequisite

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.