Excel Charts for Dashboards

Masterclass Description


The Excel Charts for Dashboards masterclass will look at techniques which are useful for working with charts in any situation, but​​ which are particularly helpful for making a great dashboard.

The first topic is a look at one effective way to structure your dashboard, to make it easy to update and maintain.

The class will then cover techniques for laying out multiple chart elements, particularly chart elements that share styles. It will describe alignment techniques, data handling, and how to keep automatic axes the same across charts with different data.

We will spend a little time discussing good and bad chart types, formats, colors,​​ and effects. But not too much time, because everyone “knows” this already and it gets boring.

The next topic is about dynamic and interactive charts. Dynamic charts update painlessly when data changes and the dashboard recalculates. Interactive charts are​​ dynamic charts with mechanisms that allow consumers of your dashboard to look at the data in different ways. These mechanisms include formulas, controls from the Form and ActiveX menus, data validation, and slicers.

Finally we get into “tips and tricks”. (It’s not really a trick if you understand how it works.) Conditional formatting is not part of charts the way it is part of the worksheet, but with some clever formulas and formatting you can make it work in your charts, as a few examples will illustrate.​​ Then there will be some smart uses for hidden (“dummy”) series, to help with axes and labeling. Finally we’ll look at advanced VBA techniques, such as chart events and chart UDFs, that can add the final touches to your dashboard.


Masterclass Outline


  • Dashboard Structure

    • Source Data

    • Staging Area

    • Dashboard

    • Sub-Pages

  • Laying Out Dashboard: Multiple Chart Elements

    • Make one perfect chart

    • Make copies

    • Arrange charts

      • Ctrl-Shift-Alt while dragging

    • Change Data

      • Properties follow data point

      • Drag highlights

      • SERIES​​ formula

      • Source Data Dialog

    • Align axes

      • Dummy series

  • Formatting – Good and Bad

    • Chart Types

    • Colors and Styles

  • User Interaction

    • Dynamic Charts

    • Interactive Charts

      • Form Controls

      • ActiveX Controls

      • Data Validation

      • Slicers

  • Tips and Tricks

    • Conditional Formatting

      • Color by Category

      • Run chart outliers

    • Dummy Series

      • Vertical Text Axis

      • Highlight Min/Max

      • Label last plotted point

    • VBA

      • Chart Events

      • Chart UDFs