Level 2 · Clean and rank the states in Excel

What the sheet holds

Ryan’s export has one row per state label exactly as it is stored in customers, for the eight states with the most revenue in 2025:

column	holds
A	the state label as typed, spaces and lowercase included
B	buyers: customers with a completed order in 2025
C, D	net revenue from completed orders in 2024 and in 2025

California alone appears as CA, ca, CA and CA. Sorted alphabetically, the labels with a leading space come first, which is often how you first notice them in a real export.

Step 1: clean, then show what changed

Cleaning a label takes two functions, nested:

=UPPER(TRIM(A2))

TRIM removes leading, trailing and doubled spaces; UPPER puts the letters in one case. A flag column then shows which rows the cleaning touched:

=IF(EXACT(A2,E2),"ok","fixed")

EXACT is the right test here because Excel’s = ignores case: ="ca"="CA" is TRUE. Two summary cells, COUNTIF of the flags and SUMIF of the 2025 revenue on the flagged rows, tell the reader how much was hiding under the messy labels.

Step 2: one row per state

With a clean label in column E, each measure is one SUMIF per state:

I6  =SUMIF($E$2:$E$26,H6,$C$2:$C$26)

The $ signs lock the data ranges, so the formula can be filled down to row 13 while H6 moves to H7, H8 and so on. Growth and revenue per buyer are then plain divisions on the totals.

Reading the result

state	revenue_2025	growth	revenue_per_buyer
CA	277,369.91	0.3%	1,459.84
TX	240,384.13	2.5%	1,602.56
NY	219,717.77	−14.3%	1,484.58

The three biggest states are close in size, but only California and Texas held their revenue. A state is still a large area: California’s revenue is spread across several metros, and part of every total is business accounts that would never visit a showroom.

Pitfalls

• A SUMIF on the raw labels comes out short without any error. Always total on the cleaned column.
• =A2=E2 treats ca and CA as equal. Use EXACT when case matters.
• Typing the totals instead of using formulas breaks the sheet the next time Ryan refreshes it.