Now, even law professors can do serious data analysis

The meanings were not pulled from a connector or web search — they came from my own knowledge. I recognized the filename (SCDB_..._justiceCentered_Vote) and the column names (caseId, naturalCourt, lcDispositionDirection, majOpinWriter, justiceName, etc.) as the Supreme Court Database (SCDB) — a well-known, publicly documented political-science dataset of U.S. Supreme Court decisions. Its codebook is established enough that I know the field definitions directly.

What I built

Two sheets, all in Excel formulas:

• Dissents by Term — rows = the 40 justices (sorted by career total), columns = terms 1946–2024. Each cell is the number of dissenting opinions that justice authored that term (zeros shown as 0). On the right: a Total column plus three type columns — Regular (vote 2), Cert‑denial (vote 6), Jurisdictional (vote 7).
• Dissent Calc — the helper layer that flags each row.

Definition: an authored dissent = the justice wrote or co‑authored an opinion (opinion ∈ {2,3}) and cast a dissenting vote (vote ∈ {2,6,7}).

Leaderboard (career authored dissents): Stevens 727 · Douglas 666 · Brennan 499 · Harlan II 440 · Marshall 394 · Black 382. Total across the Court: 7,400.

The one subtlety that mattered

The file isn't one row per justice per case — it repeats each justice across a case's consolidated dockets/issues (~13 rows per case). Since vote/opinion are case‑level, naively counting rows overcounted by ~64% (12,151 vs the true 7,400). So I deduped to one record per justice‑case before counting.

Key Excel code

Helper (Dissent Calc) — pick one record per case, then flag authored dissents:

BlockFlag (col H):
=--('Data'!C2:C125464 = XLOOKUP('Data'!A2:A125464,'Data'!A2:A125464,'Data'!C2:C125464,,0,2))

XLOOKUP(…,,0,2) does a binary search on the sorted caseId column, returning one consistent caseIssuesId per case — so BlockFlag keeps exactly one docket/issue block per case (one row per justice‑case).

Total dissent flag (col C):
= H2# * (('Data'!BE2:BE125464=2)+('Data'!BE2:BE125464=3))          ' authored
       * (('Data'!BD2:BD125464=2)+('Data'!BD2:BD125464=6)+('Data'!BD2:BD125464=7))  ' any dissent
' columns D/E/F swap the last factor for (BD=2), (BD=6), (BD=7) to split by type

Matrix (Dissents by Term):

Term headers (B1):   =SEQUENCE(1,79,1946,1)
Justice list (A2):   =LET(J, UNIQUE(FILTER('Dissent Calc'!A2:A125464,'Dissent Calc'!A2:A125464<>"")),
                          tot, MAP(J,LAMBDA(j,SUMIFS('Dissent Calc'!$C$2:$C$125464,'Dissent Calc'!$A$2:$A$125464,j))),
                          SORTBY(J,tot,-1))
Cell (B2):           =SUMIFS('Dissent Calc'!$C$2:$C$125464,
                             'Dissent Calc'!$A$2:$A$125464, $A2,
                             'Dissent Calc'!$B$2:$B$125464, B$1)
Type totals (CD2):   =SUMIFS('Dissent Calc'!$D$2:$D$125464,'Dissent Calc'!$A$2:$A$125464,$A2)

SUMIFS returns 0 for justice‑terms with no authored dissents, which is why every empty cell shows a real zero rather than a blank.

Done. New sheet Dissents 1991+ — the same dissent matrix filtered to Thomas's first term (1991) onward and to the 19 justices who authored at least one dissent in that window.

• Columns: terms 1991–2024, plus Total + the three type columns.
• Rows: 19 justices, sorted by their 1991‑onward dissent total.

Top dissenters since 1991: Stevens 319 · then Thomas, Scalia, Ginsburg, Breyer, Alito (the matrix sorts them top‑to‑bottom). Justices who served only briefly into this window (White, Blackmun) appear near the bottom with small counts but still qualify.

The filter is driven by a single condition added to the justice list — keep a justice only if their dissents from 1991 on are positive:

