the use of excel pivot tables & charts to process tri and rsei data in support of data quality,...
TRANSCRIPT
![Page 1: The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities Douglas](https://reader034.vdocuments.us/reader034/viewer/2022050714/56649d215503460f949f6005/html5/thumbnails/1.jpg)
The Use of Excel Pivot Tables & Charts to Process TRI and RSEI
Data in Support of Data Quality, Enforcement, and Pollution
Prevention Activities
Douglas M. Chatham, SEE EnrolleeDouglas M. Chatham, SEE EnrolleeAir Toxics Assessment & Air Toxics Assessment & Implementation SectionImplementation SectionU.S. EPA Region 4U.S. EPA Region 4Atlanta, GeorgiaAtlanta, Georgia
![Page 2: The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities Douglas](https://reader034.vdocuments.us/reader034/viewer/2022050714/56649d215503460f949f6005/html5/thumbnails/2.jpg)
CONTACT INFORMATIONCONTACT INFORMATION
Ezequiel Velez, EPA Region 4 Toxics Release Inventory (TRI) Coordinator
Tel. (404) 562-9191 [email protected]
Douglas Chatham, SEE EnrolleeEPA Region 4
TRI Program Assistant Tel. (404) 562-9113
U.S. EPA Region 4 61 Forsyth Street, S.W.
Atlanta, GA 30303
![Page 3: The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities Douglas](https://reader034.vdocuments.us/reader034/viewer/2022050714/56649d215503460f949f6005/html5/thumbnails/3.jpg)
Producing Charts from TRI-Explorer DataProducing Charts from TRI-Explorer Data
Export Data from TRI-Explorer to ExcelALsc06-releases.xls
1. Export TRI-Explorer Data to CSV files.
ST88-06-releases.csv, ST98-06-releases.csv, STch06-releases.csv, STsc06-releases.csv, and STfa06-releases.csv
2. Enter formulas for Total Air, Water, Land, and Underground Injection.
3. Copy the Results and Paste (to the right) the Values into a blank area. Copy the ID column and the Total Air and paste into another blank area.
4. Sort by Total TRI Releases or by Total Air in Descending order.
5. Insert bar charts from these sorted results.
![Page 4: The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities Douglas](https://reader034.vdocuments.us/reader034/viewer/2022050714/56649d215503460f949f6005/html5/thumbnails/4.jpg)
Producing Charts from TRI-Explorer DataProducing Charts from TRI-Explorer Data
Export Data from TRI-Explorer to Excel
• Copy Data from the State File to the Region 4 File.
R4sc06Temp.xls
• The Chemical and Industry Data must be aligned across the state entries to be able to sum them.
![Page 5: The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities Douglas](https://reader034.vdocuments.us/reader034/viewer/2022050714/56649d215503460f949f6005/html5/thumbnails/5.jpg)
Producing Charts from TRI-Explorer DataProducing Charts from TRI-Explorer Data
Export Data from TRI-Explorer to Excel
Let’s Do This a Little Faster.
TRIMacro.xls
![Page 6: The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities Douglas](https://reader034.vdocuments.us/reader034/viewer/2022050714/56649d215503460f949f6005/html5/thumbnails/6.jpg)
Producing Charts from RSEIProducing Charts from RSEI Export Data from RSEI to Access
1. Run RSEI with No Elements Selected
2. Set up a Table with Chemical.CAS Number, Facility.Facility ID, Submission.Year, and Release.Media Code Fields selected.
3. Start a new Access database with an appropriate name (US-96-05.mdb).
4. Import the Paradox tables Chemical.db, Facility.db, Media.db and the table produced in step 2 into the new database.
5. Establish Relationship Links between the Tables (CAS Number, Facility ID, and Media).
![Page 7: The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities Douglas](https://reader034.vdocuments.us/reader034/viewer/2022050714/56649d215503460f949f6005/html5/thumbnails/7.jpg)
![Page 8: The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities Douglas](https://reader034.vdocuments.us/reader034/viewer/2022050714/56649d215503460f949f6005/html5/thumbnails/8.jpg)
Producing Charts from RSEIProducing Charts from RSEI Set Up Access Queries and Export to Excel.
• The Fields I include are:» CAS Number Chemical
» Year
» Facility ID Facility Name
» FIPS County
» State ZipCode
» Region
» SIC Code 2Digit SICName
» Media MediaType
» TRI Pounds Hazard Full Model
![Page 9: The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities Douglas](https://reader034.vdocuments.us/reader034/viewer/2022050714/56649d215503460f949f6005/html5/thumbnails/9.jpg)
![Page 10: The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities Douglas](https://reader034.vdocuments.us/reader034/viewer/2022050714/56649d215503460f949f6005/html5/thumbnails/10.jpg)
Producing Charts from RSEIProducing Charts from RSEI
Export Query to Excel
• Close the Query in Access
• Select Export from the File Menu
• Specify How and Where the File Should be Saved (As Excel 97- 2003)
• Close Access and Open the Excel File
• The Next Slide shows a screen shot of the resulting Excel sheet.
![Page 11: The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities Douglas](https://reader034.vdocuments.us/reader034/viewer/2022050714/56649d215503460f949f6005/html5/thumbnails/11.jpg)
![Page 12: The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities Douglas](https://reader034.vdocuments.us/reader034/viewer/2022050714/56649d215503460f949f6005/html5/thumbnails/12.jpg)
Producing Charts from RSEIProducing Charts from RSEI
Set Up Excel Pivot Tables and Charts
• Open the Excel Spread-Sheet.
• qryAL98-05Start.xls
• Select Cell A1
• Select “Data/Pivot Table and Pivot Chart Report”
• Select “Pivot Chart Report”
• Move Fields from the Field List to the Appropriate Areas of the Pivot Table.
![Page 13: The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities Douglas](https://reader034.vdocuments.us/reader034/viewer/2022050714/56649d215503460f949f6005/html5/thumbnails/13.jpg)
Producing Charts from RSEIProducing Charts from RSEI Save Pivot Table Data & Create Excel
Charts
• Insert Pounds and Risk Data Sheets
• Copy Pivot Table Data and Paste to the Appropriate Data Sheet
• Create a Bar Chart from the Data Sheet
• Format the Chart
• qryAL_98-05Pivot.xls
![Page 14: The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities Douglas](https://reader034.vdocuments.us/reader034/viewer/2022050714/56649d215503460f949f6005/html5/thumbnails/14.jpg)
Producing Charts from RSEIProducing Charts from RSEI
Use Visual Basic Program to Generate Charts from the Data Sheets• Let’s Generate some charts Using Visual
Basic
• RSEIMacro.xls
What’s Next??
• I Plan to Program the Transfer of Data from Pivot Tables to the Data Tables
• http://www.epa.gov/region4/air/airtoxic/RSEI_Charts.htm