USGS
Excel for Hydrology

Zip file (52 Mb)

Contains the "Excel for Hydrology" collection of spreadsheet applications. Original directory structure, where applications are grouped into directories by task, can be replicated in the upzipping procedure.

Spreadsheet Listing and Description

This listing, along with additional explanation on selected applications, are included in the zip file.

For best results, download the spreadsheets (see zip file above)
to your computer before using the spreadsheets.

Solutions for Technical Difficulties
Please check this file for problems that may occur when opening and using the spreadsheets.

Excel or any other spreadsheet is a handy tool for many hydrologic applications. Work and tools developed in a spreadsheet format can be exchanged easily because most colleagues already own the software. Spreadsheets provide a convenient environment for entering field notes, plotting data, mapping sites, and solving general minimization problems. Many spreadsheets have been developed for aquifer-test, discharge, kriging, mapping, model calibration, and water-quality analyses.

Excel is convenient because it is flexible. Data can be imported and exported by cutting-and-pasting, queries, or read directly from external files. For example, tab-delimited data can be copied from an ASCII file and pasted directly into a spreadsheet. Logical, mathematical, statistical, and text manipulation functions are native to Excel which facilitates quick analyses of simple problems. Scientific and optimization Add-Ins extend the utility of Excel for analyses. Fitting a four-parameter, leaky aquifer solution to about 400 measurements can be performed in less than a minute, but more complex problems are less suited to solution in Excel because of numerical inefficiency. Time-series, scatter plots, geophysical logs, cross-sections, and maps can be plotted easily with internal graph formats. Matrices can be viewed with internal contouring routines or by cell shading (fig. 1a). Complex, interactive graphs such as Piper plots (fig. 1b) and three-dimensional, vector diagrams also can be depicted with simple transformations. Text, graphs, and pages can be formatted in many ways so final results can be adapted to individual needs.

Many spreadsheets have been developed for solving specific hydrologic problems. Spreadsheets for analyzing single-well aquifer tests and slug tests are a documented collection http://water.usgs.gov/pubs/of/ofr02197/ . Multiaquifer test analyses spurred the creation of spreadsheets for screen digitizing; a leaky aquifer-test solution; a radial, MODFLOW preprocessor; drawdown estimation; and hydrograph viewer. Field sheets have been created for surface-water discharge computation and slope-conveyance estimation. Cross-sections, stage, and rating for a gaging station can be viewed and analyzed with another surface-water sheet.

Excel allows complex processes to be defined as functions that are easily ported to other applications. User-defined functions are accessed in the same manner as native Excel functions such as COUNT, MIN, MAX, SUM, etc. Earth tides, Latitude-Longitude to UTM conversions, Theis aquifer, leaky aquifer, and dual-porosity aquifer are a few of the more significant functions programmed within this collection. Mundane problems such as significant figures and time conversions also are addressed readily as user-defined functions. For example, odd time formats such as the text string "20011031 093023" are translated into the numerical value " 10/31/2001 9:30" by the function TIMEREAD.

The cited examples and others can be retrieved from the links above. Additional explanation and documentation was presented at the National Ground Water meeting, June 21-25, 2004 .

Zip File of presentations from the National Ground Water Meeting

Many good Excel and Excel VBA examples with clear and complete explanations are offered by:
Chip Pearson <http://www.cpearson.com/excel.htm>
John Walkenbach <http://www.j-walk.com/>
Stephen Bullen <http://www.oaltd.co.uk/>
John F. Raffensperger <http://www.mang.canterbury.ac.nz/people/jfraffen/spreadsheets/index.html>

For questions about the content of this page, or the spreadsheets, please contact Keith Halford
(775) 887-7613

 

Nevada District Home Page

USGS || WRD || BRD || GD || NMD

U.S. Department of the Interior || U.S. Geological Survey
Contact: Nevada District Web Team
Last Modified: April 12, 2005
Privacy Statement || Disclaimer