Help with adding x-axis major gridlines

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?
0
Judi (12)
12/27/2006 11:46:01 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
737 Views

Similar Articles

[PageSpeed] 39

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, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Judi" <Judi@discussions.microsoft.com> wrote in message 
news:7A989D7B-728E-45B7-A405-B4835301DE38@microsoft.com...
> 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? 


0
jonxlmvpNO (4558)
12/28/2006 12:40:19 AM
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, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


> "Jon Peltier" wrote:
>
>> 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, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://PeltierTech.com
>> _______
>>
>>
>> "Judi" <Judi@discussions.microsoft.com> wrote in message
>> news:7A989D7B-728E-45B7-A405-B4835301DE38@microsoft.com...
>> > 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?
>>
>>
>> 


0
jonxlmvpNO (4558)
12/28/2006 9:56:53 PM
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Judi" <Judi@discussions.microsoft.com> wrote in message 
news:E3BCCC43-1635-4014-8702-2DA92E841AB2@microsoft.com...
>I don't think it's a pivot chart. Let me try to explain again.  I'm looking
> at some data that's collected each week.  I have 52 data points that my 
> boss
> wants to see - they're hours worked per week.  Now, he wants to see bars 
> for
> that (or whatever we use), but the labels at the bottom - he just wants to
> see the FISCAL month.  For last fiscal year, October was 5 weeks, November 
> 4,
> December 4, January 5, February 4, etc., with my first data point being
> Friday, September 30, 2005.
>
> So, the x-axis is the week (but he'd like to see labels of months, 
> instead -
> but FISCAL months, not normal months), and the y-axis is the number of 
> hours.
>
>
> Does that make it any more clear?
>
>
>
> "Jon Peltier" wrote:
>
>> 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, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://PeltierTech.com
>> _______
>>
>>
>> > "Jon Peltier" wrote:
>> >
>> >> 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, Microsoft Excel MVP
>> >> Tutorials and Custom Solutions
>> >> http://PeltierTech.com
>> >> _______
>> >>
>> >>
>> >> "Judi" <Judi@discussions.microsoft.com> wrote in message
>> >> news:7A989D7B-728E-45B7-A405-B4835301DE38@microsoft.com...
>> >> > 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?
>> >>
>> >>
>> >>
>>
>>
>> 


0
jonxlmvpNO (4558)
1/2/2007 7:28:16 PM
Reply:

Similar Artilces: