Excel is one of my most favorite SEO tools. I use Excel for reporting, content planning, backlink research and keyword organizing. I discover new possible areas of uses it each time I need to figure how to cope with some new SEO task I come across.
This time I decided to share a cool way to discover your most frequent keyword modifier. Let me start with the basics: your key phrase consist of the core term (that’s what your site is about) and one or more additional words (these are modifiers):
modifier1 + modifier2 + core term = long tail
This formula describes this phrase, for example: ‘free online diets‘ (where ‘diets’ is the core term).
Naturally, there are some patterns of modifiers you want to be aware of. Some words tend to be frequently used in one neighborhood – and you need to know them to catch these search referrals.
So, first step is to export your keyword list to Excel.
Now, follow these quick steps:
- use CTRL+F;
- click “Find and Replace” tab;
- type the word you think may be frequently used with your core term,
- Click “Options” button;
- Choose to “replace with” format;
- Click “Patterns” tab;
- Choose the color you want to highlight the cell containing the word:
(Note: I am hardcore Excel 2003 user, just because I got used to it, so my screenshots may look a bit outdated. Newer editions have a bit different interface but you’ll find all features in the old places.)
- Click OK and then “Replace All”;
- You should then see how many times the word was used, plus the cells containing it will be highlighted.
Note, that the matching is not exact, that means that the search in the example will include cells containing ‘freebie’; and if you search for a noun, say, ‘plan’, the cells containing plural form (‘plans’) will be also highlighted. Therefore, if you mean to broaden the scope, search for the shortest form to include all possible versions.
The method is dead simple but really useful. With it, you’ll be able to visualize your key phrase patterns – and we know that visualization is one of the best ways to notice what you might have missed otherwise.
