Part 4—Create a Streamflow-Precipitation Graph
Step 1 – Select Data and Open the Chart Wizard
- 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.
- Choose File > Open... and navigate to the location where you saved the flowsud_2000.xls file. Select the file and click Open.
- 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.
Show me how
Hide
Step 2 – Create the Chart
- Select the precipitation data (PRCP), column D, and plot as a column graph.
- 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.
- 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.
- Save your graph. It should illustrate Precipitation and Date.
- Next, add the streamflow data. Choose Chart > Add data.
- 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.
- 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 data—your cursor will indicate 14s when you click on the data).
- Select Format > Data Series. A new screen will display, select Axis > Plot series on Secondary axis. Click OK.
- 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.
- 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.
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.
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 precipitation—streamflow analysis, you will need to further adjust your chart for easier viewing.
- 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.
- 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.
