Content Gap Analysis Google Sheets Template

Free editable Content Gap Analysis Google Sheets Template. Copy, personalize, or download the Word .docx template from UNmiss.

Use this template to turn a pile of keyword exports into a working gap analysis spreadsheet. Each variant is one tab of the workbook, with the exact columns to add and the formulas that do the matching and scoring for you. Set up the tabs left to right, paste your own exports into the input tabs, then let the formulas surface the keywords worth writing about first. Everything works the same in Google Sheets and Excel; only a couple of function names differ, noted where it matters.

6 ready-to-use variants

Setup & Instructions Tab

Start here to name your competitors, set your scoring thresholds, and define what counts as a gap.

Goal: configure the workbook once so every other tab reads from a single set of inputs instead of hard-coded values.

Cells to fill in

  • Your domain: [yourdomain.com]
  • Competitor 1–3 domains: [competitor-a.com], [competitor-b.com], [competitor-c.com]
  • Ranking threshold (you "cover" a keyword if you rank in top): [20]
  • Minimum monthly volume to keep: [50]
  • Max keyword difficulty you'll target: [40]

Named ranges to create

  1. Select the threshold cell and name it rank_threshold so formulas can reference it by name.
  2. Do the same for min_volume and max_kd.
  3. Add a one-line note reminding your team which SEO tool the exports came from, so refreshes stay consistent.

Your Pages Tab

Import every keyword your own site already ranks for so the workbook knows what you cover.

Goal: hold a clean export of your own ranking keywords that the gap tab can look up against.

Columns (A–E)

  • A – Keyword: [paste keyword export]
  • B – Your position: [current rank]
  • C – Ranking URL: [your URL]
  • D – Volume: [monthly searches]
  • E – Covered?: formula below

Formula for column E

  1. In E2 enter =IF(B2<=rank_threshold,"Covered","Weak") and fill down.
  2. This flags a keyword as covered only when your position beats the threshold from the Setup tab.
  3. Freeze row 1 and sort by volume descending so your strongest terms sit at the top.

Competitor URLs Tab

Stack your competitors' ranking keywords into one list the gap formula can scan.

Goal: combine each competitor's keyword export into a single tab, tagged by source, so nothing is analyzed in isolation.

Columns (A–F)

  • A – Keyword: [competitor keyword]
  • B – Competitor domain: [which competitor]
  • C – Their position: [rank]
  • D – Their ranking URL: [competitor URL]
  • E – Volume: [monthly searches]
  • F – Keyword difficulty: [KD]

How to build it

  1. Paste each competitor's export underneath the last, keeping column B set to the right domain.
  2. Add =COUNTIF(B:B,[competitor-a.com]) above the sheet to sanity-check row counts per competitor.
  3. Remove obvious brand terms and navigational queries before scoring; they are rarely real gaps.

Gap Matching Formula Tab

Cross-reference the two lists so the sheet marks which competitor keywords you don't cover.

Goal: flag every competitor keyword you either don't rank for or rank weakly for. This is the core of the analysis.

Columns (A–E)

  • A – Keyword: pull unique keywords from the Competitor URLs tab.
  • B – Do you rank?: =IFERROR(VLOOKUP(A2,'Your Pages'!A:B,2,FALSE),"No")
  • C – Gap type: =IF(B2="No","Missing",IF(B2>rank_threshold,"Weak","Covered"))
  • D – Volume: =IFERROR(VLOOKUP(A2,'Competitor URLs'!A:E,5,FALSE),0)
  • E – Difficulty: =IFERROR(VLOOKUP(A2,'Competitor URLs'!A:F,6,FALSE),0)

Notes

  1. In Excel, swap VLOOKUP for XLOOKUP for cleaner, direction-proof matching.
  2. "Missing" and "Weak" rows are your gaps; "Covered" rows can be filtered out.
  3. Wrap lookups in IFERROR so unmatched keywords return a clean value, not an error.

Gap Scoring Formula Tab

Turn raw volume and difficulty into a single priority score you can sort on.

Goal: rank gaps by opportunity so effort goes to the keywords with the best return, not just the highest volume.

