Part 4—Create a Streamflow-Precipitation Graph

Step 1 –
Select Data and Open the Chart Wizard

Note: If you need a clean copy of the Excel file, you can download the formatted flowsud_2000b.xls ( 63kB May2 06). To download the file, right-click (PC) or control-click (Mac) each file and choose "Save File As...". Save the files to a location where you can easily find them, such as your Desktop or Documents folder.
  1. Launch Excel, by clicking its icon in the Dock (Mac) or choosing it from the Start menu (PC). Note: the steps below may differ depending upon what version of Excel you are using.
  2. Choose File > Open... and navigate to the location where you saved the flowsud_2000.xls file. Select the file and click Open.
  3. Next, create your graph. Click and drag to select all of the date, precipitation, and streamflow data (columns C, D, and E). Then, select Insert > Chart ... or click the Chart Wizard button.
    1. Open the file flowsud_2000.xls and highlight the 366 rows of date, precipitation, and streamflow data (columns C, D, and E).
    2. Then, select Insert > Chart ... or click the Chart Wizard button.

Step 2 –
Create the Chart

Note: the steps below may differ depending upon what version of Excel you are using.
  1. Select the precipitation data (PRCP), column D, and plot as a column graph.
  2. Next, click on the horizontal (X) axis and choose, Chart > Source Data.
    Note: to select the X-axis, click on any number below the axis.
  3. The Select Source Data screen will display. In the lower right-hand corner, place your cursor in the Category (x) axis labels: box. Next, immediately select column C, Dates, by clicking on the column heading. Click OK.


  4. Save your graph. It should illustrate Precipitation and Date.
  5. Next, add the streamflow data. Choose Chart > Add data.
  6. The Add Data box will display. With your cursor in the Range: box, immediately select column E by clicking on the column heading. The data will populate the graph.

    Note: you will now have two columns of data, one red and one blue. The chart will appear very crowded due to the amount of data, and the blue (precipitation) data will be difficult to see until completely formatted. Save your workbook.


  7. Next, you will change the streamflow data from a column format to a line format. Click on the streamflow data set on the graph (you can select any point of the streamflow datayour cursor will indicate 14s when you click on the data).


  8. Select Format > Data Series. A new screen will display, select Axis > Plot series on Secondary axis. Click OK.


  9. With the streamflow dataset still selected, click Chart > Chart Type or click on the graphic for the chart type of line, and choose Line > Marked Line.


  10. Next create a blank sheet. Select Insert > Sheet > Chart Sheet. Copy your chart from the original worksheet to the blank chart sheet and give your chart a name on the tab on the bottom of the workbook. Save your file. Your finished chart should like the one below.

Step 3 –
Label the Series

For your chart's legend, change the labels to read Precipitation for Series 2 and Streamflow for Series 1.

  1. The labels need to be changed in the "cells" of the worksheet, on your data table. The change will automatically display in your chart. Launch Excel, by clicking its icon in the Dock (Mac) or choosing it from the Start menu (PC). Note: the steps below may differ depending upon what version of Excel you are using.
  2. Choose File > Open... and navigate to the location where you saved the flowsud_2000.xls file. Select the file and click Open.
  3. Click on the data table tab on the bottom of the worksheet to select the tab flowsud_2000.txt.
  4. Select the cell in D1 (the column header) and change the name from precip to Precipitation; select the cell in E1 and change from 14s to Streamflow.

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.

  1. Click on the chart tab on the bottom of the worksheet to select your chart.
  2. From the main Excel menu, select View > Toolbox > Formatting Palette.
  3. Click once in the main body of your chart to select it and expand the Formatting Palette to include all its formatting features.
  4. Under Chart Options > Titles, make the following selections from the drop-down menu: Select Chart Title and enter "Precipitation and Streamflow:Sudbury River 2000"; select Vertical (Value) Axis and enter "Inches"; and select Secondary Vertical (Value) Axis and enter "Cubic Feet per second".


  5. Save your file.

Step 5 –
Specify the Chart Location for Analysis

In your Excel workbook, you should now have the data table itself (with a name similar to flowsud_2000.txt) that has a small image of the original chart in it. Your workbook should also contain a separate sheet with the labeled chart, named Chart or something similar. To do the precipitationstreamflow analysis, you will need to further adjust your chart for easier viewing.

  1. Make a copy of your finished labeled chart and paste it onto your original data table sheet. Position the chart below the original smaller chart and provide room to adjust the chart's size.
  2. Save your workbook.

Step 6 –
Resize the Chart 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 three or four months of data within the width of the screen and the whole chart will be stretched out to the right and viewable by scrolling. Note: the chart on the chart sheet is not easily resizable, so you will need to resize the copy of the labeled chart on the data table.

  1. From your workbook, select the finished and labeled chart that you copied onto the data table worksheet. The chart will appear on top of the data cells.
  2. Re-size the resulting chart 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.


  3. Drag the title and legend, which shows the symbols for precipitation and streamflow, onto the upper left-hand corner of the chart.
    The image below, showing data from January through March 2000, 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 legend and the title have also been repositioned.


  4. Save your workbook.


« Previous Page      Next Page »