How to Cluster Keywords in a Spreadsheet Without a Tool

You don’t need Ahrefs, Keyword Insights, or any paid clustering tool to group keywords by topic. Open a spreadsheet, pull the top 10 Google results for each keyword by hand, and count how many URLs repeat across pairs. Three or more shared URLs out of ten means the keywords belong on the same page. Fewer than that, split them. This manual SERP-overlap method is slower than software — budget 30 to 90 minutes per 50 keywords — but it costs nothing, and it’s the same logic paid clustering tools run automatically. Below is the step-by-step spreadsheet process, the exact formulas that do the counting, and the mistakes that turn a clean cluster map into a mess.

What does “clustering by SERP overlap” actually mean?

Clustering by SERP overlap means grouping keywords based on how many of the same URLs already rank for them, not based on how similar the words look. “Running shoes for flat feet” and “best running shoes flat feet” might read like near-duplicates, but if Google serves ten different pages for each, they’re not the same search intent — treat them as two pages, not one. Ahrefs’ own Keyword Clustering feature, for comparison, runs on two signals: SERP overlap rate and semantic similarity scored by a language model. This guide shows you how to do the SERP-overlap half by hand, in a spreadsheet, for free. You lose the semantic layer, but overlap alone catches most real clusters.

This matters because Google has already done the intent-matching work for you. If the same page ranks for two different keyword phrases, Google has decided one page satisfies both queries. Copying that judgment into a spreadsheet is more reliable than guessing from the keyword text alone.

What do you need before you start clustering?

Gather these four things before opening a blank sheet. Skipping any one of them means redoing work halfway through.

• A deduplicated keyword list. 500–2,000 rows is workable by hand. Past 2,000, manual clustering turns into a multi-day project — see the “how long does this take” section below.
• Search volume per keyword. Pull this from Google Keyword Planner, Google Search Console, or any free source. You’ll sort by volume so you work on the keywords that matter first.
• A spreadsheet tool. Google Sheets or Excel both work. Google Sheets is easier if more than one person needs to check SERPs at the same time.
• An incognito or private browser window. Personalized search results and location settings shift your top 10, which throws off overlap counts between team members checking the same keyword on different days.

How do you cluster keywords in a spreadsheet, step by step?

Here’s the full manual process, from raw list to finished cluster map.

1. List every keyword in column A, volume in column B. One keyword per row. No duplicates.
2. Sort by volume, descending. Work top-down so your highest-traffic opportunities get clustered first if you run out of time.
3. Search each keyword in an incognito window and record the top 10 URLs. Use a second tab. One approach: one row per keyword, ten columns (URL 1 through URL 10). For 50 keywords, this step alone takes roughly 45–60 minutes.
4. Build a comparison matrix. For every keyword pair, count how many URLs appear in both top-10 lists. In Google Sheets, this formula counts overlap between the URL range for keyword A (C2:L2) and keyword B (C3:L3):
   
   =SUMPRODUCT(--(COUNTIF(C2:L2,C3:L3)>0))
   
   This returns a number from 0 to 10 — the shared-URL count for that pair.
5. Set your overlap threshold. Three or more shared URLs (30%+) means treat the pair as one cluster. See the threshold table below for the full breakdown.
6. Tag clustered keywords with a cluster ID. Add a column and manually assign a short cluster name (e.g., “flat-feet-running-shoes”) to every keyword that cleared the threshold against at least one other keyword in the group.
7. Pull each cluster together with a lookup. Once every keyword has a cluster ID, use =VLOOKUP(A2,ClusterMap,2,FALSE) against a separate cluster-name reference table, then filter or sort by that column to see each group as a block.
8. Assign one target URL per cluster. Name the cluster after its highest-volume keyword. That keyword becomes the primary term the page targets; the rest become supporting terms inside the same content.
9. Manually recheck every cluster for intent mismatch before locking it in. A formula tells you URLs overlap. It doesn’t tell you why. Open the top 3 results for each cluster and confirm they’re actually answering the same question.

