1. How to Open a File in Excel

Appendix

EXCEL How-Tos

Assignment 1

1. How to Open a File in Excel:

Click on the Office Button at the top left corner of your screen.
Click Open and navigate to file location, choosing the appropriate file.

2. How to Insert a New Worksheet:

Method 1: Click on the Insert Worksheet icon located at the bottom of the screen.

Method 2:On the Home tab, in the Cells group, click Insert, and then click Insert Sheet.

Method 3: Simply press Shift+F11 and a new worksheet tab will be inserted.

3. How to Rename a Worksheet

Rename the new worksheet to a descriptive name such as “BasinLimits”

Method 1: Right click on the sheet tabto be renamed and chooseRename.

Method 2: Double click on the Sheet 1 tab (which highlights the sheet name), type the new name, then press Enter.

4. How to Save an Excel File

It is a good idea to regularly save updated work in order to minimize data loss in the event of a computer malfunction. It is also a good idea to keep copies of your original files in case you need to redo a portion of the work, and to assign names to new files that allow you to keep track of what is in the file and which file is the most current.

Click on the Office Button at the top left corner of your screen.
Click Save to overwrite an existing file

Save As to save as a new file, navigate to the intended file location, rename file, click OK.

5. How to Copy Worksheet Contents

Copy the contents of BASINLIMITS into the new worksheet you created.

Highlight all of the cells you want to copy.
Method 1: Starting in cell A1, click once on the cell and, holding down the mouse button, drag the mouse over the block of cells to be copied.
Method 2: Starting in cell A1, simultaneously hold down SHIFT+CTRL, then hit the down arrow followed by the right arrow. This will highlight only the cells with data in them.

Hit CTRL+C or on the Home tab, in the Clipboard group, then click Copy.
Go to cell A1 in the newly created worksheet and paste the copied cells by either hitting CTRL+V or on the Home tab, in the Clipboard group, then click Paste.

6. How to Calculate the SUM of Values

In order to perform some of our calculations, we need to know how many cells are present in the Tiribí Basin. Because the BASINLIMITS worksheet contains all 1’s and 0’s, simply summing the values will give us the number of cells in the basin.

