Skip common site navigation and headers
United States Environmental Protection Agency
Data & Databases
Begin Hierarchical Links EPA Home > Water > Ground Water & Drinking Water > Data & Databases > About MS Excel PivotTables® End Hierarchical Links

 

About MS Excel PivotTables®

To use these tables you'll need MS Excel 97 or later. Detailed instructions are available for downloading, and summary instructions are included in each PivotTable®. No prior knowledge of spreadsheets is required. 

PivotTables® are multidimensional databases (MDBs) that provide online analytical processing, or OLAP. They enable you to quickly summarize, cross-tabulate, and analyze large amounts of data. You can pivot, or rotate, rows and columns to see different summaries of the source data, filter the data, and drill-down to the details in the underlying source data. 

For example, you can easily find the number of systems reporting a certain type of violation, in a certain year, for a certain state. To see the data in context, you can compare the results to those of other states, or to other states in that region. To look for trends you can build a graph to compare the results across several years. To investigate an unusually high or low result you can see if the problem stems from a certain contaminant or rule, within a certain system type, size category, or primary source. Using the PivotTables® described in these instructions, you could probably perform this analysis in less than 15 minutes.

Annual Trends data, including summary inventory, violations and GPRA

Detailed inventory data, by water system type, which includes data on individual water systems


Data included in Annual Trends data, including summary inventory, violations and GPRA PivotTables

Note: acronyms are spelled-out in the PivotTables

Inventory

The Inventory PivotTable® contains summary statistical information on the number of water systems and their population served. Only currently active systems are included (some water systems are seasonal and may be inactive at the time the database is frozen).
The following measures—

  • TotSys—total # of water systems and/or the
  • TotPop—total population served by water systems

—can be categorized by any combination of the following dimensions:

  • EPA Region
  • Geography Type
  • State
  • Primary County served
  • Water system type
  • Primary source of water
  • Three different population size categories
  • Owner type
Violation type

The Violation type PivotTable® contains reported violations by violation type for the last ten fiscal years. Violation types include MCL, MRDL, TT, M/R, Other (which include Public Notice), and Total (any violation).
The following measures—

  • Viols—# of violations reported and/or the
  • SysInViol—# of water systems reporting violations and/or the
  • PopInViol—population served by water systems reporting violations

—can be categorized by any combination of the following dimensions:

  • Fiscal year
  • Violation type
  • EPA Region
  • Geography Type
  • State
  • Water system type
  • Primary source of water
  • Three different population size categories
Rule

The Rule PivotTable® contains reported violations for certain contaminants and rules for the last ten fiscal years. These include TCR, SWTR, Interim and LT1 Enhanced SWTR, Stage 1 DBP, TTHM pre-Stage 1 and other VOCs, SOCs, Nitrates, Arsenic and other IOCs, Radionuclides, and LCR (these acronyms are spelled-out in the PivotTable). These are further organized by violation type: MCL, TT and M/R.
The following measures—

  • Viols—# of violations reported and/or the
  • SysInViol—# of water systems reporting violations and/or the
  • PopInViol—population served by water systems reporting violations

—can be categorized by any combination of the following dimensions:

  • Contaminant type or rule
  • Violation type
  • Fiscal year
  • EPA Region
  • Geography type
  • State
  • Water system type
  • Primary source of water
  • Three different population size categories
Contaminant

The Contaminant PivotTable contains reported violations for federally-regulated MCL and MRDL violations for the last nine fiscal years.
The following measure—# of violations reported—can be categorized by any combination of the following dimensions:

  • Contaminant type or rule
  • Violation type
  • Fiscal year
  • EPA Region
  • Geography type
  • State
  • Water system type
  • Primary source of water
  • Three different population size categories
GPRA

The GPRA PivotTable® contains data on systems reporting health-based violations (MCL, MRDL and TT violations) for fiscal years since 1993.
The following measures—

  • Viols—# of health-based violations reported and/or the
  • SysInViol—# of water systems reporting health-based violations and/or the
  • PopInViol—population served by water systems reporting health-based violations
  • TotSys—total # active water systems
  • TotPop—total population served by active water systems

—can be categorized by any combination of the following dimensions:

  • Fiscal year
  • EPA Region
  • Geography type
  • State
  • Water system type
  • Primary source
  • Three different population size categories

With the GPRA pivot tables you can double-click any data cell to get a list of the underlying water systems reporting health-based violations.

Top of page


The detailed inventory PivotTable® contains PWS-level information on active, current systems.

The Inventory PivotTable® contains detailed information on the number of water systems and their population served, including information on individual water systems. Only currently active systems are included (some water systems are seasonal and may be inactive at the time the database is frozen).
The following measures—

  • TotSys—total # of water systems and/or the
  • TotPop—total population served by water systems
  • SvcConns—total # service connections

—can be categorized by any combination of the following dimensions:

  • EPA Region
  • Geography Type
  • State
  • Primary County served
  • Water system type
  • Primary source of water
  • Three different population size categories
  • Owner type

Top of page

 

Safewater Home | About Our Office | Publications | Calendar | Links | Office of Water | En Español

 
Begin Site Footer

EPA Home | Privacy and Security Notice | Contact Us