One site, several dates – data table included
Chart type: Clustered column
Workbook: Lakecharts
Worksheet: Secchidepth
Sort: by Site, by Date.
Parameter: Secchi transparency
Key feature: This is the same chart as the one described in the introductory “how to create a graph” section, but it has one added feature: The data number values are displayed in a table below the columns.
To Create this feature: Make the chart as described in the introductory section, but under “Chart Options”, select “Data Table”, and click “Show data table”.
Lake depth profile
Chart type: XY (Scatter), with data points connected by smooth lines.
Workbook: Lakecharts
Worksheet: depthchart
Sort: by Date, then by Depth.
Parameter: Dissolved Oxygen. (Temperature example also given)
Key feature: This chart displays a profile of data taken from several depths, top to bottom. This is commonly used to display dissolved oxygen or temperature depth profiles.
To Create:
It’s best to have data arranged in a row-column array like this (first few records only are shown):
Date
| Depth (m) |
Temp
|
DO
|
6/6/96
|
0.5
|
17.6
|
7.43
|
6/6/96
|
1
|
17.4
|
7.36
|
6/6/96
|
2
|
17.2
|
7.35
|
6/6/96
|
3
|
17.0
|
7.33
|
-
Filter chartsummary to obtain records only for Baker’s Pond. (Note that chartsummary is a copy of summary worksheet. For safety’s sake, it’s generally good to work from a copy of your data).
-
Copy these records to depthchart worksheet.
-
Delete or hide all columns except Date, Depth, Temp and DO.
-
Sort data by date, then by depth.
-
Open Chart Wizard.
-
Select chart type: XY (Scatter), with data points connected by smooth lines.
-
Select data range: Build this one series at a time, so select all DO values for the first date (e.g. “6/6/96”).
-
For “Name”, click on any cell that has the date value (“6/6/96”).
-
For “Y Values” select all depth values for one date (e.g. 0.5 – 15).
-
Add a series. Name it for the 2nd date (e.g. “6/29/96”), select the DO values for that date as “X values”, and select the depth values again (0.5 – 15) for “Y values”. Continue until all data for all dates are added as new series. The X values will change with each series (each new date), but the Y values will always be the same.
-
Give the chart a title, enter labels for X axis (“DO (mg/l)”) and Y axis (“Depth (meters)”).
-
When Chart Wizard is complete, open “Format Axis” (for Y axis), select “Scale”, and click “Values in reverse order”.
Note: If you also want to
create a temperature profile, you can save some steps by copying this chart, opening the “Data Source” option on the new chart, and changing only the X axis values for each series, by selecting temperature values instead of DO. Y axis and dates will remain the same. Change titles, of course. An example of this chart is also shown in the
depthchart worksheet.
Note: When creating a chart of this type, be careful of missing entries. For instance, with the Baker’s Pond example suppose you only sampled to 12 meters on September 12. If when you made the chart you set the Y Axis data range to 0.5 – 15 meters as will all other dates, you’ll have 3 extra Y values and the chart will show erroneous readings for those depths. So make sure that the Y range and the X range have the same number of values in them.
One site, multiple dates – multiple series, multiple years.
Chart type: Clustered column
Workbook: lakecharts
Worksheet: Onota charts
Sort: by Date, then by Site.
Parameter: Secchi transparency
Key feature: Charts data for multiple years.
In the Deerfield River example (see above), it was a simple matter to graph results from several sites on 2 separate dates, by creating a different series for each date. Trying to graph one site over different dates isn’t so easy, however. Our first attempt to do so (top of Onota charts worksheet) shows the problems that arise. For one thing, the X axis names will only show one series of dates (either 2000 or 2001), which is misleading, particularly in this example; 2000 sampling started in May, and 2001 sampling started in January. The single set of 2000 dates don’t reveal that. You can select both sets of dates to be included as labels (by placing the 2 sets in adjacent columns, then highlighting both columns when you select the range for “Category (X) labels”. But this creates an ugly display. Second, even if you know the dates for both years, you end up with dates side by side that are very different from one another: e.g. a January date alongside a May date.
Two approaches to fixing this:
Approach #1: Average readings for each month, compare monthly averages for each year.
To create:
-
First create another column, “avgmonth”. Use the average function to obtain an average for all readings for each month of the year (but let’s skip those months where there aren’t data for both years). Example (in worksheet Onota charts): type (=average(D2.D3) for the May 2000 average.
-
Do this manually for each month of the 2 years. Some will have only 2 values to average, some will have 3 or 4.
-
Then make a copy of the data set, including the average dates.
-
Copy this to another place on the worksheet (e.g. below the first set of #s). Paste Special, pasting values (not formulae).
-
Note: make sure your date fields retain their format, otherwise they might take on a very different look. If they have changed, highlight the date cells, select “Format cells”, select “Date”, and the style you want.
-
Delete all rows that do not contain an average value. This is just to make the average values contiguous. Also, delete any rows for which there are not comparable months in both years (i.e. keep only May – October).
-
In the column to the right of the averages, type in “May”, “June”, “July” etc. to “October” in each appropriate row. You only need this for the first year.
-
Create a chart: Open Chart Wizard, Cluster Column type chart. We’ll build this one series at a time.
-
Open “Series” box. For “Values:”, select 2000 Secchi values from the average column.
-
For Series “Name :”, type in “2000”.
-
For Category (X) Axis Labels:”, select the cells that you just typed in month names for.
-
Add a series, and repeat above steps to enter 2001 data.
-
Give titles, remember to change X axis to Category (under “Chart options”), and Y axis scale to “Values in reverse order” (under “Format Axis”) to for this Secchi chart.
Approach #2: Manually select a subset of dates that provide a meaningful comparison.
To create:
-
Copy the data set as above to another location on the worksheet (our example goes down to about cell A55).
-
For easier viewing and working, delete the “Water body” and “Site” fields (NOT the whole column, just those cell range).
-
Working one or 2 dates at a time, find 2001 dates that are close to the 2000 dates, and move the 2001dates (and their Secchi values) to the corresponding 2000 rows. If you end up with extra dates (e.g. 4 August dates in 2000, only 2 in 2001), delete the extras. When complete the data set would look like that shown in Onota charts worksheet, towards the bottom.
-
Add a column that describes the 2000-2001 date pairs (e.g. “Early May”, etc.)
-
Create chart: Clustered Column. For Data Range, Select the range of dates (actual dates, not the descriptors we just made up) and use the control key to also get the 2000 and 2001 Secchi values. You should have your chart in 2 ranges, as above.
-
Name the ranges as above, and for X axis labels, select the descriptors just created.
-
Finish the chart as above.
This approach to making a multi-year chart is less desirable than the first in that it deletes some values, creates imprecision the dates and names selected to describe the sample dates, and is somewhat arbitrary in selection of dates to pair. However, there may be reasons why you don’t want to use averages –
for instance, if you are graphing widely variable results that will get lost in an average.
One site, several dates: Value line added (series)
Chart type: Clustered column
Workbook: Lakecharts
Worksheet: Bakers DO
Sort: by site, then by date.
Parameter: Dissolved Oxygen
Key feature: This chart and the following one are similar to the Deerfield River examples above, in the “Charts for River Data” section. See the description in that section for a discussion of how to make these charts. This one has a “value” line added to indicate (in this case) the MA water quality standard for dissolved oxygen (warm water: 5 parts per million (or mg/l)). These charts will work for lakes when DO is being collected at a single depth, rather collecting DO data along a depth profile.
One site, several dates: Value line added (drawn in)
Chart type: Clustered column
Workbook: Lakecharts
Worksheet: Bakers DO
Sort: by site, then by date.
Parameter: Dissolved Oxygen
Key feature: Like the previous example, this is a lake version of the Deerfield River examples above, in the “Charts for River Data” section.
Note that these last two examples differ also in the Y axis scale. The first chart has a lower limit of 4 Parts Per Million, while the second has a 0 minimum value. The first (created by formatting the Y Axis scale and setting minimum value to 4) has the effect of emphasizing the difference between the highest value and the others.
DO Saturation example.
See the DO Saturation example in the River Chart section (Last chart of section).
This chart also shows how to obtain DO Saturation values from a table we have created in workbook Deerfield data, worksheet DO Saturation.
