MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Chart Question


Posted by Jennifer Wood on February 01, 2002 9:40 AM

I'm looking for some help.

I have a line graph that plots GDP and stock market growth over time. I want to highlight or shade the time periods where the economy was in recession (ie June 81 to March 83) Is there a way to do this other than placed a shaded rectangle on top of the graph (not very exact and tend to "migrate")

Thanks!

Jennifer Wood


Posted by Bariloche on February 01, 2002 8:42 PM

A work-around

Jennifer,

I can't think of a way to do this elegantly, but a work-around would be to create another chart series with a very small "height" that covers the timeframe of the recession periods and plot it as a column type. Then adjust the gap and overlap options to 0 (Format Data Series > Options > Gap = 0, Overlap = 0).

For example, lets say you have x values from 0 to 10 and y values from 0 to 1. Put in a set of data points that goes from x = 1 to 3 and has a y value of 0.1 and then another group from x = 6 to 9 that also has a y value of 0.1 (put these all in the same row or column). Then add a new series to your chart consisting of this additional data. Format the series as I explained above and adjust the "height" of your columns to get the desired thinness of your timespan indicator (the new series).

Like I said, it ain't elegant, but it works ("kinda" anyway.)


good luck

Posted by Bariloche on February 01, 2002 8:49 PM

additional thoughts

Jennifer,

Now that I've thought about it a little harder, I think you might want instead to have the "columns" be the same height as your maximum y value (hard code the y axis maximum if necessary) and change the color to something light so that the line series is seen more easily. Actually this is probably closer to what you were thinking of anyway. So now I guess that rather than being a work-around this just might be the best way to do it.

enjoy