Discover Most Frequent Modifiers with Excel

SMS Text

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:

Excel: discover frequent modifiers

(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.

Excel: frequent modifiers

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.

Ann Smarty

Ann Smarty

Brand amd Community Manager at Internet Marketing Ninjas
Ann Smarty is the blogger and community manager at Internet Marketing Ninjas. Ann's expertise in blogging and tools serve as a base for her writing,... Read Full Bio
Ann Smarty
Subscribe to SEJ!
Get our weekly newsletter from SEJ's Founder Loren Baker about the latest news in the industry!