Excel Charting Question re: shading recession years

B

bchad

New member
Joined
Jun 18, 2026
Messages
0
Reaction score
0
I’m trying to do one of those line charts that has shaded regions to represent recessions. For example, something like real GDP growth (or unemployment or whatnot) over time, with vertical shaded areas for the years when recessions are official.
Anyone know how to do this in Excel, other than manually drawing boxes over the regions and setting the transparency so they don’t cover up your lines?
Just to be on the same page, you can assume my spreadsheet column A is Date (typically Year/Quarter), column B is GDP, and column C is 1=recession; 0=not recession. (though if column C has to be in another format, go ahead and tell me how I need to format it). How do I get a chart that shows the line and shades the recession years so it’s easy to see when they happen?
 
Add both series to the graph. Right click on the recession data -> Format Data Series and click the Secondary Axis radio button. Go back to the graph and then “Change Series Chart Type…” and pick 100% stacked and click OK. Then format second axis to min/max of 0/1.
 
recycler Wrote:
——————————————————-
> Add both series to the graph. Right click on the
> recession data -> Format Data Series and click the
> Secondary Axis radio button. Go back to the graph
> and then “Change Series Chart Type…” and pick
> 100% stacked and click OK. Then format second axis
> to min/max of 0/1.
nice solution.
 
recycler Wrote:
——————————————————-
> Add both series to the graph. Right click on the
> recession data -> Format Data Series and click the
> Secondary Axis radio button. Go back to the graph
> and then “Change Series Chart Type…” and pick
> 100% stacked and click OK. Then format second axis
> to min/max of 0/1.
I was thinking about this as I typed in the question. I haven’t tried it yet, but I thought it would have problems with diagonal lines connecting the non-recession years to recession years. I know that’s a niggling point, and it’s better than nothing, but I was hoping for something better.
Oh wait, you mean stacked column chart. Then you just set the gap space to 0 and eliminate lines so that only the fill shows. I just tried it, and it works pretty well (small spaces still between the bars, but good enough).
 
See, this is the real problem with recessions - they complicate things in excel. Better to just avoid recessions altogether.
 
maratikus Wrote:
——————————————————-
> recycler Wrote:
> ————————————————–
> —–
> > Add both series to the graph. Right click on
> the
> > recession data -> Format Data Series and click
> the
> > Secondary Axis radio button. Go back to the
> graph
> > and then “Change Series Chart Type…” and pick
> > 100% stacked and click OK. Then format second
> axis
> > to min/max of 0/1.
>
> nice solution.
yeah, i always did shaded boxes, very slick
 
nice to know, I am glad you asked. I just did something similar to this, and drew in the shaded regions with shaded rectangles! I like the charting solution much better.
 
LPoulin133 Wrote:
——————————————————-
> See, this is the real problem with recessions -
> they complicate things in excel. Better to just
> avoid recessions altogether.
I plan to bring this up at the next Excel User’s Union meeting. Are you planning to be there, LPoulin?
 
bchadwick Wrote:
——————————————————-
> LPoulin133 Wrote:
> ————————————————–
> —–
> > See, this is the real problem with recessions -
> > they complicate things in excel. Better to
> just
> > avoid recessions altogether.
>
>
> I plan to bring this up at the next Excel User’s
> Union meeting. Are you planning to be there,
> LPoulin?
…are you kidding, I haven’t been able to sleep in weeks - the anticipation is killing me, I can’t wait for it. It’s going to be an excel-ent experience.
 
This was always a pain in excel.
Normally I would just import it to Factset and use their recession shader.
 
bchadwick Wrote:
——————————————————-
> recycler Wrote:
> ————————————————–
> —–
> > Add both series to the graph. Right click on
> the
> > recession data -> Format Data Series and click
> the
> > Secondary Axis radio button. Go back to the
> graph
> > and then “Change Series Chart Type…” and pick
> > 100% stacked and click OK. Then format second
> axis
> > to min/max of 0/1.
>
>
> I was thinking about this as I typed in the
> question. I haven’t tried it yet, but I thought
> it would have problems with diagonal lines
> connecting the non-recession years to recession
> years. I know that’s a niggling point, and it’s
> better than nothing, but I was hoping for
> something better.
>
> Oh wait, you mean stacked column chart. Then you
> just set the gap space to 0 and eliminate lines so
> that only the fill shows. I just tried it, and it
> works pretty well (small spaces still between the
> bars, but good enough).
Are you using 2007? You should not get any gaps if you use category instead of date for the x-axis format.
When wanting to use date and eliminate gaps, I make a dummy column of daily dates from your start date to end date. Fill in the 0 and 1’s using this formula, where sheet Recession_figures contains a table with the start date and end dates of recessions.
=IF(ISBLANK(Data!A3),”“,AND(A3>=INDEX(Recession_Figures!$F$2:$F$13,MATCH(A3,Recession_Figures!$F$2:$F$13)),A3<=INDEX(Recession_Figures!$G$2:$G$13,MATCH(A3,Recession_Figures!$F$2:$F$13)))*1)
Then plot it on the second axis.
 
You must log in or register to reply here.
Back
Top