Step 5: Plot Scatterplots to Make Hydrographs

1. At this point, you should have 3 datasets (1 that was provided and 2 that you downloaded) in the Excel Spreadsheet, and they should all be labeled and prepared for plotting. Remember that you were asked to download your oldest and longest record first, so we will start plotting that one first. We have plotted data for Victoria, British Columbia already. You will have to complete hydrograph plots for the other two sites following the instructions below.
2. Select the data you want to plot; in this example we will plot the mean sea level data (column F) on the y or vertical axis, and the time (column E) on the x or horizontal axis (See Figure 4.48). So, we will plot up the first three years of the data record for demonstration. So, we will select 1908, 1909, and 1910 and click "Insert" a "Scatter" plot using the "Scatter with smooth lines and markers" option.
3. The graphed result should look something like the plot shown in Figure 4.48. A scatterplot is generated and is inserted into the spreadsheet. We have also produced a hydrograph (scatterplot) for Victoria, British Columbia for you to act as model.
4. The plot is pretty ugly at this point, in part because of the negative numbers on the y-axis and issues with scaling of the x axis. Now you know why the PSMSL folks like to use the RLR convention when plotting their sea level data. But let's roll, because we can easily fix these problems.
5. So, to fix the "y axis" issue, click on the y axis to select the axis itself. A box should appear around the y axis in this case from -300 to 150. Once highlighted, right click and select "Format Axis" as shown in Figure 4.49.
   1. At this point, change the position where the X axis crosses the y axis. In the "Format Axis" window, under "Axis Options" near the bottom of the box is a section that says "Horizontal axis crosses:"
   2. Here, you should select the option box for "Axis value:" and enter a negative value (the most negative of all of your data points). We will use "-300" since that is the most negative in our example data.
   3. When you hit close, you should see the X axis labels drop to the bottom of the graph as shown in Figure 4.49u
6. Alright, so now the X-Axis... Notice that the first data point, January, 1908 is at the middle of the plot. So, we don't need all the empty space to the left of it. Excel arbitrarily set J-1900 as the origin for this axis, so we need to tell Excel where we want to start plotting data. Remember, Excel was told the X axis values are date values, so it has plotted the data in days from 1900 to the end of the dataset (arbitrarily ending the plot at September 1913).
   1. So, to fix this, click on the x-axis and make sure a box surrounds the labels from J-00 to S-13. Then, right click and select "Format Axis". The format axis box should come up.
   2. In Axis Options, you will see that the Minimum: was Auto selected as "0".
   3. Change this to a "Fixed" number. In our case, we want our first data point to be January 1908; so, to figure out what number to use, we multiply 365 x 8 years.
      • 365 because Excel is plotting days each year and 8 because we are 8 years into the 1900s. So, we would enter 2920 in the Minimum "Fixed" box.
      • Now, since we are plotting 3 years of data, we would multiple 365 x 10 and type 4015 in the Maximum "Fixed" box. Once you do this, Excel may automatically add one, so you might see "4016.0" show up. That is ok. You should see something that looks like Figure 4.50.
        
        
        

        ×

        Figure 4.48: Plotting data from the Panama example. Time (Column E) will be plotted against MSL (Column F) to generate a"sea level curve through time" plot as discussed in Step 4. Screen clipping taken: 1/16/2014 9:37 AMCredit: Microsoft Excel Screenshot
        
        
        
   4. One more thing to fix... The labeling interval on the x-axis needs to be changed. If you want one label per year, you can change the "Major unit: Fixed" value to "365." Go ahead and set "Minor unit Fixed" value to "0" as shown below. The graph should be updated as you make changes to each axis option field. Now you should have a decent looking graph, with nice, well-labeled axes.
      • Som now that the plot looks better, let's make it a sheet in the spreadsheet, rather than an object in another. Select the chart, right click on it, and click "Move Chart." A window will open and select "New sheet" and enter a name changing the name Chart1 to something like Panama Sea Level. When this happens, the font might appear really small. You can easily change that. Make sure the chart is selected and you can change the font size to something like 24 or larger - whatever suits you. See Figure 4.51.
        
        
        

        ×

        Figure 4.49: Screenshot for Step 4, Part 5. Here we are modifying the axis labels and ranges to improve the look of the plot.Credit: Microsoft Excel Screenshot
        
        
        
        

        ×

        Figure 4.50: Screen shot showing how to improve the axes and labels of your sea level plot.Credit: Microsoft Excel Screenshot
        
        
        
        
        

        ×

        Figure 4.51: Screenshot showing the result of this step.Credit: Microsoft Excel Screenshot
        
        
      • Before you go any further, let's fix the legend on the right side of the chart (Figure 4.51 above). You will notice that the chart title is the same as the legend, so let's change the legend so it shows that this is Panama. This is important because you will soon be adding data from additional sites.
        • So, first, click on the line of data points, and then right click to bring up the "Select Data" then "Select Data Source" window. Your screen should look something like what is shown in Figure 4.52.
        • Now, click on "Edit" in the Legend Entries (Series) box. Figure 4.53 will appear.
        • In the "Series name:" box - type Panama Tides or whatever title describes the location of your dataset. Hit "OK", and "OK" again until you are back at the chart screen. The legend on the right should say exactly what you typed in the "Series name:" box.
          
          
          

          ×

          Figure 4.52: Select Data Source screenshotCredit: Microsoft Excel Screenshot
          
          
          
          
          

          ×

          Figure 4.53: Edit Series screenshotCredit: Microsoft Excel Screenshot