Add a Score column

  • Filter first: keep only rows where volume ≥ min_volume and difficulty ≤ max_kd.
  • Opportunity score: =ROUND((D2/(E2+1))*IF(C2="Missing",1.5,1),1)
  • Intent tag: [Informational/Commercial/Transactional]
  • Cluster: [Topic cluster this belongs to]

How the score works

  1. Volume divided by difficulty rewards keywords that are searched often but easier to win.
  2. The 1.5 multiplier pushes fully missing topics above ones where you already rank weakly.
  3. Adjust the weights to fit your niche; a formula is a starting point, not a verdict.

Priority View Tab

A filtered, sorted dashboard of the gaps to brief next, ready to hand to writers.

Goal: present the finished shortlist so a content lead can assign work without touching the formula tabs.

Columns to surface

  • Keyword, Gap type, Volume, Difficulty, Opportunity score
  • Assigned to: [Owner]
  • Target publish date: [Date]
  • Status: [Idea/Briefed/In progress/Published]

Build the view

  1. Use =SORT(FILTER(...)) in Sheets, or a pivot table, to pull the top-scoring gaps automatically.
  2. Add conditional formatting so "Missing" rows and high scores stand out at a glance.
  3. Refresh the input tabs monthly; the Priority View updates itself because it reads from the formulas.

How to use this template

  1. Duplicate the workbook and fill in the Setup tab: your domain, up to 3 competitors, and your thresholds.
  2. Export your own ranking keywords from your SEO tool and paste them into the Your Pages tab.
  3. Export each competitor's ranking keywords and stack them into the Competitor URLs tab, tagged by domain.
  4. Let the Gap Matching tab cross-reference the two lists and label each keyword Missing, Weak, or Covered.
  5. Filter out Covered rows so only real gaps remain in view.
  6. Apply the Gap Scoring formula, then sort the Priority View by opportunity score.
  7. Trim the shortlist to keywords that match your intent and business, then assign owners and dates.
  8. Re-paste fresh exports each month so the formulas recalculate and the Priority View stays current.

Pro tips

  • Keep raw exports on their own input tabs and never edit them by hand; do all logic in formula tabs so a refresh is just a paste-over.
  • Divide volume by difficulty instead of sorting by volume alone: a lower-volume keyword you can actually rank for often beats a giant one you can't.
  • Group matched gaps into topic clusters before you brief them, so one strong pillar page can capture several related keywords at once.
  • In Excel prefer XLOOKUP over VLOOKUP; it doesn't break when you insert columns and reads cleaner when someone else opens the file.

Frequently asked questions

Do I need Google Sheets, or does this work in Excel?

It works in both. The tab structure and columns are identical, and almost every formula is shared. The only swaps worth making are XLOOKUP for VLOOKUP and native FILTER/SORT, both of which behave slightly more cleanly in modern Excel and Sheets.

How many competitors should I load in?

Two or three direct competitors are usually enough to reveal the important gaps without drowning the sheet in noise. Pick sites that genuinely compete for your keywords rather than the biggest brand in your space, whose rankings you may never realistically match.

Where do the keyword exports come from?

Any SEO tool that exports the keywords a domain ranks for will work; you paste that export into the input tabs. The template is deliberately tool-agnostic, so use whichever platform you already have and keep the same source each month for consistent comparisons.

What does the opportunity score actually mean?

It's a simple ratio of search volume to difficulty, nudged up for topics you're completely missing. It's a way to sort, not a guarantee. Treat a high score as a prompt to review the keyword, not as proof it will rank or convert.

Why are some keywords showing as errors?

Usually a lookup can't find a match, often from stray spaces or inconsistent capitalization between tabs. Wrapping lookups in IFERROR handles this, and running TRIM on pasted keywords removes hidden whitespace that quietly breaks matches.

How often should I rebuild the analysis?

Monthly or quarterly is plenty for most sites. Because the formulas read from the input tabs, refreshing means pasting new exports rather than rebuilding anything. Rankings shift gradually, so checking too often mostly adds work without changing the priorities much.