Chart displaying weekly data group in months without weekly labels

[smcgee01]

I am putting together charts that displays weekly data for last year and this
year but I want the x-axis only to display months while the weekly data
points appear in the plot area for that month and I am having difficulty
doing it... does anyone have any suggestions?

 

[Jon Peltier]

First column: dates for the week (Sunday or Monday dates, or whatever you
lie to use).
Second column: values being tracked.
Create a LINE chart. Excel should apply a date-scale axis.
Format the date scale axis so it uses days as its base unit, but 1 month as
its major unit. You probably also want to set the axis minimum to the first
of the first month, so the first of each month gets a tick mark and label.

- Jon

 

[smcgee01]

Thanks Jon. A couple more questions...

The tick mark labels are in m/dd/yy format. Is there a way to change it to
just month?

Additionally, is there a way to shade the plot area to identify the
different months of the year?

 

[Jon Peltier]

Thanks Jon. A couple more questions...

The tick mark labels are in m/dd/yy format. Is there a way to change it
to
just month?
http://peltiertech.com/Excel/NumberFormats.html

Additionally, is there a way to shade the plot area to identify the
different months of the year?

http://peltiertech.com/WordPress/2008/06/01/highlight-certain-time-periods-in-a-chart/

- Jon

 

[smcgee01]

John,

Thanks for the response. I had seen your post about the shading and had
already tried that. It is giving me 4 (or 5 depending on the month)
individual lines, not one full block delineating that one month. Is there a
way to have it appear as one full block?

 

[Jon Peltier]

Double click on one of the columns to bring up the Format Series dialog. On
the Options tab, set Gap Width to zero.

- Jon

 

[smcgee01]

Again, thanks for the suggestion. I had already had the gap width set at 0,
I did try to change the scale to months and the bands did identify the months
but the data points were then stack on top of each other (I need to have the
lines appear to slope). Any suggestions?

 

[Jon Peltier]

Oh yeah. You need to include every day in your data. The date scale axis
leaves a space for each day, so even with gap width = zero, there are six
slots in between weekly columns. Plot the columns on the secondary axis, and
set the X scale the same as in the primary axis, then hide the secondary
axis.

Set the *base units* of the axis scale to days, and the *major units* to 1
month.

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