Keyword Research Template: Free Excel Sheet with Priority Score

Sorting a keyword list by search volume ranks the wrong work first. The highest-volume queries are usually the hardest and the vaguest, and they are rarely the ones that produce anything.

This sheet scores each query instead, discounting volume by difficulty and then weighting by intent.

The Keywords tab. Fill the first eight columns; the priority score calculates itself and the sheet is pre-filtered so you can sort by it.

How the priority score works

The formula is deliberately simple enough that you can audit it rather than trust it:

Priority = Volume / (1 + Difficulty/10) × Intent weight

Intent weights are: transactional 1.5, commercial 1.3, informational 1.0, navigational 0.6.

Two things follow from that shape. Difficulty discounts rather than excludes — a hard keyword is not disqualified, it just has to be worth more volume to earn the same priority. And intent can outrank volume: a transactional query at 200 searches scores above an informational one at 250, which matches how they actually contribute.

Difficulty scores are not comparable across tools

Every tool calculates difficulty differently, so a KD of 4 in one tool is not a KD of 4 in another. Use one tool consistently for a whole sheet. Blank difficulty is also not the same as easy — it usually means the tool has insufficient data, which is a reason to inspect the results page manually rather than to assume an opportunity.

Mapping your tool export to this sheet

The step that actually stalls people: you export from a tool, open a template, and cannot tell which of fourteen columns maps where. This is the translation.

This sheet	Ahrefs	Semrush	Moz	Search Console
Keyword	Keyword	Keyword	Keyword	Query
Volume	Volume	Volume	Monthly Volume	Not available — GSC reports impressions, not volume
Difficulty	KD	KD %	Difficulty	Not available
Current position	Current position	Position	Rank	Position — the most trustworthy of the four
Intent	Intents	Intent	Not available	Judge it yourself from the query
Modifier, Cluster, Target URL, Status	You fill these in	You fill these in	You fill these in	You fill these in

Two things worth knowing before you paste anything in:

Difficulty scores are not comparable between tools. A KD of 4 in Ahrefs is not a KD of 4 in Semrush. Pick one tool and use it for the whole sheet, or the priority column ranks noise.
Search Console position beats every tool’s estimate, because it is your actual average position rather than a sample. Where you have both, use Search Console for position and the tool for volume.

Using it in Google Sheets

The file is a standard .xlsx, so it converts directly: in Drive, File → Import → Upload, then choose Replace spreadsheet. Everything survives the conversion because the sheet only uses functions Sheets also has — IFERROR, ROUND, IF and arithmetic. The dropdowns become Sheets data validation, and the filter view carries across.

One adjustment if you work in Sheets: paste tool exports with Paste special → Values only. Pasting formatted cells overwrites the priority column’s formula, which is the one column you want left alone.

The clustering rule

This is the decision the sheet exists to support, and it has a one-line test:

Queries belong in the same cluster when one page can satisfy all of them without compromise. If satisfying the second query would make the page worse for the first, split them.

Applied to a real example: “digital marketing proposal template”, “digital marketing proposal pdf” and “digital marketing proposal examples” all describe someone who wants a proposal they can use. One page carrying an editable template, a completed example and a PDF serves all three better than three thin pages each serving one.

Whereas “digital marketing proposal template” and “digital marketing proposal meaning” do not cluster. Adding a definition section long enough to satisfy the second would push the template further down the page and make it worse for the first.

Getting this right is what stops a keyword sheet from becoming a plan for forty weak pages.

Method

List the jobs your customer is trying to complete, in their words.
Expand each job into queries using your keyword tool and the search suggestions.
Tag every query with its intent and its modifier (template, example, checklist, pdf, software, best, vs).
Group queries into clusters using the rule above.
Pick one target URL per cluster and write it in the sheet before creating anything. This is the step that prevents two pages competing for the same query later.
Sort by priority and work down the list rather than around it.
Record current position, so the later decision to refresh or rewrite is evidence-based.

What I do before trusting a keyword

The sheet gives a priority order. Before acting on the top rows, two checks:

Look at the actual results page. The results tell you what the search engine believes the intent is, which frequently disagrees with the intent you assigned. If the page-one results are all templates and you were planning a definition article, the engine has already decided and you will not win the argument.
Ask whether I can make the best page for it. Not a comparable page — the best one. If the honest answer is no, a high priority score is not an opportunity. This is the check that removes most of the keywords I would otherwise have wasted a month on.

I also treat a blank difficulty column as a prompt to inspect manually rather than as good news. Missing data most often means the query is unusual or newly emerging, which can be genuinely valuable — but it is a reason to look, not a reason to assume.

Why modifiers matter more than head terms

The modifier column exists because modifiers are where the achievable opportunities usually sit. A head term is contested by everyone in the market. The same topic with “template”, “example”, “checklist” or “pdf” attached is a narrower query, with clearer intent and a searcher who wants an output rather than an explanation.

That is easier to satisfy well and easier to rank for, and the visitor arrives wanting something specific you can actually give them. Tagging the modifier makes those opportunities visible in the sheet instead of buried among head terms with larger volumes.