I have a chart showing water levels in a canal at 15 minute intervals (this
was pulled from an automatic data logger on the canal). I have three columns
of data:
Column 1: Date
Column 2: Time
Column 3: Water level
My series1 data comes from the Column 3. My Column 1 data contains the same
date for 96 consecutive rows (i.e. a water level reading every 15 minutes
over a 24 hour interval equals 96 data entries). I want to label the x axis
with the date label every 96th data point. I have been able to set the
X-axis gridlines to show every 96th data point.
-----How can I show an X-axis data label every 96th data point?
Thanks,
Jonathan Mulder
Durham, CA

Jon -

First, it's more efficient to use a single column for date and time.
Excel treats dates as whole numbers and times as fractions. Of course,
if your data comes in this way, you probably don't want to change it.

What kind of chart are you using? Is it a line chart? There are two
choices.

One option is to use the date column only as your category labels.
Double click the X axis, and on the Scale tab, change Number of
Categories between Tick Marks and Number of Categories between Tick Mark
Labels to 96. Probably also uncheck Value Axis Crosses Between
Categories. When you do this, then add gridlines, the major gridlines
automatically line up with every 96th point, at the X axis major tickmarks.

The other option will not look good here because you have 96 minor
divisions per major division. But it looks good in other cases, so I'll
burn a little bandwidth. Set up your date and time in two columns, as
you have it, but leave 95 of the date values blank for each one that
appears; the date should appear on the first time entry per day. You
need to use both date and time columns for your category labels. You can
select a two column range in the Source Data/Series dialog, but the
easiest way is as follows: Make sure there's a label for the value
column in the row above the data, but no label in the cells above the
date and time columns. These blanks tell Excel where to split the
categories from the values. Make the chart as a line chart, and Excel
will use the time entries as the layer of categories closest to the
axis, and the date entries as the layer further away. Unfortunately you
can't adjust the spacing between visible category labels: Excel wants to
show all 96 per date. This makes your particular chart cluttered, but in
many cases, it would be just fine.

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


JonMulder wrote:

I have a chart showing water levels in a canal at 15 minute intervals (this
was pulled from an automatic data logger on the canal). I have three columns
of data:
Column 1: Date
Column 2: Time
Column 3: Water level
My series1 data comes from the Column 3. My Column 1 data contains the same
date for 96 consecutive rows (i.e. a water level reading every 15 minutes
over a 24 hour interval equals 96 data entries). I want to label the x axis
with the date label every 96th data point. I have been able to set the
X-axis gridlines to show every 96th data point.
-----How can I show an X-axis data label every 96th data point?
Thanks,
Jonathan Mulder
Durham, CA