Annotating a stacked column chart

I’m trying to annotate a stacked column chart and want only a single annotation at the top of the stacked columns. From other posts I figured out I have to use comment rather than cells, since the cells will be applied to each series in the stack. This still leaves a problem that the annotation doesn’t appear above the stacked column, but within the top series (ie, the one I labeled). I suspect this is due to the way the annotation system uses the labels, which don’t offer an “above” option for stacked columns.

I was able to get the desired effect by creating a new series with the sum and adding it to the chart with a chart type of line. I then set the line color to none, removed the series name from the legend, and added the annotation as comments. Crufty, but it worked and let me put the annotation above the column.

In other related posts, the question arises whether comments are a possible alternative to an annotation column. In a sense, the answer is always yes, if you’re willing to go to enough work. But a coulmn for the annotation is preferable as it is always visible, easily cut-and-pasted, and can contain formulas so that the values stay in sync with the chart (even if they do have to by manually updated using the Annotate command.) In terms of simplifying the UI issue, perhaps in multi-series charts, the annotation command accepts either comments, a single column of annotations, or an many columns as there are series. In the first two cases behavior is unchanged from the present implementation. In the last case, the annotation columns are applied to the corresponding series. Thus, no change in the dialog is required.

Good suggestion, I’ll try to remember this while I rewrite the entire thing.

The trick with the invisible chart series is useful in several scenarios.

During my experience with excel I’ve compiled a small TODO list of probably useful functions that right now excel lacks.

  1. XY scatter from PivotGraph
    PivotGraphs are great, but there is no way to gey XY scatter plots with them.
    A possibility to have XY scatter plots from a pivottable/graph still exists though.
    This is done by copying the series string (=SERIES(;;)) from the pivotGraph
    and substituting an existing series string in an XY graph… this way you can have XY graphs that changes with your pivot table…
    Of course this must be done manually and if the number of series changes they have to be added or removed manually…
    The VBA code should be easy though.
    For what concerns the implementation, I was thinking about:
    You select the PivotGraph and another XY graph.
    You press a button and it automatically copies all the series from the pivot graph to the XY graph!