MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Creating a Time Plot Using the Chart Wizard


Posted by Dave on January 16, 2002 3:16 AM

I need to create a time plot displaying the varying elevation (feet above sea level) of a lake over a period of twelve years.

Originally, I made a chart on an Excel spreadsheet listing the years (1986-1997)chronologically in the left column and the feet above sea level in the right column. When I used the Chart Wizard, I chose a line graph. However, the feet above sea level showed up on the Y-axis with a range of 0 - 4500 feet. I needed specific numbers on the y-axis (i.e. 3807, 3812, etc.).

Likewise, the years showed up on the x-axis as numbers (i.e., 1,2,3) instead of in year format (i.e., 1986, 1987). Furthermore, the lines (yes, there were 2 instead of 1) on the graph was straight across the graph instead of varying.

What am I doing wrong, and how can I correct it?


Posted by Mark W. on January 16, 2002 8:37 AM

Suppose that your data in A1:B13 is...

{"Year","Elevation"
;1986,3836
;1987,3861
;1988,3876
;1989,3876
;1990,3842
;1991,3837
;1992,3841
;1993,3889
;1994,3863
;1995,3876
;1996,3840
;1997,3858}

Create a blank Line chart by selecting an empty,
unused cell, clicking the Chart Wizard toolbar
button, and pressing [ Finish ]. With the
blank chart object selected paste the formula,
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$13,Sheet1!$B$2:$B$13,1),
into the formula bar and press [ Enter ]. Right
click the Y-axis, choose Format Axis... from the
popup menu, on the Scale tab set the "Minimum" value
to 3807 and the "Major unit" to 5, and press [ OK ].