# Org-Wide Skill Gap Analysis Spreadsheet

> A build-it-yourself spreadsheet blueprint that rolls every team's individual skill matrix up into one org-level view, flagging which competencies are most under-resourced heading into next year's plan.

Free calculator/tool for sales leaders. Topic: [Skills, Competencies & Career Growth](https://www.peerlab.ai/library/topic/skills-competencies-career-growth/). Published by PeerLab in the [Sales Enablement Resource Library](https://www.peerlab.ai/library/). Source: https://www.peerlab.ai/library/org-wide-skill-gap-analysis-spreadsheet/

## How to use it

Build the 4 tabs in Sheets or Excel using the exact column headers and formulas above, populate Tab 1 from your certification or manager-assessment data, then read the Org Rollup tab's Severity Flag column sorted CRITICAL-first when building next year's hiring and enablement budget.

## What's inside

- 4-tab structure: Rep-Level Input, Team Rollup, Org Rollup, Gap Flags
- Exact column headers and data types for the Rep-Level Input tab
- Working formulas for team-level average, team-level coverage %, and variance
- Org-level rollup formula aggregating every team into one heat-map-ready table
- The Gap Flag formula and its 3-tier severity logic (Critical / Watch / Healthy)
- Conditional formatting rule set to turn the Org Rollup tab into a heat map
- Worked numeric example across 3 teams and 5 competencies so you can sanity-check your own build
- Planning-cycle usage notes: how to turn flagged gaps into next year's hiring and L&D budget lines

Build this as 4 tabs in Excel or Google Sheets. Formulas below are written in standard spreadsheet syntax, adjust ranges to your actual data size.

---

## Tab 1: Rep-Level Input

One row per rep per competency (long format, easiest to roll up and filter).

| Column | Field | Type | Example |
|---|---|---|---|
| A | Rep Name/ID | Text | J. Alvarez |
| B | Team | Text | Enterprise East |
| C | Manager | Text | R. Kim |
| D | Competency Code | Text | NEG-07 |
| E | Competency Name | Text | Multi-variable trade-off negotiation |
| F | Domain | Text | Negotiation |
| G | Current Level (1–4) | Number | 2 |
| H | Target Level (1–4) | Number | 3 |
| I | Gap (auto) | Formula | `=H2-G2` |
| J | Last Assessed Date | Date | 2026-06-01 |

This tab is fed either by manager assessment, certification data (see Certification Kit), or manager-completed skill matrices.

---

## Tab 2: Team Rollup

One row per Team × Competency combination, built with `AVERAGEIFS` / `COUNTIFS` against Tab 1.

| Column | Field | Formula (example) |
|---|---|---|
| A | Team |, |
| B | Competency Code |, |
| C | Avg Current Level | `=AVERAGEIFS('Rep-Level Input'!G:G,'Rep-Level Input'!B:B,A2,'Rep-Level Input'!D:D,B2)` |
| D | Avg Target Level | `=AVERAGEIFS('Rep-Level Input'!H:H,'Rep-Level Input'!B:B,A2,'Rep-Level Input'!D:D,B2)` |
| E | Avg Gap | `=D2-C2` |
| F | % of Team Below Target | `=COUNTIFS('Rep-Level Input'!B:B,A2,'Rep-Level Input'!D:D,B2,'Rep-Level Input'!I:I,\">0\")/COUNTIFS('Rep-Level Input'!B:B,A2,'Rep-Level Input'!D:D,B2)` |
| G | Headcount Assessed | `=COUNTIFS('Rep-Level Input'!B:B,A2,'Rep-Level Input'!D:D,B2)` |

---

## Tab 3: Org Rollup

One row per Competency, aggregated across all teams, this is the tab leadership actually reads.

| Column | Field | Formula (example) |
|---|---|---|
| A | Competency Code |: |
| B | Competency Name | `=VLOOKUP(A2,'Rep-Level Input'!D:E,2,FALSE)` |
| C | Domain | `=VLOOKUP(A2,'Rep-Level Input'!D:F,3,FALSE)` |
| D | Org Avg Gap | `=AVERAGEIF('Rep-Level Input'!D:D,A2,'Rep-Level Input'!I:I)` |
| E | % Org Below Target | `=COUNTIFS('Rep-Level Input'!D:D,A2,'Rep-Level Input'!I:I,\">0\")/COUNTIF('Rep-Level Input'!D:D,A2)` |
| F | Headcount Assessed | `=COUNTIF('Rep-Level Input'!D:D,A2)` |
| G | # Teams With Critical Gap | Count of Tab 2 rows for this code where Avg Gap ≥ 1.5 |
| H | Severity Flag | See formula below |

---

## Gap Flag formula (Severity, column H)

```
=IF(D2>=1.5,"CRITICAL",IF(D2>=0.75,"WATCH","HEALTHY"))
```

| Flag | Meaning | Suggested action |
|---|---|---|
| **CRITICAL** (avg gap ≥ 1.5 levels) | Org-wide capability hole | Budget for hiring against this competency AND a dedicated enablement sprint |
| **WATCH** (avg gap 0.75–1.49) | Uneven: strong in some teams, weak in others | Cross-team peer coaching before external hiring spend |
| **HEALTHY** (avg gap < 0.75) | On track | Maintain via existing coaching cadence |

---

## Conditional formatting rules (Org Rollup tab)

- Column D (Org Avg Gap): 3-color scale, green at 0, yellow at 1.0, red at 2.0
- Column H (Severity Flag): red fill for "CRITICAL", amber for "WATCH", green for "HEALTHY"
- Column G (# Teams With Critical Gap): data bar, scaled to total team count

---

## Worked example (3 teams, 1 competency shown)

| Team | Avg Current | Avg Target | Avg Gap |
|---|---|---|---|
| Enterprise East | 2.1 | 3.0 | 0.9 |
| Enterprise West | 1.6 | 3.0 | 1.4 |
| Mid-Market | 2.8 | 3.0 | 0.2 |
| **Org Avg (NEG-07)** | **2.17** | **3.0** | **0.83 → WATCH** |

Enterprise West alone would flag CRITICAL; the blended org average shows WATCH, this is exactly why the Team Rollup tab matters: it stops a strong team from masking a weak one in the org view.

---

## Using this in the planning cycle

1. Pull the Org Rollup tab sorted by Severity Flag, CRITICAL first
2. For every CRITICAL row, check column G: if concentrated in 1–2 teams, that's a coaching/staffing fix, not an org-wide L&D spend
3. For every CRITICAL row spread across most teams, that's a next-year hiring-profile and enablement-budget line item
4. Re-run the full sheet quarterly; track whether CRITICAL flags are shrinking, not just noting them once a year