=LET( J,   UNIQUE(FILTER('Dissent Calc'!A2:A125464,'Dissent Calc'!A2:A125464<>"")),
      tot, MAP(J,LAMBDA(j, SUMIFS('Dissent Calc'!$C$2:$C$125464,
                                  'Dissent Calc'!$A$2:$A$125464, j,
                                  'Dissent Calc'!$B$2:$B$125464, ">=1991"))),
      SORTBY( FILTER(J, tot>0), FILTER(tot, tot>0), -1) )

The ">=1991" criterion in SUMIFS does both jobs — it restricts the term window and (via tot>0) drops any justice with no dissents in that span. The grid columns start at 1991 (=SEQUENCE(1,34,1991,1)), and the original full Dissents by Term sheet is untouched.

Here's the proposal, built and ready on the Pivot sheet.

The pivot: Issue Area × Decision Direction

A genuinely classic SCDB cut — what the Court decides, and which way it leans:

• Rows: Issue Area (Criminal Procedure, Civil Rights, Economic Activity, …)
• Columns: Decision Direction (Conservative / Liberal / Unspecifiable)
• Values: Count of cases

What it shows (1946–2024, 9,341 cases):

Overall the Court tilts slightly liberal — 4,660 vs 4,472 conservative — but the lean flips by topic: criminal-procedure cases skew conservative, while economic and civil-rights cases skew liberal. That contrast is the whole story of the dataset in one table.

Why it needed a setup step

A PivotTable runs on whatever range you give it, and the Data sheet is vote-level (each case repeats ~13× across justices and dockets/issues). Pivoting it directly would have reported ~125k "cases." So I built a one-row-per-case source first:

• Case Table — deduped to 9,341 cases with =FILTER('Data'!…, A2:A<>A1:A) (keep the first row of each case), then XLOOKUP to turn the numeric issueArea and decisionDirection codes into readable labels.
• Pivot — a native PivotTable on Case Table!A1:D9342, Count of caseId.

The Grand Total reads 9,341, confirming it's counting cases correctly. Because it's a real PivotTable, you can now drag fields around — swap Decision Direction for Term or Chief Justice, or drop Issue Area into the filter — and it'll recompute instantly. Want me to add a second pivot (say, cases by Chief Justice era), or a pivot chart off this one?

It's not cell formulas — the agreement counts require a self-join (matching every justice against every other justice within the same case across 125k rows), which Excel formulas handle poorly at this scale. So I read the data with Office.js and computed the pairwise tallies in JavaScript, then wrote the summary table back. The core logic:

// 1. Read the 4 needed columns for all 125,463 rows, in chunks of 25k
//    A=caseId, K=term, BB=justice, BC=justiceName, BG=majority (1=dissent, 2=majority)

// 2. Group votes by case, deduping to one vote per justice per case
const cases = new Map();              // caseId -> {term, seen:Set, parts:[{id,maj}]}
for (let i = 0; i < n; i++) {
  const m = maj[i];
  if (m !== 1 && m !== 2) continue;   // skip non-participation
  let c = cases.get(caseId[i]) ?? {term: term[i], seen: new Set(), parts: []};
  if (c.seen.has(jid[i])) continue;   // dedupe consolidated dockets/issues
  c.seen.add(jid[i]);
  c.parts.push({id: jid[i], maj: m});
  cases.set(caseId[i], c);
}

// 3. For every case, compare each pair of participating justices
const agg = new Map();                // "term|justiceA|justiceB" -> {agree, diff}
for (const c of cases.values()) {
  const p = c.parts;
  for (let a = 0; a < p.length; a++)
    for (let b = a + 1; b < p.length; b++) {
      let [A, B] = p[a].id < p[b].id ? [p[a].id, p[b].id] : [p[b].id, p[a].id];
      const key = c.term + "|" + A + "|" + B;
      const r = agg.get(key) ?? {term: c.term, A, B, agree: 0, diff: 0};
      if (p[a].maj === p[b].maj) r.agree++; else r.diff++;   // same side vs opposite
      agg.set(key, r);
    }
}

// 4. Sort by term, then justice; write A:E as values, F & G as formulas (=D+E, =D/F)

