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.
Laying Out Dashboard: Multiple Chart Elements
Make one perfect chart
Ctrl-Shift-Alt while dragging
Properties follow data point
Source Data Dialog
Formatting – Good and Bad
Colors and Styles
Tips and Tricks
Color by Category
Run chart outliers
Vertical Text Axis
Label last plotted point