Adding Dynamic Annotations for your Charts

Here’s a quick idea for anyone who needs to show annotations or comments on their charts. Tie your annotations to a check box so that users can interactively decide to show them or not.

The annotations themselves are simply chart data labels that are showing Category names instead of values. These labels are tied to an invisible data series in the chart. This means that as your chart data changes, the annotations move with the other chart elements. The check box is a Form control that drives a simple IF formula. IF the check box is TRUE then annotation series gets plotted on the chart, else the annotations series is ignored.

This neat trick involves no VBA. It uses a few simple modeling techniques that have been around for years. In fact, this trick uses the same steps described in this post I pushed out back in 2009. Follow that post to see how it’s done.

Also, feel free to download the sample workbook to see it in action.

5 thoughts on “Adding Dynamic Annotations for your Charts

  1. peter Bedson

    How do you add the leading lines linking the comments to the data? Is the reason I cant do this because my employers are cheap and I am still on Excel 2007???

  2. alex

    I canโ€™t select different horizontal axis labels data source, once i change one it changes them all.

Leave a Reply

Your email address will not be published. Required fields are marked *