Choose an empty cell in which to enter the formula. (For example, A27)
Type in the formula to sum the values
Method 1: Type “=SUM(upper left cell address:lower right cell address)”
Method 2: Type “=SUM(“ and then highlight the area to be summed, then the closing parenthesis
Method 3: Type “=SUM(“ and then type each individual cell address followed by a comma and a parenthesis after the last number.

7. How to Multiply Values

Create a new blank worksheet called BASINELEVSin which to store the new computed values.

In cell A1, type “=”
Navigate to the TIRIBIELEV worksheet and click on cell A1.
Type “*”, the symbol for multiplication.
Navigate to the BASINLIMITS worksheet and click on cell A1.
Press Enter.

The product of the contents of A1 in the TIRIBIELEV worksheet and A1 in the BASINLIMITS worksheet appears in A1 of the new BASINELEVS worksheet.

Copy the formula across the worksheet to cover the extent of the Tiribí Basin.

Click on A1 in the new BASINELEVS worksheet.
Place the cursor over the black square at the lower right corner of the cell such that the cursor become a black cross.
Click and hold the mouse button, then drag the cursor down to cell A25
Click on the black square at the bottom right of the new column and drag it across to AN25.

The new spreadsheet will now contain the products of identical cell addresses in TIRIBIELEV and BASINLIMITS.

8. How to Create a 3-D Graph

Make sure the worksheet containing the data you wish to graph (in this case BASINELEVS) is open and active.

On Insert tab, Charts group, click OtherCharts, then the first Surface chart.

Right click within the chart area.
Choose Select Data.
Click the Switch Row/Column Button. The “Series” values should be in the Legend Entries section while the numbers (1,2,3, etc.) should be in the Horizontal Axis Labels section.
Click OK.

Right click on the “Back Wall” of the resulting chart.
Select 3-D Rotation
Under the Rotation section, enter 315° for the X: value.
Edit Axes, Titles and Legend data by clicking on Layout under Chart Tools and selecting the appropriate tools under the Labels or Axes groups.

9. How to Print a Graph

Click on the chart that you want printed to make it active.
Click on the Office Button.
Mouse down to Print.
It is advisable to Print Preview your chart first to make sure the layout is acceptable.
If changes need to be made to the layout, click Page Setup. Ifchanges need to be made to the chart itself, then click Close Print Preview to return to the chart and worksheet.
If you are ready to print, Click Print.

10.Create a Simple Line Graph (for Hypsometric Curve)

Highlight the cumulative percentages.
On Insert tab, Charts group, click Scatter, thenchoose Scatter with Smooth Lines.
Right click on the line that has been created.
Choose Select Data.
Under Legend Entries (Series) there should only be one series. Highlight this series and click Edit.
UnderSeries X values,add the cells containing thecumulative percentages.

Under SeriesY values, add the cells containing the elevations underBINS.
Click OK when finished to review the graph.

11. How to Convert Units of Runoff

The figures for annual discharge represent the average volume of water passing out of the basin every second in the year of record, e.g. in 1969, on average, 15.5 m3 of water left in the basin every second.

Calculate the total volume (m3) of water leaving in 1969. This total will be equal to 15.5 times the number of seconds in a year (always assume 365 days per year in this manual).
Calculate the average depth to which this total volume of water would cover the basin if it were spread evenly over the basin area. Do this by dividing the total annual volume (m3) by the surface area of the basin (302.4 km2). Watch out the units here, you must convert square kilometers in the denominator, to square meters. The resulting number will be a depth in meters (m3/m2 = m).

Note that 1Kilometer = 1000 meters.

As rainfall values are in millimeters (mm), the average depth of outflow (in m) must be converted to mm to facilitate a direct comparison between Precipitation and Runoff.

Convert m to mm.

Note that 1millimeter = 1 thousandth of a meter, or 1000mm = 1m.

The above process had been separated into three steps to facilitate understanding, but mathematically all three can be combined. In each year (1969, 1970, etc.)

12. How to Change the Number of Decimal Places

The default number of decimal places in EXCEL is 2, but for these exercises we only want 1.

Highlight the cells in which the format change is to be effective.
Right click on the highlightes area.
Select “Format Cells” from the menu.
Click on the “Numbers” Category.
Select the Number option.
Next to “Decimal places:” enter the number of decimal places required.
Click OK.

13. How to Perform Simple Numerical Operations

Operations involving rows and columns are easily carried out in EXCEL. For the sake of explanation, assume that the years are in column A, annual precipitation (P) in column B and annual runoff (R) is column E.

To calculate the difference (P-R):

Move to column F (presently empty), next to the first year of interest (say, row 3).
Type “=”
Click on the value of P for that year (B3).
Type “-“ (subtract).
Click on the value of R for that year (E3).
Hit Enter.

The difference between P and R should appear in cell F3.

Highlight F3.
Drag it down for all years (for example to F22).

The computer will then automatically complete that calculation (P-R) for all years.

14. How to Compute One Variable as a Percentage of Another

Assume that years are in column A, annual precipitation (P) in column B, annual runoff (R) in column E and annual (P-R) in column F.

To calculate annual Runoff as a percentage of annual Precipitation:

Move to column G (presently empty), next to the first year of interest (say, row 3).
Type “=”.
Click on the value of P for that year (cell E3).
Type “/” (divide).
Click on the value of P for that year (B3).
Type “*100” (multiply by 100).
Hit Enter.

The R as a percentage of P should appear in cell G3.

Highlight G3.
Drag it down for all years (for example to G22).

The computer will automatically complete that calculation (R as a % of P) for all years.

Repeat the exercise, only this time use the computed values of annual (P-R), in column F and place the answers in another blank column, for instance, column H.

15. How to Calculate Mean, Standard Deviation, and Coefficient of Variation

These statistics are built into the software, along with many others.

To compute the mean of a column of numbers:

Move to a blank cell, such as at the base of the column upon which the statistics are to be calculated.
To calculate the mean, type “=AVERAGE” and the range of cells for which the statistic will be calculated, e.g. “=AVERAGE(D1:D18)”

To compute the mean of a column of numbers:

Move to a blank cell, such as at the base of the column upon which the statistics are to be calculated.

To calculate the standard deviation, type “=STDEV” and the range of cells for which the statistic will be calculated, e.g. “=STDEV (D1:D18)”

You can also access these functions and more through the Function Icon, located to the left of the Formula Bar.

The coefficient of variation is {(standard deviation)/(Mean)}. Having calculated the mean and standard deviation, move to another cell (perhaps one below the mean or standard deviation calculation):

Type “=”.
Click on the cell containing the standard deviation calculation.
Type “/”.
Click on the cell containing the mean calculation.
Hit Enter.

Recall that when asked to calculate these statistics for several variables (different columns), first complete all steps for on column at its base. Then you can highlight the cells containing the computed statistics and drag the highlighted area beneath every column for which statistics are required.

16. How to Create and Time Series Plot

Click on the Insert tab.
Under the Charts group, select Scatter.
Click on the chart type “Scatter with Straight Lines and Markers”.

When the chart appears, right click on it and click Select Data.
Under Legend Entries (Series) highlight one the data series and click Edit.
Choose the cell that contains the appropriate series name or simply type the new name under Series Name (in this case, either Precipitation or Runoff).
Change the series X values to year by deleting the existing range of values and then highlighting the appropriate cells.
Change the series Y values to the values you wish to display (in this case, either Precipitation or Runoff) by deleting the existing range of values and then highlighting the appropriate cells.
Click OK.

If you ever have the need to add a data series, click the Add under Legend Entries (Series) and add the appropriate data, following the steps above.

17. How to Create Horizontal Lines on a Graph

Manually transcribe the four values from part B, a-d to the top of the next four available blank columns (for instance to the right of the calculations {P-R}, such that you have one column for Wet Year, one for Dry Year, one for High Flow Year and one for Low Flow Year).
Highlight each of these numbers and copy down the column such that each year has each of the four numbers.
Right click on the graph and choose Select Data.
Under Legend Entries (Series) click Add.
Name the Series according to which column of numbers you will be using.
Enter your Series X values(in this case, year).
Enter your Series Y values from the appropriate column (in this case, Rainy, Not Rainy, High Flow or Low Flow).
Click OK
Repeat for each of the remaining three columns so that you have four lines on the graph.

18. How to Changethe Ranges of Values Shown on Graph Axes

In general, the range of the scales shown on either axis is calculated automatically when the graph is generated. However, these ranges are often inappropriate or one may wish to generate graphs that all have identical ranges for ease of comparison.

To change the axis range:

Right click on an axis and select Format Axis.
Under Axis Options you will see radio buttons next to Minimum: and Maximum:.
Change the values by clicking on the radio button next to Fixed and entering the appropriate values.
When finished, click Close.

19. How to Insert a Row of Column into a Worksheet

Row:

Go to the far left of the worksheet and right click on the row number at which you want to insert a new row.
Select Insert and a new row will appear, moving all subsequent data down.

Column:

Go to the top of the worksheet and right click on the column letter for which you want to insert a new column.
Select Insert and a new column will appear, moving all subsequent data to the right.

You can also insert just a small section of cells (rather than an entire row or column) by highlighting the cell range within which you want to insert the new cells and right clicking in the highlighted section. This will open a dialog box asking you which way you want to move the cells. Just be cautious when using this function that any formulas you have in the affected portions of the worksheet are not accidently altered in such as way as to create miscalculations.

20. How to Add a Data Series to a Graph as Lines Without Symbols

Right click on the graph and choose Select Data.
Under Legend Entries (Series) click Add.
Name the Series according to which column of numbers you will be using.
Enter your Series X values (in this case, Annual Precipitation).
Enter your Series Y values from the appropriate column (in this case, the % Runoff).
Click OK
Repeat for each of the remaining three columns so that you have four lines on the graph.
Right click on one of the new lines and select Format Data Series.
Click on Marker Options.
Under Marker Type, click the radio button next to None. This will remove the markers.
Repeat the process by right clicking on each line in turn
When you are finished removing all markers, click Close.

Assignment 2

21. How to Create a Graph of Partial Sums (Running Totals):

Copy the value of one variable for the first year into a blank cell and column.
Move down one cell and enter an equation to sum the value of the variable for the second year with the value you just copied.
Copy this equation all the way down to the last year.
Repeat for the second variable.
Create a scatterplot with lines and markers with one partial sum as the X and the other partial sum as the Y.

22. How to Edit Labels on a Graph:

When creating graphs it is always important to be able to read the axes clearly. Each graph should include 1) an appropriate label on both axes, and 2) an indication of the units employed, and 3) should be clearly legible.

