Simple? Help Needed with Excel Chart

  • Thread starter Thread starter DKD
  • Start date Start date
D

DKD

Hi,

I have an Excel document that lists events that happened
according to its year. Each event is defined by a number
from 1 to 10, which are listed on the Y axis. I am trying
to create a chart that shows the events by year (in a
line graph) so that the data points line up according to
the year. I have set the years on the X axis.

First, what I need is to show is that the years show up
to scale (not equidistant). I think I've managed to
figure this out by setting the date to be the same for
each year (For example, 1/1/1985, 1/1/1989, etc). Is
there a way to have the chart show up to scale according
to year only, and not the exact date?

Second, I would like the line graph to show events that
happened for multiple years with one line. For example,
if an event with a score of 5 happened from 1990 to 1995,
the chart should show one line where y=5 that goes from
x=1990 to x=1995.

This is really all the help I need; I am stuck as to
exactly how to figure this out. Thank you so much for any
help, advice, and pointers that I haven't thought of. If
there is another way to do what I am talking about more
easily, I would like to know about it as well!
 
Category labels on an x-axis will, I believe, always be
equidistant unless you create empty "dummy" colums (or
dummy rows--whichever correspond to "categories") and then
include them in your source data. If you're concerned only
that plotted data reflect the uneven intervals, that's
easier. Enter your years (category, x-axis) across row 1,
against your values 1-10 (value, y-axis) down column A.
Inside the resulting grid enter each event where it's year
and it's value intersect. For example if an event of type
5 occurred in 1998, enter "5" under 1998 and beside value
5 (the number you enter will always equal it's Y-axis or,
in this case, row heading). On the spreadsheet, make sure
your years (category names) are actual Numbers/Dates (I
think you've done this), and that, on the chart, your x-
axis is a Time-Scale (Chart Type>Axis). If an event of a
certain number happens through contiguous years, a line
will be drawn across those years on the chart (if you
don't see the line, right click one of the relevant data
points in the chart, Format>Data Series, and make sure the
series is formatted for a line). Finally, to get the
points floating nicely within the line chart, you may want
to add some time at the beginning of your data that
preceeds your first event, and play with with the scaling
and other check boxes in "Format>Axis" to achieve the
positioning you want.
 
Chas suggests a way to spread out your data in a line chart. Using an XY
Scatter chart might do this as well, with more ability to adjust the
scale. You can connect the points with lines; don't let the names of the
chart types fool you.

- Jon
 
Back
Top