A caveat worth knowing: for the rare cases with split votes (a justice in the majority on one issue but dissenting on another under the same caseId), I take the justice's first record — standard for case-level agreement, but it's an approximation. Want me to add a sortable "most/least agreeable pairs" view, or switch the agreement definition to ideological-direction agreement (direction field) instead of majority/dissent side?

Done. New sheet Pair Drift (Significant) — 41 justice pairs (of 154 with ≥8 shared terms) whose agreement trend over time is significant at p<0.05, sorted by absolute drift.

Columns: Rank · Justice A/B · Terms · Shared Cases · First/Last Term · Agree% First/Last · Slope (pp/term) · Drift (pp, fitted) · R² · p-value · Sig after BH 5%. Drift is red for diverging pairs, green for converging.

Biggest movers (all BH-significant):

• Brennan–White –29.3 pp (1961→89): 90% → 58% agreement as White drifted right while Brennan stayed left
• Blackmun–Rehnquist –26.1 pp and Burger–Blackmun –25.1 pp: Blackmun's well-documented leftward migration away from the Nixon bloc
• Douglas–White –23.6 pp
• Brennan–Blackmun +23.5 pp and Marshall–Blackmun +22.0 pp: the flip side — Blackmun converging with the liberals
• Scalia–Souter –23.0 pp, Kennedy–Souter –18.5 pp, Souter–Thomas –18.2 pp: Souter's leftward shift after 1990

The Blackmun and Souter stories dominate, in both directions — exactly what you'd expect from two justices who moved a lot relative to stationary colleagues.

How it was computed

Per pair, a weighted least-squares regression of Agreement % on Term, weighting each term's point by Cases Together that term (terms with more shared cases are more reliable estimates — and since the variance of a proportion ∝ 1/n, case-count is a natural inverse-variance weight). The math, run in JavaScript over the Justice Pairs by Term data:

W   = Σ wᵢ                          (wᵢ = cases shared in term i)
x̄   = Σwᵢxᵢ / W   ;  ȳ = Σwᵢyᵢ / W   (xᵢ = term, yᵢ = agreement %)
Sxx = Σwᵢ(xᵢ−x̄)²  ;  Sxy = Σwᵢ(xᵢ−x̄)(yᵢ−ȳ)
slope b   = Sxy / Sxx               → drift per term
Drift     = b × (lastTerm − firstTerm)
SSE = Σwᵢ(yᵢ − a − b·xᵢ)²  ;  σ² = SSE/(n−2)
SE(b) = √(σ²/Sxx)  ;  t = b / SE(b) ,  df = n−2

The two-sided p-value = 2·(1 − T_cdf(|t|, df)) and the Benjamini-Hochberg false-discovery-rate adjustment (last column) were computed with scipy.stats — equivalent Excel formulas would be =T.DIST.2T(ABS(t), df) for the p-value. I used code rather than cell formulas because the agreement counts require a self-join of every justice against every other within each case across 125k rows, which Excel formulas handle poorly at that scale.

Two caveats: (1) the p-value treats weights as known reliabilities (standard analytic-weight WLS); (2) with 154 simultaneous tests, ~8 false positives are expected at p<0.05 — hence the BH column, where 25 of the 41 survive FDR control. Lean on those 25 for any firm claims.

Want me to add a scatter+trendline chart for any specific pair, or rank instead by slope magnitude rather than total drift?

Done. New sheet Justice Movement Ranking — all 31 justices ranked by how much their voting relationships shifted, using all 154 pairs (≥8 shared terms).

Columns: Rank · Justice · Pairs · Total Shared Cases · Case-Weighted Avg |Drift| (pp, with data bar) · Simple Avg |Drift| (pp).

Who moved most:

Most stable: Breyer (4.0), Jackson (3.9), O'Connor (4.4), Harlan II (4.4), Ginsburg (4.6).

Blackmun and Souter at the top is the expected result — both are the textbook cases of justices who drifted left over a career. White's #3 reflects his rightward move relative to the liberal bloc he started with. Marshall and Brennan rank high largely because their partners moved around them (their own positions were stable, but the relationships still changed).

