I am trying to make a graph showing rainfall amounts for a certain city over the last 49 years. I have the yearly rainfall totals, but the problem seems to be the high number of years I want to graph.
All the tutorials show graphs using much fewer years. I know the high number of years shouldn’t be a problem, but I just don’t know how to go about it.
You are having trouble graphing just 49 data points? I can’t imagine a line graph would have a problem with this. Can you give more specifics?
DMC,
Thanks for your reply.
I want to have the right side vertical column show inches (of rain).
The horizontal “column” will show the (49) years.
I have all the data in Excell 2003, but just don’t know how to put it in graph form.
Right now the graph shows the different yearly amounts, but neither the inches nor the years are displayed.
What kind of graph, column? Line?
You could make a histogram if that works? 49 years is a bit to visualize in a meaningful manner for a bar/column graph, line/scatter might do it though. That means you’d lump it in, so instead of 1950,1951,1952…2012, you’d have 1950-1954,1955-1959,1960-1964… etc.?
I don’t have a copy to verify the steps, but you’ll want to go into options, then add-ins, then check the “Data Analysis TookPak.” I believe that’s in 2003. Install that, and it should show up somewhere, maybe under data. You need your data and a column for the “bin size,” e.g. 1950,1955,1960,1965… in each cell. Run histogram, and have it output the new data somewhere blank, then graph that.
If your only problem is the display, it should be under the graph options for axes.
There’s no “options”, but there is “options” under “tools”.
I got to “Analysis TookPak” and clicked OK, but nothing showed up anywhere.
No histogram, no bin size.
Checked the box for it. BTW, you don’t want the VBA version, if applicable. Once installed, Google tells me it’s under Tools > Data Analysis
Sounds like it might just be the axis labels. I don’t have a copy of XL03 handy, but should be able to right click the main chart area and pick “Select Data”.
I always feel like I don’t properly understand the Excel question I’m trying to answer, but I like to try.
One possible problem may be excel regarding your years as data points.
If you have your years as say 1960 - 2000.
Now there aren’t many places that have over 2000 inches of rain so that mucks up the scale to plot your rainfall data
Assuming your data format is simply two columns eg
Year Rain
1960 48.2
…
Best option, clear the existing graph.
Then ensure the Years column does not have a title.
As a worst case scenario, make your Years data into text
Rain
'1960 48.2
I think I figured out why you’re having issues. I put the years 1960 through 2008 in column A going down. Then put random numbers between 25 and 75 in column B (I used =RANDBETWEEN(25,75)). When I highlighted the area and inserted a line graph, Excel thinks that column A is a metric, not an axis label, so you get two lines. If this is your problem, here is the fix (Excel 2007 here):
This assumes you have done as above and have two lines in your graph.
Right-click on your chart and choose “Select Data”.
Highlight “Series 1” under “Legend Entries (Series)” and click “Remove”.
Click “Edit” under “Horizontal (Category)” and it should ask for the Axis Label Range. Select all of the years and press “OK”.
Press “OK” again.
Voila! (I hope)
ETA: If Excel 2003 is drastically different, post a link to a screenshot with the dialog box that pops up when you try and edit the graph’s data.
I need to add that penultima thule’s method should accomplish the same thing, as it should prevent Excel from getting confused in the first place. Note that you actually have to make the individual years text (put a single quote in front of each year) as he/she did, which is not the same as “formatting as text”.
I’m close!
I’ve got the chart just like I want it except along the bottom there are just numbers (1 through 50). I want the bottom to show the years 1964 through 2012. (The vertical column on the left is showing the rainfall totals just like I want it to.)
How do I get rid of the numbers along the bottom and insert the years I want?
Try the steps in post 9.