Marking Selected Time Frames on Charts

  • Thread starter Thread starter John Gregory
  • Start date Start date
J

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.
 
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 D7:D36.

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

Plot B6:D37 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
 
Thank you gentlemen. I'll try them all.


Tushar Mehta said:
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 D7:D36.

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

Plot B6:D37 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

jaygreg90 said:
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.
Click to expand...
Click to expand...
 
Back
Top