I'm plotting a number of data series on an x-y scatter chart. Values are
recorded daily over an 18 month period and are plotted against time on the
x-axis. To make the graph look neat I want to place tick marks at monthly
intervals at the first of each month. Because the number of days in the
month varies it is not possible to select an appropriate major unit in the
format axis box. As a result, if I select for example 31 as the major unit
then the monthly tick marks progressively move to a later date in the month -
starting from 01 August 05 and by March 07 my tick mark is placed at the
13th. Is there anyway to overcome this? Thanks.

Yes, there is a way to overcome this issue. You can use a combination of custom number formatting and a helper column to create monthly separation tick marks on the x-axis.

Here are the steps:

1. Create a helper column next to your date column. In the first cell of the helper column, enter the formula "=TEXT(A2,"mmm yy")" (assuming your date column starts in cell A2). This will extract the month and year from the date and format it as "mmm yy" (e.g. "Aug 05").
2. Copy the formula down the helper column for all the dates in your data series.
3. Select your chart and go to the "Format Axis" pane.
4. Under "Axis Options", set the "Minimum" to the first day of the first month in your data series (e.g. "01 Aug 05").
5. Set the "Maximum" to the last day of the last month in your data series (e.g. "31 Jan 07").
6. Set the "Major unit" to "1". This will ensure that there is a tick mark for every day on the x-axis.
7. Under "Number", select "Custom" and enter the format code "mmm yy" (without the quotes). This will format the dates on the x-axis as "mmm yy".
8. Under "Axis Options", set the "Tick mark type" to "Outside". This will ensure that the tick marks are visible outside the chart area.
9. Under "Axis Options", set the "Tick mark spacing" to "1". This will ensure that there is a tick mark for every day on the x-axis.
10. Under "Axis Options", set the "Tick mark labels" to "Low". This will ensure that the tick mark labels are aligned with the axis.
11. Under "Axis Options", set the "Tick mark label position" to "Next to axis". This will ensure that the tick mark labels are visible next to the axis.
12. Finally, hide the helper column by selecting it and setting the font color to white.

Now your chart should have monthly separation tick marks on the x-axis that are aligned with the first day of each month.

Sure there is. What I'd do is create a series like this:

X Y
A1: 1/1/2007 0 <~~~or whatever your minimum value is for the chart
A2: =date(year(a1),month(A1)+1,day(a1)) 0

COPY DOWN

Create another series for your chart using these values.
Put Y error bars on the series at the size you need.

Use something like Rob Bovey's XYChart Labeler (www.appspro.com) to put
labels on this series. You labels could be the X part of your series.

Change the series marker to none.

It's kludgy, but it works.


"Rab" wrote:

I'm plotting a number of data series on an x-y scatter chart. Values are
recorded daily over an 18 month period and are plotted against time on the
x-axis. To make the graph look neat I want to place tick marks at monthly
intervals at the first of each month. Because the number of days in the
month varies it is not possible to select an appropriate major unit in the
format axis box. As a result, if I select for example 31 as the major unit
then the monthly tick marks progressively move to a later date in the month -
starting from 01 August 05 and by March 07 my tick mark is placed at the
13th. Is there anyway to overcome this? Thanks.

This is where a Line chart is nicer than an XY chart. Create a line chart
with your data, then double click the X axis, click on the Scale tab, choose
Days as your Base Unit, 1 Month as your Major Unit, and the first of any
relevant month as the Minimum.

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


"Rab" wrote in message
...
I'm plotting a number of data series on an x-y scatter chart. Values are
recorded daily over an 18 month period and are plotted against time on
the
x-axis. To make the graph look neat I want to place tick marks at monthly
intervals at the first of each month. Because the number of days in the
month varies it is not possible to select an appropriate major unit in the
format axis box. As a result, if I select for example 31 as the major
unit
then the monthly tick marks progressively move to a later date in the
month -
starting from 01 August 05 and by March 07 my tick mark is placed at the
13th. Is there anyway to overcome this? Thanks.

Rab:

I use custom axis to get my time series charts to look the way I want. This
"how-to" shows you the overall approach to custom axes.

http://processtrends.com/pg_charts_custom_axis.htm

My step chart example shows how to center the label name in the X axis tick
mark gap. http://processtrends.com/pg_charts_d...xis_series.htm

Once you master custom axes, you'll find a lot of it easy to spruce up your
XY charts.

Kelly

http://processtrends.com


"Barb Reinhardt" wrote in message
...
Sure there is. What I'd do is create a series like this:

X Y
A1: 1/1/2007 0 <~~~or whatever your minimum value is for the chart
A2: =date(year(a1),month(A1)+1,day(a1)) 0

