Highlighting Operating Hours

[Rebecca R]

I have an XY scatter plot of temperature inside an office building throughout
different times of day across a 3 week period. What I would like to see are
coloured bars that highlight when the office is open (operating hours).

I tried adding colour-filled rectangles with 50% transparency, but I am
having printing issues. It looks fine on screen, but when I go to print the
bars are scaled differently than the chart thus they don't line up properly.
I think this might be a bug in Excel. This issue comes and goes; sometimes it
prints OK, most of the time it doesn't (and I haven't changed any settings!)

Is there a better way to do this? I must admit, using the coloured-bars is
very tedious. If there is a more elegant way to colour-code the chart then I
am all for it!

Thanks,

Rebecca

 

[Andy Pope]

Hi,

Rather than shapes use a combination chart. An additional series plotted as
column chart is probably the simplest solution.
See Jon's example of vertical bands which should give you a starting point.

http://peltiertech.com/Excel/Charts/ColoredVerticalBand.html

If not post back with details of x-axis values. Dates or numbers?

Cheers
Andy

 

[Rebecca R]

Hi Andy,

The x-axis is dates & times. The y-axis is temperature values. I don't see
how I can use a stacked bar graph for this.

Thanks!

Rebecca

 

[Del Cotter]

The x-axis is dates & times. The y-axis is temperature values. I don't see
how I can use a stacked bar graph for this.

Don't worry, the horizontal bar chart will have its own axes separate
from the scatter chart. So its "y-axis" (actually its x-axis) can be one
category, and it "x-axis" will be the hours the office is open and
closed. Study Jon's example, and adapt it to your case; I'm confident it
will work for you.

To see how this can make a time marker for your office opening and
closing, see this other example of Jon's, using the same technique:

http://peltiertech.com/Excel/Charts/GanttChart.html

It's like your office "open" and "closed" hours are two tasks in a Gantt
chart, only we really want them to fill the background, to make a field
on which the scatter markers are displayed.

But if it really bothers you, there is an alternative. Instead of a
stacked horizontal bar chart, use a stacked area chart with a
"Time-scale" x-axis instead. It will still have to have its own x and y
axes separate from the scatter chart, but at least it will be oriented
the same way for you instead of at right angles.

Whereas the horizontal stack bar design will have as many series as
there are cycles of opening and closing, there will be a lot of series
in that method, which you would delete from the legend if you are using
a legend. The stacked area method has only two series, one for "open"
and one for "closed" (it could have only one, with the background
standing for the alternative). In this scheme you would make one series
maximum when the office is open and zero when it is closed, and vice
versa for the other. Taking advantage of the Time-scales ability to do
vertical lines, you would repeat a time at opening and closing time,
like this:

Open Closed
22 100% 0
22 0 100%
23 0 100%
23 100% 0

At time "22" the "Open" area goes vertically down from 100% to 0, and
then at time "23" it goes vertically up again from 0 to 100%, and vice
versa for the "Closed" area, making rectangles of open and closed color
that you control from the spreadsheet, instead of having to position
graphical rectangles.

Since your hours are fractions of a day, you won't be able to use the
"Time-scale" in the obvious way, because it doesn't do hours. Just let
the "days" stand for hours or minutes and it will work fine.