Method: for each justice, Σ(sharedCases × |Drift|) / Σ(sharedCases) across their qualifying pairs, where |Drift| is the absolute fitted change in agreement from first to last shared term. The full formula is in the note on each sheet. One caveat flagged on the sheet: Gorsuch (5 pairs), Jackson (5), and Minton (6) rest on thin samples.

Want a horizontal bar chart of this ranking, or a per-justice breakdown showing which specific relationships drove each justice's score?

Straight answer: the heavy computation was not done with Excel formulas — it ran in JavaScript (Office.js), with one step in Python. Here's exactly what ran where.

So the numbers on the ranking sheet are computed values, not formulas. The exact JavaScript that produced this latest sheet:

// 1. Read Term, Justice A/B, Cases Together, Agreement % from "Justice Pairs by Term"
const pairs = new Map();                         // "A|B" -> [{x:term, y:agree%, w:cases}]
for (const row of values) {
  const [term, jA, jB, , , cases, agr] = row;
  if (typeof term !== "number" || typeof agr !== "number") continue;
  (pairs.get(jA+"|"+jB) ?? pairs.set(jA+"|"+jB, []).get(jA+"|"+jB))
    .push({ x: term, y: agr, w: cases > 0 ? cases : 1 });
}
// 2. Per pair (>=8 terms): weighted least-squares slope, then |drift| = |slope x span|
const perPair = [];
for (const [key, pts] of pairs) {
  if (pts.length < 8) continue;
  let W=0, Sx=0, Sy=0;
  for (const p of pts) { W+=p.w; Sx+=p.w*p.x; Sy+=p.w*p.y; }
  const xb=Sx/W, yb=Sy/W;
  let Sxx=0, Sxy=0;
  for (const p of pts) { const dx=p.x-xb; Sxx+=p.w*dx*dx; Sxy+=p.w*dx*(p.y-yb); }
  if (Sxx <= 0) continue;
  const b = Sxy / Sxx;                            // slope (agreement fraction per term)
  const xs = pts.map(p=>p.x), span = Math.max(...xs) - Math.min(...xs);
  const [jA, jB] = key.split("|");
  perPair.push({ jA, jB, drift: Math.abs(b*span), cases: W });
}
// 3. Aggregate per justice: case-weighted mean of |drift|
const J = new Map();
for (const pp of perPair)
  for (const name of [pp.jA, pp.jB]) {
    const g = J.get(name) ?? J.set(name, {n:0, cases:0, wsum:0, usum:0}).get(name);
    g.n++; g.cases += pp.cases;
    g.wsum += pp.cases * pp.drift;                // numerator:  Σ cases·|drift|
    g.usum += pp.drift;
  }
// 4. Score = Σ(cases·|drift|) / Σ(cases);  sort descending
const out = [...J].map(([name,g]) =>
  [name, g.n, Math.round(g.cases), +(g.wsum/g.cases*100).toFixed(2), +(g.usum/g.n*100).toFixed(2)]
).sort((a,b)=>b[3]-a[3]);

The case-weighting is the g.wsum/g.cases line — step 4's Σ(cases × |drift|) / Σ(cases).

If you'd rather have this fully native in Excel so every number traces to a formula, I can drop all 154 pairs (with their |drift| and shared-case weight) onto a helper sheet and compute each justice's score with SUMPRODUCT — e.g. for a justice in cell $H2:

=( SUMPRODUCT((Pairs[A]=$H2)*Pairs[Cases]*Pairs[AbsDrift])
 + SUMPRODUCT((Pairs[B]=$H2)*Pairs[Cases]*Pairs[AbsDrift]) )
 /( SUMPRODUCT((Pairs[A]=$H2)*Pairs[Cases])
 + SUMPRODUCT((Pairs[B]=$H2)*Pairs[Cases]) )

Want me to build that native version?

Done. New sheet CoA Reversal with reversal rate for each federal court of appeals × each term (1946–2024), all in Excel formulas.

Layout:

• Rate grid (B6:N84) — terms down the rows, the 13 circuits across columns (1st–11th, D.C., Federal), color-scaled. Cells with fewer than the threshold in B2 (default 3) cases that term are left blank.
• All-terms summary (row 85–86) — each circuit's overall reversal rate and total cases.
• Case-count grid (rows 90–169) — N per cell so you can see which rates are solid.