COPY DOWN

Create another series for your chart using these values.
Put Y error bars on the series at the size you need.

Use something like Rob Bovey's XYChart Labeler (www.appspro.com) to put
labels on this series. You labels could be the X part of your series.

Change the series marker to none.

It's kludgy, but it works.


"Rab" wrote:

I'm plotting a number of data series on an x-y scatter chart. Values are
recorded daily over an 18 month period and are plotted against time on
the
x-axis. To make the graph look neat I want to place tick marks at
monthly
intervals at the first of each month. Because the number of days in the
month varies it is not possible to select an appropriate major unit in
the
format axis box. As a result, if I select for example 31 as the major
unit
then the monthly tick marks progressively move to a later date in the
month -
starting from 01 August 05 and by March 07 my tick mark is placed at the
13th. Is there anyway to overcome this? Thanks.

Thanks for all the answers. I'll have a play around and see where I get to.

Barb, Kelly

Thanks. This method at first appears a bit cumbersome but is actually very
straight forward. Problem solved.

But...after however many versions of excel, why does it still fail to be
able to perform so many desirable functions in a simple way via the chart
wizzard? If I want to generate charts with different chart types for
different series, multiple y-axes etc it seems that there are a lot of
counter-intuitive things going on. Please sort it out microsoft!

Thanks again all for the useful advice.

Rab

"Kelly O'Day" wrote:

Rab:

I use custom axis to get my time series charts to look the way I want. This
"how-to" shows you the overall approach to custom axes.

http://processtrends.com/pg_charts_custom_axis.htm

My step chart example shows how to center the label name in the X axis tick
mark gap. http://processtrends.com/pg_charts_d...xis_series.htm

Once you master custom axes, you'll find a lot of it easy to spruce up your
XY charts.

Kelly

http://processtrends.com


"Barb Reinhardt" wrote in message
...
Sure there is. What I'd do is create a series like this:

X Y
A1: 1/1/2007 0 <~~~or whatever your minimum value is for the chart
A2: =date(year(a1),month(A1)+1,day(a1)) 0

COPY DOWN

Create another series for your chart using these values.
Put Y error bars on the series at the size you need.

Use something like Rob Bovey's XYChart Labeler (www.appspro.com) to put
labels on this series. You labels could be the X part of your series.

Change the series marker to none.

It's kludgy, but it works.


"Rab" wrote:

I'm plotting a number of data series on an x-y scatter chart. Values are
recorded daily over an 18 month period and are plotted against time on
the
x-axis. To make the graph look neat I want to place tick marks at
monthly
intervals at the first of each month. Because the number of days in the
month varies it is not possible to select an appropriate major unit in
the
format axis box. As a result, if I select for example 31 as the major
unit
then the monthly tick marks progressively move to a later date in the
month -
starting from 01 August 05 and by March 07 my tick mark is placed at the
13th. Is there anyway to overcome this? Thanks.

On Thu, 10 May 2007, in microsoft.public.excel.charting,
Rab said:
But...after however many versions of excel, why does it still fail to be
able to perform so many desirable functions in a simple way via the chart
wizzard? If I want to generate charts with different chart types for
different series, multiple y-axes etc it seems that there are a lot of
counter-intuitive things going on. Please sort it out microsoft!

I sympathise, but remember that Excel is not a graphing program, it's a
spreadsheet. Once upon a time, there were graphing programs that were
sold separately, with names like Harvard Graph and Lotus Freelance, but
the demand doesn't seem to have been there for these programs to be
developed. The business market seems to be satisfied with a stunted
little graphing facility embedded in a spreadsheet.

(and yet the same big business customers demand that that little
graphing facility must have fancy metallic-look visual effects. go
figure.)

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

On May 10, 4:52 am, Rab wrote:
Barb, Kelly

Thanks. This method at first appears a bit cumbersome but is actually very
straight forward. Problem solved.

But...after however many versions of excel, why does it still fail to be
able to perform so many desirable functions in a simple way via the chart
wizzard? If I want to generate charts with different chart types for
different series, multiple y-axes etc it seems that there are a lot of
counter-intuitive things going on. Please sort it out microsoft!

Thanks again all for the useful advice.

Rab



"Kelly O'Day" wrote:
Rab:

I use custom axis to get my time series charts to look the way I want. This
"how-to" shows you the overall approach to custom axes.

http://processtrends.com/pg_charts_custom_axis.htm

My step chart example shows how to center the label name in the X axis tick
mark gap. http://processtrends.com/pg_charts_d...xis_series.htm

Once you master custom axes, you'll find a lot of it easy to spruce up your
XY charts.

