Keyword grouping in Excel
Four formulas that genuinely group a keyword list in Excel or Google Sheets, the pivot table that summarises them, and an honest account of the point where a spreadsheet stops being the right tool.
Most articles about grouping keywords in Excel are an advert with a formula bolted on. This one is the other way round: the formulas are real and they work, and the limitation at the end is real too. If you have a few hundred keywords and a tag list you already understand, a spreadsheet is the correct answer and you should not download anything.
Column A holds your keywords starting at A2, column B holds monthly volume. A second sheet named Groups holds your group names in A2:A60. Every formula below is written for Excel 365 and works unchanged in Google Sheets unless noted.
Method 1 — Tag keywords against a group list
This is the keyword grouper most people actually want. You write the group names once, and every keyword gets labelled with each group name it contains.
On the Groups sheet, list your groups one per row: espresso, cold brew, grinder, price, vs, recipe. Then in C2:
=TEXTJOIN(", ", TRUE, IF(ISNUMBER(SEARCH(Groups!$A$2:$A$60, A2)), Groups!$A$2:$A$60, ""))Fill it down. Each keyword now carries every group name found inside it, comma separated. SEARCH is case-insensitive, which is what you want, and the IF over a range spills automatically in Excel 365 and Sheets — no Ctrl+Shift+Enter needed. On Excel 2019 or older, confirm the formula with Ctrl+Shift+Enter.
Two refinements worth making immediately:
- Order the group list by priority.
TEXTJOINreturns matches in list order, so putting your most important groups at the top means=TEXTBEFORE(C2, ",")gives you a usable primary group in one more column. - Catch the leftovers.
=IF(C2="", "UNGROUPED", C2)makes the gap visible. The ungrouped rows are the list of group names you have not written yet, and working through them is the whole maintenance loop.
Method 2 — Let the data suggest the groups
Writing the group list from memory is how you end up with an UNGROUPED pile of four hundred rows. Better to read the groups out of the keywords first. This counts every word in the list by frequency:
=LET(
words, TOCOL(TEXTSPLIT(TEXTJOIN(" ", TRUE, A2:A2000), " "), 3),
uniq, UNIQUE(words),
SORT(HSTACK(uniq, COUNTIF(words, uniq)), 2, -1)
)In Google Sheets, use this instead:
=SORT(
QUERY(FLATTEN(SPLIT(A2:A2000, " ")),
"select Col1, count(Col1) where Col1 is not null group by Col1", 0),
2, FALSE)The top thirty rows of that output, minus the stop words, are your group list. Paste them into the Groups sheet and go back to method 1. Doing it in this order takes about ten minutes and produces a far better tag list than guessing does.
Two-word phrases group better than single words. To count those, add a helper column of bigrams with =TEXTBEFORE(A2," ",2) and run the same frequency count over it.
Method 3 — Bucket by search intent
Intent is the one grouping a spreadsheet does almost as well as anything else, because intent really is signalled by specific words. In D2:
=IFS(
OR(ISNUMBER(SEARCH({"buy","price","cheap","order","coupon","discount"}, A2))), "Transactional",
OR(ISNUMBER(SEARCH({"best"," vs ","alternative","review","top ","comparison"}, A2))), "Commercial",
OR(ISNUMBER(SEARCH({"near me","opening","address","login"}, A2))), "Navigational",
TRUE, "Informational")The spaces inside " vs " and "top " matter. SEARCH matches anywhere in the string, so a bare "vs" also fires on servs and a bare "top" on stop and laptop. Order matters too: the first true condition wins, so transactional signals belong above commercial ones.
Roughly three quarters of any keyword list lands in Informational. That is normal, and the interesting reading is which of your groups have an unusually high commercial share.
Method 4 — Summarise with a pivot table
Grouping is only useful once you can see the totals. Select A1:D2000, then Insert → PivotTable. Put the group column in Rows, the volume column in Values set to Sum, the keyword column in Values set to Count, and intent in Columns.
What you are looking for is not the biggest group. It is a high volume total spread across few keywords — that is a topic one good page can plausibly take. Groups holding hundreds of keywords are usually competitive and slow.
Which method for which list
| Method | Effort | Good for | Fails when |
|---|---|---|---|
| Tag against a group list | Medium, ongoing | Lists where you already know the categories | New keywords keep arriving ungrouped |
| Word frequency | Low, one-off | Discovering what the groups should be | Stop words dominate the top of the count |
| Intent buckets | Low | Any list, as a second dimension | Intent is implied rather than stated |
| Semantic clustering | None, automatic | Lists over ~1,000, or unfamiliar markets | You need Google's own verdict on two queries |
Where the spreadsheet stops
Every method above matches characters. That is the ceiling, and it is a low one. Three keywords from a coffee list:
“cold brew ratio” · “how to make cold brew” · “iced coffee recipe”
A SEARCH for "cold brew" catches the first two and misses the third completely, even though anyone searching all three wants the same article. “cheap flights berlin” and “budget airline tickets to berlin” share one word. “SERP” and “search results page” share none. No formula fixes this, because the information needed is not in the characters.
The second limit is maintenance cost. A tag list is a piece of software you now own. Every export brings phrasings it does not cover, the UNGROUPED pile refills, and the marginal value of each new tag falls while the cost stays flat. Somewhere between one and two thousand keywords that curve crosses, and the spreadsheet quietly becomes the expensive option.
Sort by your UNGROUPED column. If it is under about 5% of rows and stays there between exports, keep using Excel. If it climbs every time you refresh the data, the list has outgrown character matching.
What a keyword grouper does instead
Grouping by meaning works differently. Each keyword is converted into a vector — a list of numbers placing it in a space where distance means difference in meaning — and keywords sitting close together are grouped. A model trained on enough text has learned that “SERP” and “search results page” belong together, without any tag list saying so.
Graph My Keywords does exactly this, and it does it in your browser tab: the model downloads once and then runs on your own machine, so the keyword file is never uploaded. That matters more than it sounds when the list belongs to a client and pasting it into a web app would need a data-processing agreement. It is free with no account up to 5,000 keywords, and larger lists are free on request.
Practically, the workflow replaces methods 1 and 2 entirely and keeps method 4. Drop the same CSV in, get groups you did not have to name, and export them back to CSV for the pivot table you were going to build anyway.
It is not strictly better at everything. A spreadsheet gives you exact, auditable rules — if a keyword contains "price" it lands in the price group, every time. Semantic grouping is a good draft produced by a machine that has never seen your market, so you review it. And neither approach sees live search results, which is the one thing the expensive SERP-overlap tools buy you.
Try the free keyword grouper
Same CSV, no tag list to write. Free up to 5,000 keywords, no signup, and the file never leaves your browser.
Group my keywordsFrequently asked questions
Can you group keywords in Excel?
Yes, and up to roughly a thousand keywords it is a perfectly reasonable choice. Tag against a group list with SEARCH and TEXTJOIN, read the candidate groups out of a word-frequency count, bucket intent with IFS, and summarise in a pivot table. The four sections above are the whole method.
What is the best formula for grouping keywords in Excel?
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(Groups!$A$2:$A$60,A2)),Groups!$A$2:$A$60,"")). It returns every group name contained in the keyword rather than just the first, so a keyword can legitimately belong to two groups, and you can still take a primary group with TEXTBEFORE.
Is there a free keyword grouper?
Yes — this one. It groups by meaning instead of by shared words, is free with no account up to 5,000 keywords, and runs entirely in your browser so nothing is uploaded.
Do I need keyword grouper software at all?
Not for a small list you know well. You need it when the tag list stops keeping up with the data, when the list is in a market you do not know well enough to name the groups, or when keywords that obviously belong together keep being split because they share no words.
What is the difference between keyword grouping and keyword clustering?
In everyday use, nothing — people mean the same thing. Where a distinction is drawn, grouping means sorting by a shared word or tag, which is what a spreadsheet does, and clustering means grouping by relatedness. The longer answer is here.