Excel | Source and method
Put crude and gas on one scale, and gas carries 64% of the energy.
Twenty-six years of US monthly oil and gas production, modelled and visualised entirely inside one Excel workbook. Power Query does the cleaning, a data model does the maths, and there is no VBA anywhere in it.
The question
The EIA publishes US crude oil production in thousands of barrels and natural gas production in millions of cubic feet, in two separate files, each with three header rows and dates stored as serial numbers. They describe the same industry in units that cannot be added together.
So the question is not only which states produce the most - it is what US hydrocarbon output actually looks like when crude and gas are measured on one scale. That turns out to change the answer, which is the subject of the technical note below.
There is a second question behind this build, aimed at a different audience: can plain Excel produce a dashboard that stands next to dedicated visualisation software? No add-ins, no macros, nothing that is not in the box.
How it is structured
One dashboard sheet, four zones, with the pivot and helper layers hidden behind it. Every visual responds to the same two slicers, so the whole sheet answers as one instrument rather than four charts that happen to share a page.
| Title bar and KPI row | Crude, gas, combined energy and the year-on-year move. The values are text boxes linked to cube formulas, so they follow the slicers rather than sitting still |
|---|---|
| Filter rail | Year and State. Two slicers driving every pivot, chart and KPI on the sheet at once |
| Trend and ranking | Monthly output stacked by fuel, and the top ten producing states. The trend axis relabels itself: years across the full span, months once a year is selected |
| Mix and long run | The crude-to-gas split on an energy basis, and the full-period area chart that shows the shale inflection |
The machinery underneath
- Power Query does all the cleaning. Four queries strip the header rows, drop the national and regional roll-up columns, unpivot the state columns to long form, extract the state name out of the description text, and filter to 2000 onward. No step is manual, so a refresh cannot silently skip one.
- A real data model, not sheet formulas. One fact table of 10,028 monthly rows joined to a generated date dimension, with the measures written in DAX.
- Cube formulas feed the KPI cards, which is what lets the headline numbers sit inside shapes and still react to a slicer.
- A dynamic-array helper layer feeds two of the charts - for reasons covered in the technical note.
What it shows
- Gas dominates on an energy basis. Across the full period the split is roughly 36% crude to 64% gas - the reverse of how US production is usually discussed.
- Texas is the crude story on its own, at about 42% of state-level crude output.
- Output roughly tripled from the mid-2000s. The long-run area chart shows the shale inflection as a change in slope, not a step.
- Totals for the period: 64,322 million barrels of crude and 663,114 billion cubic feet of gas, or 178,652 million barrels of oil equivalent combined.
Technical note
Two choices that could have gone the other way
1. Converting gas at 5.8 Mcf per barrel of oil equivalent
Barrels and cubic feet cannot be added. The alternative to converting was to show two separate totals and let the reader hold both in their head - which is what most published charts do, and it is why the gas share is not common knowledge.
Gas is converted at 5.8 thousand cubic feet per barrel of oil equivalent, the standard energy-content ratio, which makes crude and gas addable on a single axis. That single decision is what produces the headline above: on energy content, gas is roughly two thirds of US output. It is a conversion, not a price, so it says nothing about revenue - the two commodities are worth very different amounts per BOE, and this dashboard deliberately does not claim otherwise.
2. Computing the top ten in a helper layer instead of filtering the pivot
The obvious way to build a top-ten chart in Excel is a Top 10 value filter on the pivot table. It works, and then it fails silently: filtering a state and clearing it again drops the value filter altogether, and the chart quietly redraws with all 32 states. Nothing errors. The reader simply sees a different chart from the one that was built, and only if they happen to have touched a slicer first.
So the ranking is not filtered - it is computed. A hidden helper sheet rebuilds the top ten from the pivot with dynamic array formulas and feeds a regular chart, deliberately sorted ascending so the largest state lands at the top of a bar chart without flipping the axis. The same layer rebuilds the trend series so its date axis stays real, which is what lets it relabel from years to months when a year is selected.
The general rule this came from is the part worth keeping: never rely on a setting that a viewer's own interaction can silently remove. If a state must survive interaction, compute it.
How the numbers were checked
Model output was compared against externally known figures rather than against itself. For 2024: crude of 4,186 million barrels across the included states, which is about 11.4 million barrels a day and correct once federal offshore is excluded; Texas crude of 2,077 million barrels, about 5.7 million a day; and gas of 40,388 billion cubic feet. Anything that does not reconcile to a published daily rate is a modelling error, not a discovery.
Caveats, stated plainly
- Federal offshore production is excluded by design, because it belongs to no state. The state columns therefore do not sum to the EIA national total, and the KPIs here are computed from the included states only.
- 2026 is partial - January to April - so the last point of any annual view is not a full year.
- Gas is marketed production and crude is field production; they are the EIA's own published series, not derived estimates.
- The BOE conversion is energy content, not value.