Inserting and editing labels. Graph and axes titles should be inserted during the process of graph creation, however once a graph has been created changes/additions can be made.

Under Chart Tools, click on the Layout tab.
For axis titles, in the Labels Group, click on Axis Titles.
Choose the appropriate axis (vertical or horizontal) and the direction in which you want the text oriented.
For the chart title, in the Labels Group, click on Chart Title.
Choose the title placement for you text.
To edit the text in any of the titles, right click on the title.
To edit the text properties (border, fill, alignment, etc.) select Format Axis Title for axes, or Format Chart Title for the main chart title.
To edit the font select Font.

Inserting and editing axis units.

Under Chart Tools, click on the Layout tab.
In the Axes Group, click Axes.
Choose the axis you want to edit (horizontal or vertical).
Click on the appropriate selection to display your axis units (most likely will be Show Default Axis).
Once you the units are displayed you may edit them by right clicking on the axis to be edited and selecting Font to change font properties and Format Axis for all other properties (minimum and maximum, tick marks, fill, alignment, etc.). Note: see pervious How to on editing minimums and maximums.

23. How to Add a One-to-One Line to a Graph:

Right click on the graph, choose Select Data.
Click Add under Legend Entries (Series).
Give the series a name that indicates it is a one-to-one line.
Choose the exact same column of numbers for both the Series X values and the Series Y values.
Click OK.
Right click on your new one-to-one line and select Format Data Series.
Click on Marker Options and click the radio button next to None under Marker Type to remove the line markers.
If you want the one-to-one line to pass through the origin, you will need to add a zero to the beginning of the data set used to make the line. See previous How to on adding rows and columns for more information.

24. How to Copy Computed Values Within and Between Worksheets: