Excel Dashboards Complete (2 Days)

Excel Dashboards Complete (2 Days)

This course is for experienced Excel users who wish to learn how to create interactive Excel Dashboards and Visualisation Models using analytical tools, visual aids and options.

Learning Outcomes

By the end of this course students will be able to work confidently with advanced nested functions, use features like the scenario manager, consolidation, advanced filtering options as well as pivot tables and Macros.

Prerequisites

This course is designed for students with at least Intermediate level Excel knowledge.

Introductions

  • Overview of dashboard reporting
  • Understanding the purpose of the dashboard
  • How will the dashboard be used?
  • Dashboard Design Principles
  • Separating data, analysis and presentation
  • Organising your data

Analysing Data

  • Lookup functions - Vlookup(), Hlookhup()
  • Sumproduct function
  • Choose() function
  • Using Excel Lists/Tables to manage data

Dashboard Charts

  • Creating Mini Charts
  • Managing Legends and X/Y axes
  • Adding a data series to a chart
  • Using Primary and Secondary value axes
  • Adding Autoshapes to a chart
  • Creating Dynamic text boxes
  • Creating better charts in Excel

Figures that Use Charts and Worksheets

  • Simple chart formatting
  • Row/Column control
  • Resizing charts
  • Forcing charts to work together
  • Using common scaling

Formula Driven Visualisations

  • Creating "in-cell" charts
  • Creating visualisations with symbols
  • Creating a formula histogram
  • Adding a cumulative percent

Excel Camera Tools

  • Introduction to the Excel Camera tool
  • Using the Camera tool
  • Cool uses for the camera tool
  • Displaying tables with different column widths/row heights
  • Rotating reports
  • Variable length lists
  • Traffic lights

Showing Trends

  • Line chart trends
  • Area chart trends
  • Vertical scale
  • Logarithmic scale
  • Labelling do's and don'ts
  • Showing side-by-side comparison
  • Using a secondary axis
  • Marking significant events
  • Representing forecasts
  • Creating a sparkline

PivotTable Analysis

  • Using pivot tables to create interactive views
  • Creating a histogram with a pivot table
  • Highlighting top values

Performance against target

  • Thermometer style charts
  • Bullet graphs
  • Creating qualitative bands
  • Performance against a target range

Designing an Interactive Interface

  • Creating macros to update chart data
  • Using Data validation and in-cell lists
  • Adding a check box to include a trendline
  • Using Option buttons to dynamically feed data
  • Creating synchronised Pivottables using a combo list

Using External Data

  • Importing external data
  • Using MS Query to obtain specific data

Final Project

  • Putting it all together.
  • Using functions to pull data into charting tables.
  • Designing the Mini-chart format.
  • Setting up the Dashboard layout

What people say about us