Every keyword tool exports data a little differently — different capitalisation, stray spaces, combined columns, duplicates across sources, junk rows. Before any analysis means anything, that data has to be cleaned and combined into one consistent list. Excel is still one of the best tools for the job: universal, flexible and, with modern functions and Power Query, capable of automating the whole process.
This guide covers the preparation stage: setting up a keyword spreadsheet, cleaning whitespace and formatting, normalising case, splitting columns, combining and deduplicating lists, filtering, word and character counts, extracting domains, generating keyword combinations, and automating it all with Power Query and macros. For clustering, gap analysis, prioritisation and dashboards, see keyword analysis in Excel.
Note: some functions mentioned (such as TEXTSPLIT, UNIQUE, FILTER and the REGEX functions) require Microsoft 365 or a recent Excel version. Classic alternatives are given where useful.
Setting Up
Building Your First Keyword Spreadsheet in Excel
Start with one table (Insert → Table) with clear columns: Keyword, Source, Search Volume, Difficulty, CPC, Current Position, URL, Intent, Topic, Priority, Notes. Tables expand automatically, keep formulas consistent down each column, and make filtering and pivot tables easy. Keep raw exports on separate sheets and never edit them — build your clean list from them so you can always trace back.
Cleaning
Cleaning Whitespace and Formatting Errors From a Keyword Export
Invisible characters are the most common reason identical-looking keywords fail to match:
=TRIM(A2)removes leading, trailing and repeated spaces.=CLEAN(A2)removes non-printing characters.- Non-breaking spaces (common in web exports) survive TRIM; replace them with
=SUBSTITUTE(A2,CHAR(160)," ").
Combine them: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))), then paste the results as values.
Converting Keyword Text to Lowercase for Consistency in Excel
Search engines treat queries case-insensitively, but Excel's matching and some deduplication approaches can be thrown off by case variations from different tools. =LOWER(A2) normalises everything. Lowercase before deduplicating and before lookups so "Best Running Shoes" and "best running shoes" are treated as one keyword.
Removing Stop Words From a Keyword List Before Importing to Excel
For word-frequency analysis, stop words ("the", "a", "for", "to") drown out meaningful terms. Remove them in a copy of your data used for analysis — never from the keywords themselves, where "for" can change intent ("software for nonprofits"). In Excel, a helper column with nested SUBSTITUTE functions or Power Query's replace steps can strip a list of stop words; a dedicated text tool is faster for one-off lists.
Splitting a Delimited Keyword Export Into Usable Columns
Some exports put several values in one column, separated by commas, tabs or pipes. Use Data → Text to Columns (choose the delimiter), or in Microsoft 365, =TEXTSPLIT(A2,","). Check for delimiters inside values (such as commas in keyword phrases) before splitting, or the columns will shift.
Removing Duplicate Keywords in Excel Before You Start Analyzing
After cleaning and lowercasing, remove duplicates with Data → Remove Duplicates on the keyword column, or create a dynamic unique list with =UNIQUE(A2:A5000). When combining sources, decide which row to keep (for example, the one with the most complete data) by sorting first, because Remove Duplicates keeps the first occurrence.
Combining and Organising
Combining Keyword Lists From Multiple Sources in Excel
Stack exports from different tools into one table with a Source column so you know where each keyword came from. Align column names first (volume, difficulty and CPC may be named differently), clean and lowercase everything, then deduplicate — keeping notes of which sources found each keyword can be done with a pivot table or COUNTIFS before deduplicating.
Sorting a Keyword List Alphabetically in Excel
Alphabetical sorting (Data → Sort A to Z, or =SORT(A2:A5000)) groups related variations together — "running shoes", "running shoes for flat feet", "running shoes women" — which makes duplicates, near-duplicates and clusters easy to spot by eye.
Using Excel Filters to Narrow Down a Keyword List by Topic
AutoFilter (Data → Filter) with "Text Filters → Contains" lets you isolate keywords containing a word ("price", "how", "near me"). In Microsoft 365, =FILTER(A2:D5000, ISNUMBER(SEARCH("review",A2:A5000))) creates a live filtered list on another sheet — useful for building topic subsets you can analyse separately.
Using Regex-Like Pattern Matching to Filter Complex Keyword Structures in Excel
For patterns that simple filters cannot express — questions starting with "how" or "what", keywords containing years, model numbers — newer Microsoft 365 versions include REGEX functions such as =REGEXTEST(A2,"^(how|what|why)\b"). Without them, combine LEFT, SEARCH and wildcards in COUNTIFS ("how *"), or use Power Query's text functions.
Counting Words and Characters in Excel for Keyword Length Analysis
- Characters:
=LEN(A2) - Words:
=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1
Word counts separate head terms (1–2 words) from long-tail phrases (3+ words) and help you see the shape of your keyword list; character counts help with ad copy and title length checks.
Extracting Root Domains From a Backlink Export in Excel
To summarise backlinks by linking domain, strip URLs down to the host. In Microsoft 365: =TEXTBEFORE(TEXTAFTER(A2,"//"),"/",,,,TEXTAFTER(A2,"//")) returns the host (the last argument handles URLs with no trailing path). Then remove a leading www. with SUBSTITUTE. Classic Excel can do the same with MID and FIND, or use Power Query's URL parsing. Count links per domain with a pivot table.
Generating Keyword Combinations
Using CONCATENATE and TEXTJOIN to Build Long-Tail Keyword Combinations
Combine modifiers and core terms to generate candidate long-tail phrases: =A2&" "&B2, =CONCAT(A2," ",B2) or =TEXTJOIN(" ",TRUE,A2:C2) (TEXTJOIN skips blanks). Generated phrases are candidates, not keywords — validate them for real demand before writing.
Automating Keyword Cartesian Combinations for PPC Ad Group Structuring
A Cartesian product combines every item in one list with every item in another — every service with every city, every product with every modifier — which is useful for building PPC ad groups and local landing page plans. In Microsoft 365, =TOCOL(A2:A6&" "&TRANSPOSE(B2:B4)) generates all combinations of two lists in one step. Review the output: many combinations will be unnatural or have no demand.
Automation
Using Excel Power Query to Automate Recurring Keyword List Cleaning
Power Query (Data → Get & Transform) records cleaning steps — trim, clean, lowercase, split columns, remove duplicates, filter rows, merge sources — and replays them whenever you refresh. Point it at a folder of exports and it can combine and clean every new file automatically. It is the single biggest time-saver for anyone who processes keyword exports regularly.
Using Excel Macros (VBA) to Bulk-Process Recurring Keyword Exports
Macros automate repetitive actions that Power Query does not cover — formatting reports, running a sequence of sorts and filters, exporting sheets. Record a macro while performing the steps once, then edit the VBA code to generalise it. Save workbooks as .xlsm, keep backups, and only run macros from trusted sources.
The Bottom Line
Clean data is the foundation of keyword research. Trim and normalise text, split and combine sources into one table, deduplicate carefully, use filters and pattern matching to create topic subsets, and automate the whole routine with Power Query so each new export takes minutes instead of an afternoon.
Next step: For quick one-off cleaning jobs without formulas, paste your list into SeoWolf's free Notepad tools — trim, lowercase, deduplicate and remove stop words in seconds.