Kelly

http://processtrends.com

"Barb Reinhardt" wrote in message
...
Sure there is. What I'd do is create a series like this:

X Y
A1: 1/1/2007 0 <~~~or whatever your minimum value is for the chart
A2: =date(year(a1),month(A1)+1,day(a1)) 0

COPY DOWN

Create another series for your chart using these values.
Put Y error bars on the series at the size you need.

Use something like Rob Bovey's XYChart Labeler (www.appspro.com) to put
labels on this series. You labels could be the X part of your series.

Change the series marker to none.

It's kludgy, but it works.

"Rab" wrote:

I'm plotting a number of data series on an x-y scatter chart. Values are
recorded daily over an 18 month period and are plotted against time on
the
x-axis. To make the graph look neat I want to place tick marks at
monthly
intervals at the first of each month. Because the number of days in the
month varies it is not possible to select an appropriate major unit in
the
format axis box. As a result, if I select for example 31 as the major
unit
then the monthly tick marks progressively move to a later date in the
month -
starting from 01 August 05 and by March 07 my tick mark is placed at the
13th. Is there anyway to overcome this? Thanks.- Hide quoted text -

- Show quoted text -

Try Multiple-Y Axes for Excel: www.OfficeExpander.com
There is a free demo program.
-Dave

Although it's a stunted little graphing facility, it has great flexibility,
both in terms of combination charts which use additional series to achieve
certain functionality as well as programmability to do it repeatedly and
effortlessly. Excel allows you to hack together features that you would
otherwise need to purchase an expensive specialized software package for,
and that package wouldn't do all the other things that a spreadsheet can do.

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


"Del Cotter" wrote in message
...
On Thu, 10 May 2007, in microsoft.public.excel.charting,
Rab said:
But...after however many versions of excel, why does it still fail to be
able to perform so many desirable functions in a simple way via the chart
wizzard? If I want to generate charts with different chart types for
different series, multiple y-axes etc it seems that there are a lot of
counter-intuitive things going on. Please sort it out microsoft!

I sympathise, but remember that Excel is not a graphing program, it's a
spreadsheet. Once upon a time, there were graphing programs that were sold
separately, with names like Harvard Graph and Lotus Freelance, but the
demand doesn't seem to have been there for these programs to be developed.
The business market seems to be satisfied with a stunted little graphing
facility embedded in a spreadsheet.

(and yet the same big business customers demand that that little graphing
facility must have fancy metallic-look visual effects. go figure.)

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

"Jon Peltier" wrote:

This is where a Line chart is nicer than an XY chart. Create a line chart
with your data, then double click the X axis, click on the Scale tab, choose
Days as your Base Unit, 1 Month as your Major Unit, and the first of any
relevant month as the Minimum.

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


"Rab" wrote in message
...
I'm plotting a number of data series on an x-y scatter chart. Values are
recorded daily over an 18 month period and are plotted against time on
the
x-axis. To make the graph look neat I want to place tick marks at monthly
intervals at the first of each month. Because the number of days in the
month varies it is not possible to select an appropriate major unit in the
format axis box. As a result, if I select for example 31 as the major
unit
then the monthly tick marks progressively move to a later date in the
month -
starting from 01 August 05 and by March 07 my tick mark is placed at the
13th. Is there anyway to overcome this? Thanks.

prince

You can use Jon's suggestion of a time series line chart or you can use an
XY trend chart with a custom X axis that will let you label your X axis
dates exactly the way you want.

Here's a link to a tutorial on making custom XY axis.

http://processtrends.com/pg_charts_custom_axis.htm

I have several examples of XY trend charts with custom axis , look he

http://processtrends.com/TOC_trend_charts.htm

Kelly

http://processtrends.com

"prince_mavs" wrote in message
...


"Jon Peltier" wrote:

This is where a Line chart is nicer than an XY chart. Create a line chart
with your data, then double click the X axis, click on the Scale tab,
choose
Days as your Base Unit, 1 Month as your Major Unit, and the first of any
relevant month as the Minimum.

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


"Rab" wrote in message
...
I'm plotting a number of data series on an x-y scatter chart. Values
are
recorded daily over an 18 month period and are plotted against time on
the
x-axis. To make the graph look neat I want to place tick marks at
monthly
intervals at the first of each month. Because the number of days in
the
month varies it is not possible to select an appropriate major unit in
the
format axis box. As a result, if I select for example 31 as the major
unit
then the monthly tick marks progressively move to a later date in the
month -
starting from 01 August 05 and by March 07 my tick mark is placed at
the
13th. Is there anyway to overcome this? Thanks.