WORKED EXAMPLE · EXCEL ANALYSIS
Sizing the US solar O&M market from EIA data
A client wanted a quick top-down size of the US utility-scale solar O&M market. Her consultant uploaded the EIA’s state-by-state power capacity workbook and asked to filter the solar market and perform scenario analysis in the same excel
added a new “Solar O&M” sheet, filtered the data with SUMIFS formulae and performed the scenario analysis as instructed. A chart was drawn to show the analysis and the edited file (with the original data of 57,778 rows) was delivered. Upon further instruction by the consultant, a cheaper $17 scenario was considered, and a note on Texas overtaking California was written.
1The upload: EIA’s state capacity workbook, 57,778 rows
- 57,778EIA rows, left untouched
- $3.09bn2025 O&M market at $20/kW-yr
- 0.0MW difference from EIA’s US row
- 1workbook, edited again for request 2
Open the real files
The EIA file is linked at its official home. The two workbooks are the ones from the run, each with the full EIA sheet inside. Download them and open them in Excel.
Where the file comes from: U.S. Energy Information Administration, state electricity data, existing nameplate and net summer capacity by energy source, producer type and state, 1990-2025 (derived from Form EIA-860). A US government work in the public domain.
01 · THE STARTING FILE
One sheet, 57,778 rows, and two traps
Each row of the “Existing Capacity” sheet is one year, one state, one producer type and one fuel, from 1990 to 2025. Two things make a quick sum go wrong. Each state has a “Total Electric Power Industry” row that already adds up the producer types below it. And a “US” row adds up the states. Sum everything and the market comes out four times too big.
The file is a normal Excel workbook, so nothing had to be converted. It is too big to read in one go, so looked up the values it needed, then wrote formulas that work on the whole sheet.
30766.8| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Existing Nameplate and Net Summer Capacity by Energy Source, Producer Type and State, 1990-2025 | |||||||
| 2 | Year | State Code | Producer Type | Fuel Source | Generators | Facilities | Nameplate Capacity (Megawatts) | Summer Capacity (Megawatts) |
| 3 | 1990 | AK | Combined Heat and Power, Commercial Power | All Sources | 4 | 85.9 | 80.09 | |
| … | ||||||||
| 57431 | 2025 | TX | Commercial Power | Solar Thermal and Photovoltaic | 2 | 2 | 3.1 | 3 |
| … | ||||||||
| 57439 | 2025 | TX | Electric Generators, Electric Utilities | Solar Thermal and Photovoltaic | 5 | 5 | 172 | 169 |
| … | ||||||||
| 57450 | 2025 | TX | Electric Generators, Independent Power Producers | Solar Thermal and Photovoltaic | 222 | 215 | 29853.8 | 29877.9 |
| … | ||||||||
| 57457 | 2025 | TX | Industrial Power | Solar Thermal and Photovoltaic | 8 | 4 | 737.9 | 737.9 |
| … | ||||||||
| 57468 | 2025 | TX | Total Electric Power Industry | Solar Thermal and Photovoltaic | 237 | 226 | 30766.8 | 30787.8 |
| … | ||||||||
| 57540 | 2025 | US | Total Electric Power Industry | Solar Thermal and Photovoltaic | 8012 | 7333 | 154300.6 | 153350 |
02 · REQUEST 1
"Size the solar O&M market in this workbook"
What the consultant typed
Quick top-down sizing of the US utility-scale solar O&M market from the attached EIA file (sheet 'Existing Capacity'). Basis: rows with Fuel Source = 'Solar Thermal and Photovoltaic' and Producer Type = 'Total Electric Power Industry', column 'Nameplate Capacity (Megawatts)'. Use state rows only; the national total should be the sum of states and should agree with EIA's 'US' row. Add a sheet 'Solar O&M' to this workbook with: 1. An input cell for O&M cost = $20 per kW-year, which all the calculations reference 2. National solar MW for 2020 and 2025, the 2020-2025 CAGR, and the 2025 O&M market in $ 3. Top 10 states by 2025 MW: state, 2020 MW, 2025 MW, 2020-25 CAGR, share of US total, O&M $m 4. A bar chart of the top 10 states' O&M $m Leave the 'Existing Capacity' sheet as it is.
What did
- Looked up the distinct fuel sources, producer types and state codes first, so the filters use the file’s exact wording.
- Added a new “Solar O&M” sheet to the same workbook and left “Existing Capacity” as it was.
- Put the $20 per kW-year cost in one input cell, B3. Every dollar figure points at it.
- Wrote every MW figure as a live SUMIFS formula on the EIA sheet: solar, industry total only, states only. Click a green cell below to see one.
- Added two check lines: the state sum minus EIA’s own US row, for 2020 and 2025. Both come to 0.0.
- Built the top-10 states table with 2020 MW, 2025 MW, growth rate, share of the US and O&M $m, and a real Excel bar chart of the O&M column.
=SUMIFS('Existing Capacity'!G:G,'Existing Capacity'!A:A,2025,'Existing Capacity'!D:D,"Solar Thermal and Photovoltaic",'Existing Capacity'!C:C,"Total Electric Power Industry",'Existing Capacity'!B:B,"<>US")| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ||||||
| 2 | ||||||
| 3 | O&M cost ($/kW-year) | 20 | ||||
| 4 | ||||||
| 5 | 2020 national solar MW (state sum) | 48,338.5 | ||||
| 6 | 2025 national solar MW (state sum) | 154,300.6 | ||||
| 7 | 2020 EIA US-row solar MW | 48,338.5 | ||||
| 8 | 2025 EIA US-row solar MW | 154,300.6 | ||||
| 9 | Check 2020 (state sum - US row) | 0.0 | ||||
| 10 | Check 2025 (state sum - US row) | 0.0 | ||||
| 11 | 2020-25 CAGR (national) | 26.1% | ||||
| 12 | 2025 O&M market ($) | 3,086,012,000 | ||||
| 13 | ||||||
| 14 | ||||||
| 15 | State | 2020 MW | 2025 MW | 2020-25 CAGR | Share of 2025 US total | 2025 O&M $m |
| 16 | TX | 4,879.5 | 30,766.8 | 44.5% | 19.9% | 615.3 |
| 17 | CA | 14,473.4 | 24,832.2 | 11.4% | 16.1% | 496.6 |
| 18 | FL | 3,785.1 | 12,300.6 | 26.6% | 8.0% | 246.0 |
| 19 | NC | 5,039.1 | 7,050.9 | 6.9% | 4.6% | 141.0 |
| 20 | AZ | 2,411.9 | 6,868.8 | 23.3% | 4.5% | 137.4 |
| 21 | NV | 2,517.9 | 5,479.9 | 16.8% | 3.6% | 109.6 |
| 22 | GA | 2,207.7 | 5,414.4 | 19.7% | 3.5% | 108.3 |
| 23 | VA | 1,301.0 | 5,180.3 | 31.8% | 3.4% | 103.6 |
| 24 | IN | 279.0 | 4,593.4 | 75.1% | 3.0% | 91.9 |
| 25 | OH | 112.8 | 4,486.5 | 108.9% | 2.9% | 89.7 |
| 26 | ||||||
| 27 | National total is the state sum (State Code <>US) and agrees with the EIA US row. | |||||
Top 10 states by 2025 solar O&M ($m)
We checked it against the EIA data: 154,300.6 MW of solar in 2025 and 48,338.5 MW in 2020, a 26.1% yearly growth rate, a $3,086,012,000 market at $20, and the right ten states in the right order. We changed the input cell and every dollar figure followed. The EIA sheet still has all 57,780 rows, unchanged.
03 · REQUEST 2
"Add a $17 scenario and a note on Texas"
What the consultant typed
The partner thinks $20/kW-yr is rich. On the same 'Solar O&M' sheet: 1. Add a second scenario at $17/kW-yr next to the $20 case (its own input cell), for the national total and the top-10 O&M column 2. Show the scenario in the existing bar chart 3. Add a short note under the table: Texas overtook California in 2025 - by how many MW, and what was the gap in 2024? Keep the rest of the sheet as it is.
What did
- Opened the workbook it had just edited and worked in the same “Solar O&M” sheet.
- Added a second input cell, C3 = 17, next to the $20 one, and a $17 national total beside the $20 total.
- Added a $17 column to the top-10 table, pointing at the new input cell.
- Looked up the 2024 and 2025 figures and wrote the note under the table: Texas ahead by 5,934.6 MW in 2025, California ahead by 97.3 MW in 2024.
- Did not change any request-1 cell. We checked every one.
=B6*1000*$C$3| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | |||||||
| 2 | $20/kW-yr case | $17/kW-yr case | |||||
| 3 | O&M cost ($/kW-year) | 20 | 17 | ||||
| 4 | |||||||
| 5 | 2020 national solar MW (state sum) | 48,338.5 | |||||
| 6 | 2025 national solar MW (state sum) | 154,300.6 | |||||
| 7 | 2020 EIA US-row solar MW | 48,338.5 | |||||
| 8 | 2025 EIA US-row solar MW | 154,300.6 | |||||
| 9 | Check 2020 (state sum - US row) | 0.0 | |||||
| 10 | Check 2025 (state sum - US row) | 0.0 | |||||
| 11 | 2020-25 CAGR (national) | 26.1% | |||||
| 12 | 2025 O&M market ($) | 3,086,012,000 | 2623110200 | ||||
| 13 | |||||||
| 14 | |||||||
| 15 | State | 2020 MW | 2025 MW | 2020-25 CAGR | Share of 2025 US total | 2025 O&M $m | 2025 O&M $m ($17/kW-yr) |
| 16 | TX | 4,879.5 | 30,766.8 | 44.5% | 19.9% | 615.3 | 523.0356 |
| 17 | CA | 14,473.4 | 24,832.2 | 11.4% | 16.1% | 496.6 | 422.1474 |
| 18 | FL | 3,785.1 | 12,300.6 | 26.6% | 8.0% | 246.0 | 209.1102 |
| 19 | NC | 5,039.1 | 7,050.9 | 6.9% | 4.6% | 141.0 | 119.8653 |
| 20 | AZ | 2,411.9 | 6,868.8 | 23.3% | 4.5% | 137.4 | 116.7696 |
| 21 | NV | 2,517.9 | 5,479.9 | 16.8% | 3.6% | 109.6 | 93.1583 |
| 22 | GA | 2,207.7 | 5,414.4 | 19.7% | 3.5% | 108.3 | 92.0448 |
| 23 | VA | 1,301.0 | 5,180.3 | 31.8% | 3.4% | 103.6 | 88.0651 |
| 24 | IN | 279.0 | 4,593.4 | 75.1% | 3.0% | 91.9 | 78.0878 |
| 25 | OH | 112.8 | 4,486.5 | 108.9% | 2.9% | 89.7 | 76.2705 |
| 26 | Texas overtook California in 2025 by about 5,935 MW (TX 30,766.8 vs CA 24,832.2, gap 5,934.6 MW) while in 2024 California still led by about 97 MW (CA 22,562.9 vs TX 22,465.6, gap 97.3 MW). | ||||||
| 27 | National total is the state sum (State Code <>US) and agrees with the EIA US row. | ||||||
We checked it against the EIA data: $2,623,110,200 at $17 against $3,086,012,000 at $20, and Texas at $523.0m at $17. Texas 30,766.8 MW against California 24,832.2 MW in 2025; California 22,562.9 MW against Texas 22,465.6 MW in 2024. Both are right.
Analysis that stays in the client’s workbook
A chat answer gives you numbers to retype. works inside the spreadsheet you uploaded: it writes formulas on the real rows, checks its total against the source, and keeps the inputs live so the next “what if” is one cell away.
KEEP READING
Read another worked example
Bankers & Consultants
Turn a World Bank outlook chapter into a client steering deck
Bankers & Consultants
State RFP to a compliance matrix, then a response outline
Bankers & Consultants
A 23-slide development deck in French, then three fixes
See all 19 worked examples →All examples for Bankers & Consultants →