1 G476/576 Hydrology Lab Exercise Introduction to Microsoft Excel Spreadsheets and Data Manipulation d:wou:hydro:intrexcl_ver2.doc INTRODUCTION The study of hydrology is based primarily on the observation, analysis, and modelling of water-related processes at or near the Earth's surface. The observation of hydrologic phenomena is accomplished by data collection over varying spatial and temporal scales. Examples of hydrologic data include yearly rainfall magnitudes for the state of Oregon, monthly stream discharge rates for the United States, or reconstruction of global ice budgets for the past 500,000 years. The steps in hydrologic analysis include: (1) conceptual observation, (2) hypothesis building (deriving relationships), (3) time-averaged data collection, (4) data analysis, (5) hypothesis testing and reformulation, (6) continued data collection and analysis, (7) model building and validation, and (8) application of hydrologic models to real-world problems (e.g. water resource planning for a city). Microsoft Excel is a spreadsheet software program that is very useful in basic data manipulation and analysis. The objective of this lab is to introduce students to Excel techniques, that will be later applied to hydrologic problems. HYDROLOGY CLASS COMPUTER ACCOUNTS ______________________________________________________________________________________ Lab Task 1 - Testing Your Hydrology Student Account 1. Find a network computer station in the lab and log-on at the Windows NT prompt with your username and password. 2. After you log-on to the server, perform the following mouse-click functions on the pull-down menus: Start-Programs-Windows Explorer or use “My Computer” to check your H:\ folder 3. Check your log-on folders, you should see the following folders available: - H:username this is your personal folder space for storing files. 4. Using windows explorer or My Computer, create a new folder on your H:\ drive entitled “Hydro” ______________________________________________________________________________________ Other notes / things to think about: - All computers in the student labs have the local hard drives (e.g. C:\ drive) "locked" so that you cannot save or install software to the computers. You can only save to the network account, to the floppy drive (a:\ drive), a flash memory stick, or to a CD. - Students are encouraged to work in teams, but remember that each of you will have to turn in your own work for grading. Some of this work will be electronically submitted. If you work in groups or with partners, make sure you know how to do the exercises and save your own data files. You will be tested on your ability to work with the computer as a tool!2 SPREADSHEET SOFTWARE Microsoft Excel (part of MS Office suite of software) is a spreadsheet software package that facilitates data collection, data organization, and data analysis. This software is widely used at Universities, businesses, and government facilities. This is a good tool to know how to use. Excel is widely available on campus. An overview of Excel includes: - Excel is software that organizes information into a series of rows and columns (e.g. a "table" of data) - Information may either be numeric (i.e. numbers) or alphanumeric (words, letters, and numbers combined) -Excel files are referred to as "Workbook" files, and are given a *.xls extension. Within each workbook, you may have multiple "worksheets" with different types of data. Examples of a workbook might include river discharge data for the Willamette River (e.g. a file named WillamQ.xls). Within the WillamQ.xls file, there may be 12 worksheets with average monthly data for each month respectively. -Excel data is organized in columns and rows ("spreadsheet" or "table" format). - columns are identified by letter ID: A,B,C....AA, AB, AC...etc. - rows are identified by number ID: 1, 2, ...80,000 - individual "cells" are identified by a unique column-row identifier: e.g. A1 = first cell, B25, etc. - The column-row structure of Excel worksheets allows quick and easy analysis of data, mathematical calculations, graphing, etc. Think of Excel worksheets as "calculator" software, but you only have to enter the data once and can perform different types of mathematical and statistical functions. Example data functions include: adding, subtracting, multiplying, dividing, means, standard deviations, frequency distributions, histogram plots, x-y scatter graphs, graph-line fitting, data sorting, trig calculations (sin, cos, tan, etc.), evaluation of algebraic / trigonometric equations... plus much more... sorting in increasing order, sorting in decreasing order, sorting in increasing alphabetical order... plus other stuff. -Types of data/information that can be analyzed include: numbers, angles (degrees, radians), names (e.g. sorting alphabetically) -Math functions are performed on the "cells", regardless of the specific data that is contained in the cells. "Cell equations" are used to manipulate and analyze the data that are contained in the cells. We will refer to this methodology as "Cell Algebra". ______________________________________________________________________________________3 Lab Task 2 - Basic Data Entry, Formatting and Analysis in Excel 1. Log-on to campus computer. 2. Start microsoft Excel software Start-Programs-Microsoft Excel 3. Once in Excel, note the column-row "cell" structure. Move the mouse around and note the "+" pointer. Move the mouse / pointer, and click on Cell D5. Note that the cell becomes outlined as "active". Type your first name in cell D5. Now click on Cell B 9, type in your last name. 4. Click back on Cell D5 and change the font size to "22" (the font style and size are located on the tool bar to the top of the spread sheet). Note that your name does not "fit" in cell D5 anymore. Change the width of cell D5 with the follow menu clicks: Format-Column-Width-20 Now center your name in cell D5 with the following clicks: Format-Cells-Alignment-Horizontal-Center Save this file to your H:\Hydro folder, let's call it "temp.xls" File-Save-H: folder-temp.xls Check and see what this file will look like if you print it: File-Print Preview While in Print Preview, click on "margins", you will now see the page margins as they will be printed, drag the inner top line down to the middle of the page. Drag the left line to a third of the distance across the page. Now