Overall reversal rate by circuit (1946–2024):

The Ninth Circuit is the most-reversed (73.9%, and by far the most cases), with the D.C. Circuit second — both well-known patterns. The Second Circuit is the least reversed (57.2%).

The formula (rate cell, term in $A6, circuit code in B$4):

=LET( c, SUMPRODUCT(('Case Calc'!$B$2#=$A6)*('Case Calc'!$H$2#=B$4)*'Case Calc'!$G$2#),
      r, SUMPRODUCT(('Case Calc'!$B$2#=$A6)*('Case Calc'!$H$2#=B$4)*'Case Calc'!$F$2#),
      IF(c < $B$2, "", r/c) )

I added one field to Case Calc — H2 =FILTER('Data'!AB2:AB125464, mask) — to carry each case's source court (caseSource) alongside the existing term and reversal/denominator flags.

Two notes: (1) Per-term-per-circuit counts are small, so many single-term rates are noisy — lean on the counts grid and the all-terms summary. (2) I couldn't apply frozen header panes because the other open workbook (admissions.xlsx) kept grabbing focus; the data and formatting are all in place. Want me to add a trend chart of the 9th vs 2nd Circuit reversal rate over time?

Done — all in Excel formulas, no JavaScript/Python in the computation. Three new sheets:

• Reversal by Decade — the result: reversal rate for each petitioner type × decade, with a "Most Favored" column.
• Pet Buckets — the 311 SCDB petitioner codes mapped to 6 buckets (editable lookup table).
• Case Calc — case-level helper (deduped from the 125k vote rows to 9,341 cases).

Definitions: Reversal = the Court reversed or vacated the decision below (disposition 3, 4, 5, 8); rate = reversals ÷ affirm-or-reverse cases (2, 3, 4, 5, 8). Reversal favors the petitioner (who sought review), so the highest-rate bucket each decade is the one the Court "favored most." Cells with fewer than 15 cases (threshold in B3, adjustable) are blanked.

Who the Court favored most, by decade:

The clear story: the federal government was the Court's most-reversed-for petitioner for five straight decades (when the US asks the Court to reverse, it usually does), then state & local governments overtook it in the 2000s. Business and individual petitioners run lower (~50–75%). Other/Foreign is mostly suppressed for thin samples.

The key formulas (all Excel):

Case-level dedup — the data is sorted by case, so a row starts a new case when its caseId differs from the row above:

Case Calc!A2:  =FILTER('Data'!R2:R125464, 'Data'!A2:A125464 <> 'Data'!A1:A125463)   ' petitioner, 1 row/case
        D2:  =FLOOR(B2#, 10)                                                       ' decade
        E2:  =XLOOKUP(A2#, 'Pet Buckets'!$A$2:$A$312, 'Pet Buckets'!$C$2:$C$312, "Other/Foreign")
        F2:  =IF((C2#=3)+(C2#=4)+(C2#=5)+(C2#=8)>0, 1, 0)        ' reversal flag
        G2:  =IF((C2#=2)+(C2#=3)+(C2#=4)+(C2#=5)+(C2#=8)>0, 1, 0) ' denominator flag

Rate cell (decade in $A6, bucket in B$5), with the case threshold:

=LET( c, SUMPRODUCT(('Case Calc'!$D$2#=$A6)*('Case Calc'!$E$2#=B$5)*'Case Calc'!$G$2#),
      r, SUMPRODUCT(('Case Calc'!$D$2#=$A6)*('Case Calc'!$E$2#=B$5)*'Case Calc'!$F$2#),
      IF(c < $B$3, "", r/c) )

Most-favored bucket per decade:

=IFERROR(INDEX($B$5:$G$5, MATCH(MAX(B6:G6), B6:G6, 0)), "")

One caveat: the 6-bucket assignment of 311 codes (Federal / State-Local / Business / Individual-Employee / Criminal / Other-Foreign) is a judgment call — I classified each official label, and the full mapping is on the Pet Buckets sheet so you can re-bucket any code and everything recalculates.

Want me to add a chart of the favored-type trend, or split out a "Criminal defendant" vs "Business" comparison line over time?