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.
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
- Pull the Org Rollup tab sorted by Severity Flag, CRITICAL first
- 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
- For every CRITICAL row spread across most teams, that's a next-year hiring-profile and enablement-budget line item
- Re-run the full sheet quarterly; track whether CRITICAL flags are shrinking, not just noting them once a year
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.