Category Axis Labels

[E Harris]

Thanks in advance for any help.

I'm using Excel 2002

At step 2 of 4 of the chart wizard, you can go to the "Category (X)
axis labels" box and select a range of cells to be used as the labels
for the ticks on the x-axis of your chart. But I've noticed that, if
there are duplicate values in this range, the wizard will only select
one row of data for every unique value.

My data consists of measurements taken over the course of several
weeks. I want to plot the changes in measurements over time. The
x-axis is time. Unfortunately, those who made the measurements didn't
record the time of day. So, for example, in my data I may have
something like:

Measurement Date
39896.2 3/23/2001
36221.4 3/27/2001
37563.0 3/28/2001
35236.5 3/29/2001
35863.2 4/2/2001
34353.3 4/3/2001
34211.0 4/3/2001

The wizard automatically decides that I have two series, and the
x-axis is just auto-numbered. Although this default does show all the
data, I do not want that. I then delete the date series, and use the
"Category (X) axis labels" box to specify that I want the x-axis
labels to be the dates. This would still show all the data, but, as I
said, the wizard drops any rows with duplicate labels.

In my above example, the last two plots on the chart would go from a
value of 35863.2 on 4/2/2001 to 34211.0 on 4/3/2001. The value
34353.3, also on 4/3/2001, is not shown.

Is there anyway to avoid the chart wizard's doing this? I understand
that it is just trying to maintain integrity in the chart, but for my
purpose, having two identical ticks on the x-axis is acceptable.

Incidentally, I'm acutally creating the chart on-the-fly with VBA
code. I've just been using the macro recorder to record my wizard
choices, so I can then see the code to do what I want. So I'm hoping
that, although the interface for the chart wizard seems to have
nothing to do what I want, that someone will tell me that it can be
done in code alone.

Thanks for any help.

--e harris

 

[Stephen Bullen]

Hi,

Is there anyway to avoid the chart wizard's doing this? I understand
that it is just trying to maintain integrity in the chart, but for my
purpose, having two identical ticks on the x-axis is acceptable.

I'm guessing that Excel is automatically applying a time-series for the
x axis, and plotting your data on that series. To change it back to a
set of categories, select the chart, click on Chart > Options > Axes
and change the X Axis from 'Automatic' to 'Category'.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[Raj]

A simple solution is to choose XY scatter as the chart
type. It would also help if you make the dates as the
first column and the measurement as the second column.
The XY scatter chart plots X (Column 1) versus Y (Column
2). This way there is only one series, and the X axis
will be time and the Y axis will be the measured value.
There will be two points for 4/3/2001. This should work.

 

[E Harris]

Raj and Stephen--

Thanks for the responses.

I won't be able to try either solution for a while (another project
taking priority now), but I wanted to express my gratitude.

--emil