Create a graph – example:
Graph Lake Onota Lake Secchi data for site D2, 2000.
Step 1: Make a copy of the data. -
Open lakecharts workbook, summary worksheet. In this example, there are records for several lakes and several dates spanning several years.
-
Right-click on summary worksheet name tag. Select “Move or copy sheet”. Be sure to select “Create a copy” option. Accept the book name that’s given, select “summary” in the “before sheet” option – or move it elsewhere, if you prefer.
-
Rename the new sheet to whatever you want, (e.g. “chartsummary”).
Step 2: Sort / filter the data. -
Open worksheet chartsummary.
-
Position cursor on any cell in the waterbody column.
-
Open “Data” icon on toolbar. Select “Filter” option, select “Autofilter”. Arrows will appear on each column header.
-
Click the down arrow in the waterbody column, select “Onota Lake”. All other records will now be hidden from view.
-
Select “Data” icon again. Select “Sort” option.
-
Select field to sort on (if it doesn’t say “waterbody”, click the arrow to bring up the field list, then select). Select “Ascending”.
-
Select the secondary sort in the same way. Select “Site”.
-
Select tertiary sort. Select “Date”. Then press “OK”. Sheet will sort itself into something like this (showing first few relevant records only):
Waterbody
|
Date
|
Sample ID
|
time
|
depth
|
Samp#
|
Site
|
TP
|
Secchi
|
Onota Lake
|
5/5/00
|
|
|
|
|
D2
|
|
2.5
|
Onota Lake
|
5/24/00
|
|
|
|
|
D2
|
|
3.2
|
Onota Lake
|
6/13/00
|
|
|
|
|
D2
|
|
3.2
|
Onota Lake
|
6/28/00
|
|
|
|
|
D2
|
|
3.0
|
Onota Lake
|
7/11/00
|
|
|
|
|
D2
|
|
2.4
|
Step 3: “Massage” the data.
Beyond sorting and filtering, there are any number of operations you might want to do to facilitate graphing; actions that change the appearance of data, change the order of or remove some fields, etc. Whether or not this step is necessary depends on the shape of your existing data set and what type of chart you want to make. We provide two sample operations here. They illustrate approaches one might take to address a specific graphing problem. We don’t mean to imply these are the only ways to solve these or similar issues. Nor would it be possible to cover all possible issues in a short handbook. One of the examples involves deleting a few fields to make it easier to view your data set. Obviously, if you are taking actions that affect the actual data, you will definitely want to work from a copy, not your master data set.
-
Copy the Lake Onota 2000 records from chartsummary into yet another worksheet. We’ll call this one Secchichart. Be sure to copy the column headers also.
-
Delete the Sample ID, depth, TP columns, and all columns past Secchi for easier graphing, resulting in (first few records only are shown here):
Waterbody
|
Date
|
Site
|
Secchi
|
Onota Lake
|
5/5/00
|
D2
|
2.5
|
Onota Lake
|
5/24/00
|
D2
|
3.2
|
Onota Lake
|
6/13/00
|
D2
|
3.2
|
Onota Lake
|
6/28/00
|
D2
|
3.0
|
Onota Lake
|
7/11/00
|
D2
|
2.4
|
-
Change the date format. Highlight the “Date” column (Click on the column header). Select “Format Cells”, select “number”, “date”, and “3/14”. Records will now look like this:
Waterbody
|
Date
|
Site
|
Secchi
|
Onota Lake
|
5/5
|
D2
|
2.5
|
Onota Lake
|
5/24
|
D2
|
3.2
|
Onota Lake
|
6/13
|
D2
|
3.2
|
Onota Lake
|
6/28
|
D2
|
3.0
|
Onota Lake
|
7/11
|
D2
|
2.4
|
Step 4. Create the “draft” chart. -
Click on a cell where you want the chart to appear. Try somewhere near the bottom of the range of cells you are graphing. This makes it easy to see your work. It doesn’t matter if the chart covers cells with information in them. You can easily move it later.
-
Open the Chart Wizard. (Click the chart icon from the toolbar). A dialogue box will open.
-
Select chart type you want. For this example, try the default, clustered column.
-
Select data range. Click the small icon that looks like a miniature chart with a red arrow, at the right side of the “data range” box. Then bring the cursor to the cell containing the words “Date” at the top of that column. Drag the mouse down to the last date in this range: “11/2”. Then hold down the control key, bring the cursor to the cell containing the word “Secchi” at the top of that column. Holding the control key, drag down to the cell holding the Secchi value for 11/2. (Note: by including the column headings “Date” and “Secchi” in the cell ranges you selected, you have directed Excel to use these as default labels on the chart). Once you’ve selected the cells you want, click the small icon (now with downward-pointing red arrow) on the right side of the “Source Data – data range:” box. The dialogue box gives a preview of what the chart will look like. Don’t worry about appearance yet – we’ll change as necessary in a moment.
-
Click on “Series in Columns”, then on “Series in Rows”, to see which looks best. In this case, columns work best, even though the lines appear skinny at first.
-
Click on “Series” tab to open that portion of dialogue box. In this case, you only have one series, and everything else is in order: the name of the series, the values to be represented, and the labels for the X axis. Click “Next”.
Tip 4. Graphing Time-sequence data in Excel.
Excel charts that display data over time (e.g. Secchi values for one site, several dates) default to an automatic time-scale category axis. This means that it calculates the number of days (or weeks, months, years) from the first to the last date, and spaces the data points evenly along that axis (usually the X axis). In our experience, this tends to mess up the graph. The following example shows how to fix this.
-
If you are not already in the Chart Wizard, (Step 3) Open the “Chart Options” dialogue box.
-
Open the “Axes” tab. Under “Category (X) axis”, select the “category” option. In this Secchi example, the graph will look better, as data columns fill out.
-
Add/change titles: Open the “Titles” tab. Enter “Onota Lake Water Transparency 2000 – Site D2” (or something similar) in the title box. Type “Sample Date” to identify the X axis, and “Depth (meters)” in the Y axis box. The X and Y labels now appear along the 2 axes. The Y axis is one place to identify the parameter and/or unit of measure. You could also use the legend or the graph title for this information. Alternatively, you don’t always need a legend, particularly when there’s only one series, as in this case. To eliminate the legend, open the “Legend” tab and deselect the “show legend” box.
-
Click “Next”, then “Finish” to accept the chart as an object in the current worksheet.
Step 5: Clean up the chart.
Once the chart’s created, there are usually a few remaining steps to take to improve the chart appearance or fix problems. Some examples:
Tip 5. Getting all the X axis labels to display.
Sometimes the X axis labels don’t all show, or don’t align well with the data points they represent. This is usually due to space limitations. There are 2 ways to fix this:
#1: Reformat the labels.
-
Right-click the mouse over the labels and select “Format Axis”.
-
Select “Font”. Reduce size as much as you think appropriate. If this doesn’t do the job, also try:
-
Select “Alignment”. Move the text arrow closer to 90 degrees.
#2. Resize chart.
If reformatting labels didn’t fix it, try grabbing the side (or bottom) borders of the chart and stretching it, or removing any legend that’s on the side. That tends to widen the display area available for the X axis labels, allowing the missing ones to reappear.
Modify the data set. Let’s remove the last few dates from the chart. Right click on the chart to get a “Source data” option (you may have to do this a few times, moving the cursor around until that option appears). Open this, then click on the “values” icon in the “Series” dialogue box. Select the data range as you did above, only this time grab the Secchi column only, don’t include the “Secchi” heading itself, and drag down only to the value found in the 9/14/00 row. Save this, then click on the “category (X) axis labels and do the same for the date column. Click “OK”, and your chart now omits the late September - November readings.
Change the Y Axis scale. Sometimes you will want to lengthen or shorten the length of the columns, in order to better focus on some aspect of your data. For instance, the chart we’ve just created may look like this:
This display compresses the values, thereby visually reducing the significance of any change in transparency. You can fix this in two ways. One is by simply stretching the chart. Grab the top or bottom, and pull up or down to stretch. This will lengthen the bars and will probably change the numbers on the Y scale as well:
You can also manually change the Y axis scale: right-click over the Y axis. Select “Format “Axis”. Enter “1.5” for minimum value and “4” for maximum value. This view dramatizes the difference between values more than either of the previous displays.
Tip 6. Reorienting a graph to display Secchi values.
Let’s the last change the orientation of the graph columns to make it appear more like a Secchi measurement, which records depth from the water surface in a downward direction.
-
Right-click mouse somewhere over the Y axis, then choose the “Format Axis” option. Select “Scale” tab, then check the “values in reverse order” box. Check “OK”, and columns will now point down.
The above examples are just a few of many things that you can do to change chart appearance. Once you’ve created a chart try fooling around with it by trying different options (different chart types, changing scale or maximum and minimum values on the Y axis, etc.) to get the effect you want. You can easily create copies of the chart and work on these so you don’t lose your work on one you want to keep.
These are the basic steps involved in creating charts. The remainder of this handbook provides samples of charts that might be useful to water monitoring data sets.
Dostları ilə paylaş: |
