Excel Dashboards & Advanced Analytics: 2 Day Course

< Back to Excel Course Outlines

This course is for advanced users of Excel you need to created automated dashboards based on advanced data analytics.  Topics covered include: dashboard design principles, advanced charting, advanced formulas, interactivity and automation using controls and VBA and dashboard security. Delegates will need to be comfortable with the content of our Fundamentals, Intermediate and Advanced courses to take part in this training.  Download the course outline in pdf format

Dashboard Design Principles
  • Building a data model
    • The data layer
    • The analysis layer
    • The presentation layer
  • Documentation
  • Keeping it simple
  • Avoiding chart clutter
  • Emphasising / de-emphasising content
  • Positioning components to reflect weighting/importance
Advanced Charting Techniques
  • Displaying symbols on the category axis
  • Dynamic chart titles based on text formulas
  • Conditional formatting charts to pick out best, worst performing or positive vs negative values
  • Creating dynamic data labels using shapes
  • Using Sparklines
  • Small multiples/panel charts
  • Using the REPT function to compare data
  • Creating rollover buttons using the HYPERLINK function & VBA
Components that Display Trending Data
  • Line graphs
  • Area charts
  • Bar charts
  • Logarithmic scales
  • Useful techniques for displaying multiple time dimensions as axis labels
  • Comparative trending
  • Displaying forecasts
  • Smoothing data
  • Step charts
Components that Group Data
  • Histogram charts
  • Pareto charts
  • PivotCharts
Components that Display Performance vs Target
  • Displaying values against a median or target
  • Creating thermometer charts
  • Displaying a target band
  • Creating bullet charts
Interactive Controls
  • Check box controls
  • Option button controls
  • Combo box/ list box controls
  • Scroll bar controls
  • Using control outputs to display/hide data in a chart
Advanced Formula Work for Dashboards
  • Using the INDEX and MATCH functions to determine data for chart series
  • Using the IF function
  • Using the NA function to hide data from a chart
  • Using the CHOOSE function to select data for a chart
  • Using the INDIRECT function with charts
  • Utilising control outputs in formulas
Macros & VBA for Dashboards
  • Recording macros
  • Editing the VBA for recorded macros
  • Introduction to VBA
    • Writing your own macros
    • Declaring variables
    • Writing IF-Then-Else structures
    • Using control outputs in your VBA
    • Assigning macros to worksheet controls
Dashboard Security
  • Protect your dashboard at the worksheet level
  • Protect your dashboard at the workbook level
  • Protect your dashboard at the VBA project level
Avoiding Dashboard Lag

  • Top tips – dos and don’ts

Posted by Blue Pecan Computer Training