Overlaying Two Xcelsius charts for Actual/Target

11

In Feb. 2008, I wrote an article, Overlay 2 Xcelsius Charts. Since then, this technique has been burried in the archives and many people have missed it. Thank’s to Jesse Aden’s own creation of this solution, he has provided new guidance that is extremely helpful in the article below:

By Jesse Aden
VIA Consultants

I often got frustrated trying to show a bar or column chart with the projected/targeted data next to the actual, as it wasn’t very visually appealing. To fix this, I tried using a stacked column chart, which would look alright, but the figures would always be off.

Fixing this turned out to be very simple. Just overlap (exactly) two of the same bar/column charts.

Download Source File

A few things to watch out for:
1. The top graph should display the actual (current) data, where the bottom should be the projected or targeted.
2. On the top graph, make transparent the background, major & minor markers, and the legend (if using alerts).
3. The default colors for both are the same, so until you either apply alerts, or change the series color, it will look like it doesn’t work.
4. After changing the color, make the back chart’s bars bigger than the front charts’. It shows exactly what is intended, and accounts for when the target is made, or passed.
5. The titles have to be the same. Don’t put in a title on one and delete the title for the second.
6. The scales have to be the same. This means that automatic scaling is out of the question, and the scale must be based off of the highest number of the displayed series. For example, I have excel search through the display values (target and actual) to find the MAX, then round-up and add a certain number over that. It looks better this way, instead of just rounding up.
7. Finally, remember that if you use alerts, you cannot use a legend.

Share.

11 Comments

  1. Interesting ! I have been trying to overlay a stack column with a line chart and it seems to be misaligned (horizontally). I tried to fix it .. but it went off on vertical axis.

    Is there a way you could help or share a sample with us?

  2. I am getting the data via Web Services for these two overlayed charts. For the scale of these charts,I am using MAX function to read maximum value from the range of data. Its not able to read MAX value from the data that comes from Web Services. Can anyone help me with this issue

Leave A Reply