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