Time axis column and scatter/line graph

I need to make a combination graph with a time x-axis and two series: 3
columns at X = 1988, 1995, and 1999; and a set of X-Y points with date
(1/1/1980, etc.) as X. So far, I cannot make the columns appear on the axis
at the correct location within the time x axis, whether I use line or scatter
for my other series. The columns are just plotted equally spaced from each
other, ignoring the x-axis, or if I make sure that their x-axis is time, then
they plot as very skinny vertical lines at the correct places. Is there any
way of getting around this without using yearly averages for my x-y scatter
data?

Example:

Point data:
Date Value
1/1/1980 .5
2/5/1980 .1
5/1/1988 .6
10/7/1990 .4
12/1/1995 .1
12/1/1996 .2
3/10/1999 .3

Column data:
1988 30
1995 40
1999 70

You can change the bar width. Double click on the series to get to Format
Data Series. Select the Options tab and change the gap width to 0 to have
no gap between the cells.
"kraymond" <kraymond@discussions.microsoft.com> wrote in message
news:ADE1701B-E818-41FC-9B57-B37C7E1EB30C@microsoft.com...
> I need to make a combination graph with a time x-axis and two series: 3
> columns at X = 1988, 1995, and 1999; and a set of X-Y points with date
> (1/1/1980, etc.) as X. So far, I cannot make the columns appear on the
axis
> at the correct location within the time x axis, whether I use line or
scatter
> for my other series. The columns are just plotted equally spaced from
each
> other, ignoring the x-axis, or if I make sure that their x-axis is time,
then
> they plot as very skinny vertical lines at the correct places. Is there
any
> way of getting around this without using yearly averages for my x-y
scatter
> data?
>
> Example:
>
> Point data:
> Date Value
> 1/1/1980 .5
> 2/5/1980 .1
> 5/1/1988 .6
> 10/7/1990 .4
> 12/1/1995 .1
> 12/1/1996 .2
> 3/10/1999 .3
>
> Column data:
> 1988 30
> 1995 40
> 1999 70

Thanks, it worked. I thought I had tried that and failed, but it worked fine
this time as long as my time axis wasn't too long.

"Barb Reinhardt" wrote:

> You can change the bar width. Double click on the series to get to Format
> Data Series. Select the Options tab and change the gap width to 0 to have
> no gap between the cells.
> "kraymond" <kraymond@discussions.microsoft.com> wrote in message
> news:ADE1701B-E818-41FC-9B57-B37C7E1EB30C@microsoft.com...
> > I need to make a combination graph with a time x-axis and two series: 3
> > columns at X = 1988, 1995, and 1999; and a set of X-Y points with date
> > (1/1/1980, etc.) as X. So far, I cannot make the columns appear on the
> axis
> > at the correct location within the time x axis, whether I use line or
> scatter
> > for my other series. The columns are just plotted equally spaced from
> each
> > other, ignoring the x-axis, or if I make sure that their x-axis is time,
> then
> > they plot as very skinny vertical lines at the correct places. Is there
> any
> > way of getting around this without using yearly averages for my x-y
> scatter
> > data?
> >
> > Example:
> >
> > Point data:
> > Date Value
> > 1/1/1980 .5
> > 2/5/1980 .1
> > 5/1/1988 .6
> > 10/7/1990 .4
> > 12/1/1995 .1
> > 12/1/1996 .2
> > 3/10/1999 .3
> >
> > Column data:
> > 1988 30
> > 1995 40
> > 1999 70
>
>
>

The widest your columns can be in this circumstance is as wide as one
day. Even if your data is sparse, Excel's time scale axis reserves a
space for every day.

Something else you can try to improve the appearance of these skinny
columns: format the columns with a darker fill color and no border.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

kraymond wrote:

> Thanks, it worked. I thought I had tried that and failed, but it worked fine
> this time as long as my time axis wasn't too long.
>
> "Barb Reinhardt" wrote:
>
>
>>You can change the bar width. Double click on the series to get to Format
>>Data Series. Select the Options tab and change the gap width to 0 to have
>>no gap between the cells.
>>"kraymond" <kraymond@discussions.microsoft.com> wrote in message
>>news:ADE1701B-E818-41FC-9B57-B37C7E1EB30C@microsoft.com...
>>
>>>I need to make a combination graph with a time x-axis and two series: 3
>>>columns at X = 1988, 1995, and 1999; and a set of X-Y points with date
>>>(1/1/1980, etc.) as X. So far, I cannot make the columns appear on the
>>
>>axis
>>
>>>at the correct location within the time x axis, whether I use line or
>>
>>scatter
>>
>>>for my other series. The columns are just plotted equally spaced from
>>
>>each
>>
>>>other, ignoring the x-axis, or if I make sure that their x-axis is time,
>>
>>then
>>
>>>they plot as very skinny vertical lines at the correct places. Is there
>>
>>any
>>
>>>way of getting around this without using yearly averages for my x-y
>>
>>scatter
>>
>>>data?
>>>
>>>Example:
>>>
>>>Point data:
>>>Date Value
>>>1/1/1980 .5
>>>2/5/1980 .1
>>>5/1/1988 .6
>>>10/7/1990 .4
>>>12/1/1995 .1
>>>12/1/1996 .2
>>>3/10/1999 .3
>>>
>>>Column data:
>>>1988 30
>>>1995 40
>>>1999 70
>>
>>
>>

