In the promotional materials and during the Spilling the Truth campaign launch webinar, we advertised and delivered on what we believe to be never-before-seen data about the record number of oil spills that have been reported in Weld County. We provided our first example using the record number of oil spills reported for April 2025. Our second example also comes from the webinar materials; let’s recreate the chart that shows the breakdown of historical vs recent spills for the 12 months ending in February 2025. See if you can follow the steps below, and tell us at the bottom of the page if you agree that this is never-before-seen data.
Historical Spills Account for 86% of the 1,187 Total Reported Spills in the 12 Months Ending in February 2025
In this example, we’re taking a deeper dive into when oil spills are discovered and subsequently reported to the ECMC. Here’s what we showed during the webinar for the relative fractions of reported historical releases vs recent spills:
What would it take for a regular person to find these data on the ECMC website using their existing tools? It’s simply not possible using the Daily Activity Dashboard, meaning we can only analyze historical data that is only updated monthly (as of the writing of this post, the latest available data is through the first weeks of March, 2025). Instead, we’re going to have to download the data and do additional processing in Microsoft Excel. Here are the steps, assuming you’re proficient with Excel’s filtering, pivot table, and charting features:
- Navigate to the ECMC home page at https://ecmc.colorado.gov/
- From the Data, Maps, & Reports top menu, choose Downloadable Data & Documents.
- From the Environmental section, choose Spill Data (Updated Monthly).
- After reading the disclaimer, download the Spill Data zip file and extract it to your local hard drive.
- Open the
Spills.xlsxfile in Microsoft Excel. Your screen should look like the image below.
- First, let’s remove duplicates (this data set includes every initial and supplemental Form 19 spill report). Navigate to the Data ribbon bar, choose Remove Duplicates, and leave only the Tracking # column selected. Choose OK.
- Now, let’s add a new column G to the right of the Initial Report Date column F and call it Initial Report Month. “Fill down” with this formula:
=TEXT(F2,"yyyy-mm") - From the Data ribbon, re-enable filtering by selecting the main Filter icon and filter the Initial Report Month column to only show the last 12 months. From the filter dropdown menu, choose values 2024-04 through 2025-03.
- Filter the data for Weld County. Navigate to the county column, and from the dropdown filter menu, choose only WELD. The bottom left status bar should show you’ve got “1485 of 8643 records found.” This is the raw data we’ll use for our pivot table. At this point, I find it easiest to copy the data to a new Sheet called WeldSpills.
- Select the entire sheet range, and from the Insert ribbon, choose Pivot Table.
- For the pivot table fields, choose Initial Report Month for the Rows, Spill Type for the columns, and Count of Spill Type for the values. After adding the formulas to calculate the Historical and Recent spill percentages (and a bit of formatting), you should have a pivot table that looks similar to the image below.
12.) From the Insert ribbon, add a stacked bar chart to visualize the results by month. With a bit of styling, you should have something that looks similar to the image below, and contains the data presented in our webinar.
Please contact us if you’d like a copy of the Microsoft Excel workbook that generated this stacked bar chart.
Our Rating
For the relative complexity of having to download and deduplicate a large raw data set, the more advanced Microsoft Excel skills required, and the curious, but not surprising result, we’re giving this one 2 out of of 3 minds blown.
What do you think?
Take the poll below and let us know! With your help, we’ll continue to push for transparency and better tools at the ECMC to keep the public informed of the impacts of oil & gas extraction in our communities.
