Marking Selected Time Frames on Charts

[John Gregory]

The task involves making comments about time periods where two to three line
graphs suggest something significant. First thought was to simply draw
vertical lines at the beginning and end of each time slice. But that
requires drawing and may require a more steady hand than mine. I'm looking
for something neat and fast. I like shaded areas for these time frames but
that seems to be just a refinement in the drawing function; I still have to
draw line.

Any suggestions? I'm sure this is a pretty common issue that Excel probably
has the capability of handling with little effort... if I just knew where to
look.

 

[Andy Pope]

Hi John,

Check out the examples below on how to add data lines to a chart.

http://peltiertech.com/Excel/Charts/DummySeries.html#AddLine
http://andypope.info/charts/averageline.htm
http://www.tushar-mehta.com/excel/charts/straight_lines/index.html

Also check Jon's coloured band example
http://peltiertech.com/Excel/Charts/ColoredVerticalBand.html

Cheers
Andy

 

[Tushar Mehta]

You can also use a column chart to highlight regions of interest. For
an application see http://www.tushar-mehta.com/excel/ngstats/overview-
daily.html.

Suppose you have daily data in B:C starting with B6 and you want to
hightlight weekends

7/1/2004 888
7/2/2004 698
7/3/2004 379
7/4/2004 437
7/5/2004 736
7/6/2004 1127
7/7/2004 1202
7/8/2004 1217
7/9/2004 1176
7/10/2004 436
7/11/2004 436
7/12/2004 1000
7/13/2004 1186
7/14/2004 1237
7/15/2004 1068
7/16/2004 1087
7/17/2004 379
7/18/2004 428
7/19/2004 975

Then, in D6 enter the formula
=OR(WEEKDAY(B6)=1,WEEKDAY(B6)=7)*MAX($C$6:$C$36) and copy to D736.

[Obviously, instead of selecting weekends, you would use whatever
criteria make sense to you]

Plot B637 in a line chart ensuring that column B is treated as x-
values. Now, select the series corresponding to column D and select
Chart | Chart Type... | and select the clustered column type. Double
click this column chart, from the Patterns tab set the Border to None,
the Area to some aesthetically appealing light color, and from the
Options tab set Overlap to zero and Gap Width to zero.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[John Gregory]

Thank you gentlemen. I'll try them all.

You can also use a column chart to highlight regions of interest. For
an application see http://www.tushar-mehta.com/excel/ngstats/overview-
daily.html.

Suppose you have daily data in B:C starting with B6 and you want to
hightlight weekends

7/1/2004 888
7/2/2004 698
7/3/2004 379
7/4/2004 437
7/5/2004 736
7/6/2004 1127
7/7/2004 1202
7/8/2004 1217
7/9/2004 1176
7/10/2004 436
7/11/2004 436
7/12/2004 1000
7/13/2004 1186
7/14/2004 1237
7/15/2004 1068
7/16/2004 1087
7/17/2004 379
7/18/2004 428
7/19/2004 975

Then, in D6 enter the formula
=OR(WEEKDAY(B6)=1,WEEKDAY(B6)=7)*MAX($C$6:$C$36) and copy to D736.

[Obviously, instead of selecting weekends, you would use whatever
criteria make sense to you]

Plot B637 in a line chart ensuring that column B is treated as x-
values. Now, select the series corresponding to column D and select
Chart | Chart Type... | and select the clustered column type. Double
click this column chart, from the Patterns tab set the Border to None,
the Area to some aesthetically appealing light color, and from the
Options tab set Overlap to zero and Gap Width to zero.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

The task involves making comments about time periods where two to three line
graphs suggest something significant. First thought was to simply draw
vertical lines at the beginning and end of each time slice. But that
requires drawing and may require a more steady hand than mine. I'm looking
for something neat and fast. I like shaded areas for these time frames but
that seems to be just a refinement in the drawing function; I still have to
draw line.

Any suggestions? I'm sure this is a pretty common issue that Excel probably
has the capability of handling with little effort... if I just knew where to
look.