Excel Spreadsheet Tools for Analyzing Groundwater Level Records and Displaying Information in ArcMap

Techniques and Methods 4-F1




When beginning hydrologic investigations, a first action is often to gather existing sources of well information, compile this information into a single dataset, and visualize this information in a geographic information system (GIS) environment. This report presents tools (macros) developed using Visual Basic for Applications (VBA) for Microsoft Excel 2007 to assist in these tasks. One tool combines multiple datasets into a single worksheet and formats the resulting data for use by the other tools. A second tool produces summary information about the dataset, such as a list of unique site identification numbers, the number of water-level observations for each, and a table of the number of sites with a listed number of water-level observations. A third tool creates subsets of the original dataset based on user-specified options and produces a worksheet with water-level information for each well in the subset, including the average and standard deviation of water-level observations and maximum decline and rise in water levels between any two observations, among other information. This water-level information worksheet can be imported directly into ESRI ArcMap as an 'XY Data' file, and each of the fields of summary well information can be used for custom display. A separate set of VBA tools distributed in an additional Excel workbook creates hydrograph charts of each of the wells in the data subset produced by the aforementioned tools and produces portable document format (PDF) versions of the hydrograph charts. These PDF hydrographs can be hyperlinked to well locations in ArcMap or other GIS applications.

Additional publication details

Publication type:
Publication Subtype:
USGS Numbered Series
Excel Spreadsheet Tools for Analyzing Groundwater Level Records and Displaying Information in ArcMap
Series title:
Techniques and Methods
Series number:
Version 1.0
Year Published:
U.S. Geological Survey
Contributing office(s):
Arizona Water Science Center
Report: vi, 59 p.; Spreadsheet Tools
Larger Work Type:
Larger Work Subtype:
USGS Numbered Series
Larger Work Title:
Chapter 1 of Section F, Groundwater, of Book 4, Hydrologic Analysis and Interpretation
Additional Online Files (Y/N):