Yes, once I plotted the columns, I had to change the x-axis base unit to 1
year to make it look half-way decent. That made all of my other points plot
vertically directly above the middle of the year, instead of on the specific
dates like I wanted.
It was the best I could do in the end.


"Jon Peltier" wrote:

> The widest your columns can be in this circumstance is as wide as one
> day. Even if your data is sparse, Excel's time scale axis reserves a
> space for every day.
>
> Something else you can try to improve the appearance of these skinny
> columns: format the columns with a darker fill color and no border.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> kraymond wrote:
>
> > Thanks, it worked. I thought I had tried that and failed, but it worked fine
> > this time as long as my time axis wasn't too long.
> >
> > "Barb Reinhardt" wrote:
> >
> >
> >>You can change the bar width. Double click on the series to get to Format
> >>Data Series. Select the Options tab and change the gap width to 0 to have
> >>no gap between the cells.
> >>"kraymond" <kraymond@discussions.microsoft.com> wrote in message
> >>news:ADE1701B-E818-41FC-9B57-B37C7E1EB30C@microsoft.com...
> >>
> >>>I need to make a combination graph with a time x-axis and two series: 3
> >>>columns at X = 1988, 1995, and 1999; and a set of X-Y points with date
> >>>(1/1/1980, etc.) as X. So far, I cannot make the columns appear on the
> >>
> >>axis
> >>
> >>>at the correct location within the time x axis, whether I use line or
> >>
> >>scatter
> >>
> >>>for my other series. The columns are just plotted equally spaced from
> >>
> >>each
> >>
> >>>other, ignoring the x-axis, or if I make sure that their x-axis is time,
> >>
> >>then
> >>
> >>>they plot as very skinny vertical lines at the correct places. Is there
> >>
> >>any
> >>
> >>>way of getting around this without using yearly averages for my x-y
> >>
> >>scatter
> >>
> >>>data?
> >>>
> >>>Example:
> >>>
> >>>Point data:
> >>>Date Value
> >>>1/1/1980 .5
> >>>2/5/1980 .1
> >>>5/1/1988 .6
> >>>10/7/1990 .4
> >>>12/1/1995 .1
> >>>12/1/1996 .2
> >>>3/10/1999 .3
> >>>
> >>>Column data:
> >>>1988 30
> >>>1995 40
> >>>1999 70
> >>
> >>
> >>
>

You could plot the other data on the secondary axis: add at least one
series, double click on it, and on the Axis tab, select Secondary. This
only gives you a secondary Y axis. Choose Chart Options from the Chart
menu, and on the Axes tab, check the secondary category axis, and if
desired, uncheck the secondary Y axis. Format the scale of the secondary
X axis so it starts on the first day of the first year on the primary
axis, and ends on the last day of the last year on the primary axis.
Then if you want to hide the secondary axis, double click on it, and on
the Patterns tab, choose every None option you can find.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

kraymond wrote:
> Yes, once I plotted the columns, I had to change the x-axis base unit to 1
> year to make it look half-way decent. That made all of my other points plot
> vertically directly above the middle of the year, instead of on the specific
> dates like I wanted.
> It was the best I could do in the end.
>
>
> "Jon Peltier" wrote:
>
>
>>The widest your columns can be in this circumstance is as wide as one
>>day. Even if your data is sparse, Excel's time scale axis reserves a
>>space for every day.
>>
>>Something else you can try to improve the appearance of these skinny
>>columns: format the columns with a darker fill color and no border.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>kraymond wrote:
>>
>>
>>>Thanks, it worked. I thought I had tried that and failed, but it worked fine
>>>this time as long as my time axis wasn't too long.
>>>
>>>"Barb Reinhardt" wrote:
>>>
>>>
>>>
>>>>You can change the bar width. Double click on the series to get to Format
>>>>Data Series. Select the Options tab and change the gap width to 0 to have
>>>>no gap between the cells.
>>>>"kraymond" <kraymond@discussions.microsoft.com> wrote in message
>>>>news:ADE1701B-E818-41FC-9B57-B37C7E1EB30C@microsoft.com...
>>>>
>>>>
>>>>>I need to make a combination graph with a time x-axis and two series: 3
>>>>>columns at X = 1988, 1995, and 1999; and a set of X-Y points with date
>>>>>(1/1/1980, etc.) as X. So far, I cannot make the columns appear on the
>>>>
>>>>axis
>>>>
>>>>
>>>>>at the correct location within the time x axis, whether I use line or
>>>>
>>>>scatter
>>>>
>>>>
>>>>>for my other series. The columns are just plotted equally spaced from
>>>>
>>>>each
>>>>
>>>>
>>>>>other, ignoring the x-axis, or if I make sure that their x-axis is time,
>>>>
>>>>then
>>>>
>>>>
>>>>>they plot as very skinny vertical lines at the correct places. Is there
>>>>
>>>>any
>>>>
>>>>
>>>>>way of getting around this without using yearly averages for my x-y
>>>>
>>>>scatter
>>>>
>>>>
>>>>>data?
>>>>>
>>>>>Example:
>>>>>
>>>>>Point data:
>>>>>Date Value
>>>>>1/1/1980 .5
>>>>>2/5/1980 .1
>>>>>5/1/1988 .6
>>>>>10/7/1990 .4
>>>>>12/1/1995 .1
>>>>>12/1/1996 .2
>>>>>3/10/1999 .3
>>>>>
>>>>>Column data:
>>>>>1988 30
>>>>>1995 40
>>>>>1999 70
>>>>
>>>>
>>>>