This gallery provides a number of sample graphs used to report different types of water monitoring data. The charts themselves are found in the indicated Excel files. Information on source data or on graphing techniques can generally be obtained by opening the Excel files and then using the “Source Data” or “Chart Options” menus to locate the feature in question. We provide some information below on key steps that are required to make a particular graph or give it a specific feature.
In general, these charts were created by
-
Compiling a summary worksheet from a variety of different data sets.
-
Copying the entire summary worksheet to a transitional worksheet, called chartsummary, where data are sorted.
-
Copying a subset of the relevant records (now sorted) from chartsummary into the worksheet where the chart is located. In some cases, these data were further manipulated by additional sorting, removing or renaming fields, etc. An illustration of this process is found in the Several Dates, Several Sites – Multiple Series example, below.
-
Using the Chart Wizard to create a graph from this subset of records. In some cases, several charts are made from the same data set.
Charts for River Data
(Many of these will work for lakes also).
One site, several dates:
Chart type: Clustered column
Workbook: Deerfield Data
Worksheet: Site-date
Sort: by site, then by date.
Parameter: Dissolved Oxygen
One site, several dates #2: Value line added (series)
Chart type: Clustered column
Workbook: Deerfield Data
Worksheet: Site-date
Sort: by site, then by date.
Parameter: Dissolved Oxygen
Key feature: This chart 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)).
To create feature:
-
Add a column to the data set. Title it “MA WQ Standard”. Make all values equal to 5.
-
Create the chart as above.
-
Open “Source Data”. Open “Series” tab. “Add” a series. Select the new range of “5” values that you just created for the “Values:” for this series. Note: You could also just type in “5,5,5,5,5,5” (as many 5s as there are DO data points).
-
Open “Chart type” option. Open “Custom Types” tab. Select “Line – Column”.
-
Right Click on the new series line. Select “Format Data Series:”. Change “Marker:” to none. Change “Weight:” to 5 (or whatever you wish). Change “Color:” to red (or whatever you wish).
-
In this example, the legend entry for series 1 (the DO values) was cleared (right-click on the legend entry for DO, then “clear”), so the legend shows only “MA WQ Standard”. See next example for a different approach.
One site, several dates #3: Value line added (drawn in)
Chart type: Clustered column
Workbook: Deerfield Data
Worksheet: Site-date
Sort: by site, then by date.
Parameter: Dissolved Oxygen
Same graph as above, except:
Key feature: This chart has a “value” line by drawing one in
To create feature:
-
Create one site – several dates chart as above. Don’t bother with the 2nd series.
-
Open the “Draw” menu. Select a line (no arrow), drag it to the place on the chart where 5 mgl/l is represented. Stretch the line to extend evenly along that value.
-
Right Click line, select “Format Autoshape”, and format line as in previous example.
-
Select a “Callout” from the “Autoshapes” menu. Position it where you want on the chart, enter “MA WQ Standard” text in the box, size the font as necessary, then resize and reposition box and arrow as desired.
Note: this version of a “value” line looks better (because it fills the whole data plot area from left to right), but it can be quite troublesome. Sometimes it will disappear behind the chart. If you have trouble with this, try clicking the “Size with chart” option off and on (“Format Autoshape” menu). Also, if you change the scale of the Y axis, the line will usually reposition itself, so it won’t read 5 mg/l anymore. If you use this version, it’s best to get everything in place then print it immediately before it has a chance to mysteriously change on you.
Chart type: Mixed: Clustered column and stacked area
Workbook: Deerfield Data
Worksheet: Site-date
Sort: by site, then by date.
Parameter: Dissolved Oxygen
Same graph as above, except:
Key feature: Uses “Stacked area” chart subtype to denote value ranges. In this example, both the MA WQ standard of 5 parts per million (PPM or mg/l) DO for warm water fisheries and the cold water standard of 6 PPM are indicated.
To create feature:
-
First, create a row of blank cells above and below the location of the source data cells.
-
Create chart like one site – several dates chart above, with these changes:
-
The first series will contain DO values for site COR-010, 4/19 – 7/18/98. However, expand the “Source Data” range for this series to include one blank cell each above and below the DO values.
-
Similarly, when selecting “Category (X) axis labels” select the dates, along with one empty cell above and below.
-
Add a series. For “Name”, type “Warm Water”. For “Values”, type “5,5,5,5,5,5” (note there are 2 more values than there are data points in the DO series).
-
Add another series. For name, type “Cold Water”. For Values, type “1,1,1,1,1,1” (same idea as 2nd series).
-
Add titles, etc. to finish draft version of chart. It will look like a regular column chart. Click on one of the warm water series columns. Select “Chart type”. Select “Area”, subtype “Stacked Area” (2-D).
-
Do the same for the cold water series.
-
For each of the warm and cold water series, select “Format Data Series”, and choose a color for the “Area” that you think is appropriate for the condition you are depicting.
-
Then select callouts from the “Autoshapes” menu to identify the areas, or use text boxes or legends.
Note: this chart is a little tricky to build, but it does solve some of the problems associated with the value lines used above, and it allows you to use color to denote different water quality ranges. It works by creating an area of a given height. We selected 5 for the first area, to match the MA warm water standard of 5 PPM. The next series is added to this, so our given value of 1 brings it up to the cold water standard of 6 PPM. Because these color zones are opaque, you have to turn off Y axis grid lines to make this chart work. (“Chart Options”, “Axes”, “Grid Lines”, “Value (Y) Axis” off). A similar approach is used to display acceptable pH ranges in the next example.
One site, several dates #5: Value areas added
Chart type: Mixed: Clustered column and stacked area
Workbook: Deerfield Data
Worksheet: Site-date
Sort: by site, then by date.
Parameter: pH
Similar to above, except it uses 2 areas to display an upper and lower acceptable limit for pH data. Also obtains the standard values via a slightly different approach.
Several dates, several sites: 2-D
Chart type: Clustered column
Workbook: Deerfield Data
Worksheet: Site-date
Sort: by Date, then by Site.
Parameter: Dissolved Oxygen
Key feature: Shows multiple sites and dates. This chart also uses multiple series. In this example, each series represents the DO values for a specific date.
To create:
-
Open Chart Wizard. Select chart type cluster column.
-
Select Values. The source data for this graph are found in the site-date worksheet, range A45-C74. When obtaining source data for the graph, proceed one series at a time. Start by selecting DO values for the first date (4/19/98), all sites (i.e. C46-C55 in this example).
-
Select “Series” tab, then click “Name” box for this series. Click on any cell containing the value “4/19/98”. Accept this.
-
Click “Category X axis labels”, and select a range that includes site names for all the sites (e.g. B46-B55).
-
Click “Add Series”. Click “Values” for the second series. Select all DO values for the next date (i.e. for 4/26/98). Click “Name”. Select a cell with value “4/26/98”. You don’t need to change the X axis labels.
-
Click “Add Series” again, and continue as above until all dates are covered.
-
Add titles, legends, formatting, etc. as desired.
Several dates, several sites: 3-D view.
Chart type: 3-D column
Workbook: Deerfield Data
Worksheet: Site-date
Sort: by Date, then by Site.
Parameter: Dissolved Oxygen
Key feature: Uses 3-D to show multiple sites and dates.
To create:
-
Open Chart Wizard. Select chart type 3-D column.
-
Select data as in the previous example, adding a series at a time until all dates are covered.
-
Click Next. Give a chart title and a Z axis title. No need for X or Y titles here.
-
Once finished, you can play with the orientation of the chart by selecting “3-D View” and trying different elevations, rotations, or perspectives.
In our view, the 3-D chart should not be over-used. It starts to look overly busy as more data points are added, and depending on the view, it’s either hard to determine what values are represented, or some values will be hidden from view.
Several Dates, Several Sites – Multiple Series
Chart type: Clustered column
Workbook: Deerfield Data
Worksheet: Summer 03 fecal
Sort: by Site vertically, by Date horizontally.
Parameter: Fecal coliform
Key feature: Data set is arranged differently, to make graphing easier. Also, method for dealing with extremely high values is employed.
To Create: Data is first re-organized to facilitate graphing: one row displays data for each site, several dates.
-
Open chartsummary worksheet. Add a column here to make it easier to filter data to reveal only 2003 data.
-
Insert a new column just after “Date” column. Name it “Year”. In row 2 (row just below title row), enter this formula: “= year(b2)”. This will yield the year of the date found in cell B2.
-
Copy this cell , paste to all cells in this column.
-
Click on first cell in “Year” column. Open “Data” dialogue box, select “Autofilter”. Down arrows will appear on all column headings. Click on “Year” column. Drop-down window will display year values. Select “2003”. The data set will now be filtered to show only 2003 samples.
-
Select “Data” again. Select “Sort”. For first sort, sort by date ascending. Second sort, by site ascending.
-
Copy 1 set of data from the “site” column (i.e. range of cells from “SOR-010” to “WBD-010”).
-
Open a new worksheet. Call it “summer 03 fecal”.
-
Paste site list to this worksheet, 2nd row or lower.
-
Resort the chartsummary worksheet, this time by site ascending, then date ascending.
-
Hide “Temp”, “DO”, and “DOSat” columns. (right click mouse, select “hide”).
-
Copy 1 set of dates. Copy the dates only – not the site names, fecal values, or other cells. Copy a set that contains all the dates that were sampled – i.e. the copied cells will include these dates: 6/15/03, 6/29/03, 7/13/03, 7/27/03, 8/10/03 and 8/24/03. Go to “summer 03 fecal” worksheet, click on the cell that is 1 row above and to the right of the cell containing first site name (“COR-010”). Paste special (either via “Edit” icon or by right-clicking mouse): check “transpose” option. You have just created data headings, with the dates listed all in one row.
-
Copy each set of (six) fecal values for each site in the same way: go to chartsummary worksheet, copy the fecal values for the first site (COR-010), return to summer 03 fecal, and paste special (transpose) into cell to the right of “COR-010” and below “6/15/03”. Continue until all values for the year are entered. You now have the data arranged so that each row contains site name and fecal values for each biweekly sampling at that site.
-
Create graph. Open Chart Wizard, select clustered column chart type. For data range, highlight the entire array of cells, including header site IDs and dates. Select “Series in” columns. The chart is now arrayed in a convenient fashion: each series equals the fecal values for a different date, and site names are displayed sequentially along the bottom of the chart. Enter titles, etc. to suit.
Tip 7. Displaying extremely high values:
Another common scale problem occurs when trying to display widely varying values, as is often the case with fecal coliform and other bacteria. This data set has values ranging from 3 to 1667. If you use a “normal” Y axis scale that tops off somewhere near the higher (i.e. 1667 value), many of the other results will be virtually invisible, and others will seem very small, giving the impression that the water at these sites/dates was cleaner than it really is. You could use a logarithmic Y axis scale, which visually exaggerates low numbers and diminishes high numbers by making giving each order of magnitude (e.g. 0-10, 11 -100, 101 - 1000, etc.) equal height in the column. This can give the wrong impression in the opposite direction (i.e. that there isn’t that much difference between samples. What we did with this graph was to set a low upper limit to the y-axis scale. (We opened the Y Axis dialogue box, opened “scale”, and set maximum to 800. Additionally, we clicked on the column for the high data reading and formatted the “data point” to display value). This cuts off the high value, but notice that the high value is listed atop the bar. This suggests that the pollution levels are literally “off the charts,” and reinforces the idea that this reading is much higher than the others.
Multi-year Charts: Several Sites, Multiple Dates
Chart type: Clustered column
Workbook: Deerfield Data
Worksheet: multi-year
Sort: by Date, then by Site.
Parameter: pH
Key feature: Charts data for multiple years.
Graphing multi-year data can be a problem because of inconsistencies in dates. E.g. you don’t sample on April 15, May 15, June 15, etc. each year, and you may not collect the same number of samples each month. These present challenges in organizing data to make it easy to graph and compare values from year to year. We offer one approach here, with some more complex examples given the lake chart section below.
To create: This first example is the easiest: simply compare values for 1 April sampling date in 1998 with 1 April sampling date in 1999 - several sites each year. Data are split into 2 series: Series 1 = values for April 19, 1998 at all sites. Series 2 = values for April 19, 1999 all sites. For each series, type in the Series name “April 98” and “April 99”. These will show on the legend.
Calculated data - Several Dates, several Sites.
Chart type: Clustered column
Workbook: Deerfield Data
Worksheet: Summer 03 fecal
Sort: by Date, then by Site.
Parameter: Fecal coliform
Key feature: Statistical summary of data is performed, then graphed.
To Create: Data is first re-organized to facilitate graphing: one row displays data for each site, several dates.
-
Open chartsummary worksheet. Add a column here to make it easier to filter data to reveal only 2003 data.
-
Insert a new column just after “Date” column. Name it “Year”. In row 2 (row just below title row), enter this formula: “= year(b2)”. This will yield the year of the date found in cell B2.
-
Copy this cell , paste to all cells in this column.
-
Click on first cell in “Year” column. Open “Data” dialogue box, select “Autofilter”. Down arrows will appear on all column headings. Click on “Year” column. Drop-down window will display year values. Select “2003”.
-
Select “Data” again. Select “Sort”. For first sort, sort by date ascending. Second sort, by site ascending.
-
Copy 1 set of sites (i.e. range of cells from “SOR-010” to “WBD-010”).
-
Open a new worksheet. Call it “summer 03 fecal”.
-
Paste site list to this worksheet, 2nd row or lower.
-
Resort the chartsummary worksheet, this time by site ascending, then date ascending.
-
Hide “Temp”, “DO”, and “DOSat” columns. (Right click mouse, select “hide”).
-
Copy 1 set of dates (i.e. range of cells from “6/15/03” to “8/24/03”. Go to “summer 03 fecal” worksheet, click on cell 1 row above and to the right of the cell containing first site name (“COR-010”). Paste special (either via “Edit” icon or by right-clicking mouse): check “transpose” option. This will display the dates along the same row.
-
Copy each set of (six) fecal values for each site in the same way: go to chartsummary worksheet, copy the fecal values for the first site (COR-010), return to summer 03 fecal, and paste special (transpose) into cell to the right of “COR-010” and below “6/15/03”. Continue until all values for the year are entered. You now have the data arranged so that each row contains site name and fecal values for each biweekly sampling at that site.
-
Add a column for the geometric mean of each site’s results over the summer: write “Geomean” in the column to the right of the last date (“8/24/03”).
-
Place cursor on the cell just below this title. Click the function icon on toolbar (fx). “Paste function” dialogue box opens. Select “statistical function”, then scroll down the menu and select “GEOMEAN”. Click the range selection box (icon with red arrow) for “Number 1”. Then highlight the six cells to the left of the cell you are in (i.e. the range of fecal values for that site). Accept. Select OK. Function closes, and the value is now shown. Alternatively, you could just type in “=geomean(c3.h3)” (or whatever cells the fecal values are found in).
-
Copy this cell, paste in the subsequent cells for each site. Then format these cells by highlighting them, select “Format cells”, select “Number”, and set decimals to 0. Note that it doesn’t matter if there are empty cells, or even ones with text (e.g. “NS” for “not sampled”). Excel will calculate geometric mean only on the cells with number values.
-
Now create graph: Open chart wizard, select Clustered Column chart type, select the cells for the geometric mean as “Values:”, add labels, titles, etc.
Two Parameters, Two Axes – One Date, Several Sites.
Chart types: 3 different custom types: Lines on 2 axes, line/column on 2 axes, 2 columns on 2 axes.
Workbook: Deerfield Data
Worksheet: Site-date
Sort: by Date, then by Site.
Parameter: pH and ANC
Key feature: Compares 2 different parameters, employs 2 different Y Axes.
To create: We give 3 different examples.
#1: Two lines.
-
Arrange data by date and site as shown in worksheet Site-date, in the vicinity of cell AI10.
-
Open Chart Wizard, select Chart type “lines on 2 axes” (a custom type).
-
For data range, select site names, pH and ANC values (include headers) all in one grab.
-
Enter title, Value Y axis (“pH”), 2nd Value Y axis (“ANC”). Finish; pH and ANC are each a separate series in this chart.
-
Chart will now appear as 2 lines, with both a pH and an ANC axis.
#2. Line/column on 2 axes.
-
Create as above, but for “Chart type” select “Line/column on 2 axes”.
#3 Two columns on 2 axes.
-
Create the line/column 2 axes chart above.
-
When completed, hold the cursor somewhere on the line. Right-click, select “Chart type”. Select cluster column. Chart will change this series only to a column, so you have two columns. However, it will look like stacked columns:
-
Click on one of the columns. Select “Format Data Series”. Select “Options”. Select “Gap Width”, and increase or decrease the value. It will begin to appear as 2 separate columns, one in front of the other. Fool around with this feature until you get the look you want.
One Date, Several Sites. Dissolved Oxygen % Saturation
Chart type: Clustered column
Workbook: Deerfield Data
Worksheet: DO Saturation
Sort: by Date, then by Site.
Parameter: Dissolved Oxygen
Key feature: This chart uses a table and a lookup function to calculate the % saturation of a DO sample. These are found in worksheet Do Saturation. To use this to determine DO from your samples, enter the sampling information (site #, date, DO and water temperature measurements) in the yellow cells. If you need to adjust for elevation or low barometric pressure, check the elevation correction table and replace the default “1” factor (light yellow cells) with the corrected factor. DO saturation will then automatically be calculated. We created a standard clustered column chart to display the values. Our only modification was to adjust the Y axis scale to top out at 100%.
Dostları ilə paylaş: |
