Part 4 — Create a Streamflow- Precipitation Graph
In any step, click the Show me link to reveal extra information. If you prefer a printout of the full set of instructions for this part, choose Print from the File menu.
Step 1- Select Data and Open the Chart Wizard
In the file flowsud_2000.xls, highlight the date, precipitation, and streamflow data (columns C, D, and E) and open the Chart Wizard.
Show Details
Hide- Open the file flowsud_2000.xls and highlight the 366 rows of date, precipitation, and streamflow data (columns C,D, and E).
- Open the Chart Wizard by clicking on its icon in the Excel toolbar (or under the Insert menu, select Chart....)
Step 2- Select the Chart Type
Select the graph type, Line-Column on 2 Axes, which is located in the Custom Types area of the Wizard. Click Next.
- In Step 1 of the Chart Wizard, choose the Custom Types of charts. Scroll down and select the chart type, Line-Column on 2 Axes. Click Next.
NOTE: If you do not have the Line-Column on 2 Axes chart option in the Custom Types area of your Chart Wizard, open the link below for directions on how to get it.
- Download the file lc_TwoAxes.xls (Excel 20kB Apr7 08) and open it.
- Click on the graph.
- In the Excel menu bar, click Chart->Chart Type.
- On the Chart Wizard that opens, select the Custom Types tab.
- Select the User-Defined radio button
- Click Add
- Name the chart type Line-Column on 2 Axes and click OK.
If you have added the Line-Column on 2 Axes chart type in this manner, you will always need to go to Custom Types in the Chart Wizard, select the User-Defined radio button, and highlight the icon when you want to use it.
Finally, close the file lc_TwoAxes.xls and return to Step 2, being sure to also return to the file flowsud_2000b.xls
Step 3- Label the Series
Label Series 2 as Precipitation and Series 1 as Streamflow. Click Next.
- In Step 2 of the Chart WIzard, choose the Series page. This is where you can label the Key for the graph. Type Precipitation for the Series 2 label; then highlight Series 1 and type Streamflow. Click Next.
Step 4- Add a Title and Labels for the Axes
Enter the title, Precipitation and Streamflow: Sudbury River, 2000. Enter the label, Inches for the Value (Y) axis and Cubic Feet per Second for the Second Value (Y) axis. Click Next.
In Step 3 of the Chart Wizard, enter the chart title, Precipitation and Streamflow: Sudbury River, 2000. Then add Inches for the Value (Y) axis and Cubic Feet/Second for the Second Value (Y) axis. Click Next.
Step 5- Specify the Chart Location
Leave the default setting: As object in, and click Finish.
- In Step 4 of the Chart Wizard, leave the default setting: As object in. Click Finish.
If you elect to place the chart in a new Excel sheet, its size will be fixed to fit into one sheet. Since there is a full year of data in this graph, it really needs to be stretched both vertically and horizontally in order for it to be readable. Keeping it as an object in the spreadsheet means that it can be stretched.
Step 6- Resize the Graph for Easier Reading
Stretch the finished chart both horizontally and vertically until every day of data can be clearly distinguished. The elongated chart may show just 4 months of data within the width of the screen.
- Re-size the resulting graph until it is easily readable. Drag any of the six handles you see around the perimeter of the background to re-size both the background and the graph area. You can also select just the graph area and re-size it in the same way, so that the graph fills more of the background area.
- Drag the Key, which shows the symbols for precipitation and streamflow, onto the upper left corner of the graph.
The image below has been expanded so that the height is approximately equal to the height of 30 cells on the spreadsheet, and the length is approximately equal to the length of 32 cells on the spreadsheet. The key and the title have also been repositioned.
