Help with adding x-axis major gridlines

[Judi]

I'm trying to make a chart that will have small tick-marks along the x-axis
for a fiscal week, and a long tick-mark separating the fiscal month. I think
I'm not formatting the data properly in the worksheet. Any ideas?

 

[Jon Peltier]

Use a Line chart with dates as the X values. Double click the X axis. On the
Scale tab, set the X axis minimum to occur on the 1st of a month. Set the
Base Unit to Day(s), the Major Unit to 1 Month(s), the Minor Unit to 7
Day(s). On the Patterns tab, choose whatever for Major Ticks and something
besides None for Minor Ticks. Click OK. On the Chart menu, choose Chart
Options, and on the Gridlines tab, check the box for Category (X) Axis Major
Gridlines.

- Jon

 

[Jon Peltier]

Judi -

This has been very helpful, Jon. Thank you so much. However, each time I
change the setting, it defaults back to its original setting. Is it how
I'm
formatting the data source?

Is it a pivot chart? Pivot charts revert to default formatting at the drop
of a hat. Otherwise, I'm not sure I understand your description.

I have the data in there with dates (which I don't want to show up), and I
have a fiscal month next to that (which I DO want to show up - and just
have
the date information as tick marks on the x-axis). Does that make sense?

If the major unit is 1 month, you can have a label each month, the first if
the chart begins on the first of a month. You can format it to show just the
name of the month (number format "mmm" for Jan or "mmmm" for January). You
can't just move it to the middle of the month, though.

- Jon

 

[Jon Peltier]

What you can do, which is a bit of a kludge, is arrange your data like this
(view with non-proportional font):

Value
Jan 1 4
8 5
15 6
22 7
29 8
Feb 5 9
12 10
19 11
26 12
Mar 5 13
12 14
19 15
26 16

The first column is the month if it's the first week of the month, otherwise
blank*. The second column is the day of the month. The third column is the
data. The first two cells in the top row are blank*, the cell above the
values has a label. Make a line or column chart. *These must be true blank
cells, not a formula that returns "" to look like a blank.

The X axis will look like this, with the month names centered beneath the
dates within that month:

1 8 15 22 29 5 12 19 26 5 12 19 26
Jan Feb Mar

- Jon