tick mark labels

[Debrane]

How can I increase the quanity of tick mark labels along the x-axis in a
graph? I.E. Excel only puts my Date/Time tick mark labels at the beginning
of each day. I'd like to show tick mark labels every 6 hours.

 

[Barb Reinhardt]

You can change the scale of the X axis to be every 6 hours (.25 day) IF you
are using an XY Scatter chart. If it's a LINE chart, you can't do it.

 

[Debrane]

Thanks. I tried that ... and you did answer my question, but now I have ALL
major tick marks. Is there any way that I can have daily major tick marks
with labeled minor tick marks. ( I know that I can "fake" it my placinfg
heavy vertical lines in, but I'm trying to make a template for future use by
other users in the office.) Excel (and other community discussion) sez under
"Selected Axis", go to "Scale" tab, then in the "Number of categories between
tick mark labels" box, type 1 to label every category, type 2 to label every
other category, etc. In my "Scale" tab, I don't get the "Number of
categories between tick mark labels" box. I have Excel version 2003,
Professional.

 

[Jon Peltier]

Excel stores date-time data as the sum of a whole number (the date, in
days since 1 Jan 1900, give or take a leap day) plus a fraction (the
time, as elapsed since midnight, so 6 am = 0.25).

If you make an XY Scatter chart using this date-time as your X values,
you can do whatever you want, with major ticks at midnight and minor
ticks every 6 hours. You can enter 0.25 for your minor spacing (1/4 day)
or 6:00, and Excel will convert the time to the decimal equivalent. You
can also enter dates for the min or max, and Excel will convert them.

A Line chart has two ways to do this. If Excel recognizes the dates, it
creates a numerical date scale axis (misnamed a "time scale" axis by
Microsoft). It uses only the whole number piece of the data and ignores
the fraction. Your X axis spacing then can be no less than 1 full day,
major or minor spacing, and all points are plotted at midnight at the
start of the day.

A Line chart can also have a category axis which uses each X value entry
as a non-numerical label. Points are equally spaced regardless of the
time difference between points. You can specify a number of categories
between major and minor labels, and if your data consists of four evenly
spaced measurements per 24-hour period, you can approximate a daily
major tick with four minor divisions.

In a Line chart you can change from one category axis type (Category or
Time Scale) by selecting Chart Options from the Chart menu, and clicking
on the Axes tab. You might be better off with an XY Scatter chart. You
can format the points as in a line chart, so don't let an unfortunate
choice of chart type names mislead your choice of chart types.

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

 

[Debrane]

This chart is an XY scatter graph. The x-axis is formatted with date/time
format, so I successfully made 0.25 minor tick marks. To label every tick
mark, I had to change my major tick marks to 0.25 also. However, I want
major tick marks for every day and minor tick marks for every 6 hours. But I
also want minor tick marks labelled. You said that I can change the x-axis
format axis to a category-type axis so that I can get the "Number of
categories between
tick mark labels" box in the "Format Axis" Scale tab? If that's what you
mean, then how do I change the x-axis format?

 

[Jon Peltier]

A tick with a label is a major tick mark.

In an XY Scatter chart, you can have major (labeled) tick marks every 24
hours with minor (unlabeled) tick marks every six hours; or you can have
major (labeled) tick marks every 6 hours.

In a Line chart with a Category-type category axis, you can get "Number
of Categories between Tick Mark Labels". This will help you define the
number of minor ticks between each pair of major tick marks, but it will
not allow you to label minor tick marks.

If you really want to switch to a Line chart, select the entire chart,
and using Chart Type from the Chart menu, select one of the Line Chart
styles. Then using Chart Options from the Chart menu, Axes tab, choose
the Categories option.

Why not try the above on a copy of your chart, so when you're done you
can delete one, without having to backtrack lots of steps if you like
the previous appearance better.

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

 

[Debrane]

So it sounds like I can't "have my cake and eat it too". I want date AND
time (therefore must have XY scatter chart); my data are spaced avery 10
minutes (must have XY scatter chart), but I want to have labels every 6 hours
with major grid lines every 24 hours. So, if I want it done my way, I'll
have to just draw heavy vertical lines or .... generate some extra data that
looks like vertical lines. Unless you have someother ideas? By the way,
thanks for the overview on chart types.

 

[Jon Peltier]

You could use the major ticks every 24 hours with a date-only label, and
format the vertical grid line in a darker grey, then put minor ticks
every 6 hours with a lighter gray gridline. Then use a dummy series to
create a dummy axis, with time-only labels every 6 am, noon, and 6 pm
across the axis, using this technique:

http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

Since you already have the minor tick marks, you can hide all the
markers for the dummy series, and you can use a smaller font size for
the times, which is not possible for labels along a regular axis.

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

 

[Debrane]

THank you!!! THank you. THank you!!!!!

You could use the major ticks every 24 hours with a date-only label, and
format the vertical grid line in a darker grey, then put minor ticks
every 6 hours with a lighter gray gridline. Then use a dummy series to
create a dummy axis, with time-only labels every 6 am, noon, and 6 pm
across the axis, using this technique:

http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

Since you already have the minor tick marks, you can hide all the
markers for the dummy series, and you can use a smaller font size for
the times, which is not possible for labels along a regular axis.

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

 

[Jon Peltier]

Sorry it took so long....

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