What formulas actually do the clustering work?

Four formulas cover almost everything in this process. You don’t need macros or scripts.

What overlap threshold should you use to merge keywords?

Not every shared URL means “same page.” Use a sliding scale, not a single cutoff.

What mistakes wreck a manual keyword cluster?

Most bad clusters come from five repeatable errors:

• Clustering by search volume instead of overlap. A high-volume keyword and a low-volume keyword can look related and still serve completely different SERPs.
• Skipping incognito mode. Personalized results and location settings change your top 10, so two people checking the same keyword on different machines get different overlap counts.
• Merging keywords that share URLs but split intent. “CRM software” and “CRM software free” can share 5 of 10 URLs and still deserve separate pages, because one buyer wants pricing and the other wants a free plan.
• Never rechecking clusters after they’re built. SERPs shift. A cluster that was valid in January can drift apart by June as Google reranks intent. Recheck every 3–4 months for your top clusters.
• Trying to manually cluster more than 300–500 keywords in one sitting. Past that volume, error rates climb and the spreadsheet becomes unmanageable. Split the list into batches by topic first, using our keyword mapping process to group keywords by page intent before you cluster within each group.

How long does manual clustering take compared to a paid tool?

For 50 keywords, expect roughly 1–2 hours by hand: SERP collection plus overlap scoring plus a manual intent check. A paid clustering tool does the same 50 keywords in under a minute, though you still need to review the output manually for intent mismatches — software doesn’t skip that step either. At 300 keywords, manual clustering becomes close to a full working day. That’s the point where the math starts favoring a tool or a done-for-you service over your own hours. If your list is bigger than a few hundred keywords, or you’d rather not spend a day in a spreadsheet, our one-time keyword clustering service delivers the finished cluster map without the manual SERP checks — priced à la carte instead of a monthly retainer.

Before you cluster anything, make sure your raw keyword list is solid — a messy input list produces messy clusters no matter which method you use. Our full keyword research process covers how to build that list from seed keywords through to volume and intent data. And if you’d rather see the SERP-overlap method run through clustering software instead of formulas, our guide to keyword clustering walks through the tool-based version of this exact approach.

What do you do with the clusters once they’re built?

A finished cluster map is a list of groups, not a plan. The next step is deciding which clusters get a page first, since most teams can’t publish all of them in the same month. Our keyword prioritization framework scores each cluster on volume, difficulty, business relevance, and funnel stage, then turns that score into a realistic publishing order — the natural next step after this one.

FAQ

What counts as SERP overlap in keyword clustering?

SERP overlap is the number of identical URLs that appear in the top 10 Google results for two different keywords. If 5 of the same 10 URLs rank for both “running shoes for flat feet” and “best flat feet running shoes,” that’s 50% overlap — a strong signal both keywords belong on one page.

Can you really cluster keywords with zero paid tools?

Yes, for lists under roughly 500 keywords. You need a spreadsheet, an incognito browser window, and time to check each SERP manually. It’s slower than software — 30 to 90 minutes per 50 keywords — but the underlying logic (SERP overlap) is the same one paid tools automate.

How many keywords can you realistically cluster by hand?

Most teams can manage 300–500 keywords in a manual pass before error rates climb and the spreadsheet becomes unwieldy. Beyond that, split your list into topic groups first using keyword mapping, then cluster within each smaller group.

What overlap percentage should trigger a merge?

Use 70%+ overlap (7 or more shared URLs out of 10) as an automatic merge. Treat 30–70% overlap as a manual review case — check intent before merging. Below 30% overlap, keep the keywords as separate pages.

Does manual clustering work as well as a paid tool?

It gets you most of the way there. Paid tools like Ahrefs add a semantic-similarity layer on top of SERP overlap, which catches a few edge cases manual checking misses. For most keyword lists, though, the overlap method alone produces workable clusters.

Written by Palash, founder of PalV’s DM. 5+ years in SEO, 1,000+ articles published.