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 is the blogger and community manager at Internet Marketing Ninjas. Ann's expertise in blogging and tools serve as a base for her writing, tutorials and her guest blogging project, MyBlogGuest.com.
Ann Smarty
Get the latest news from Search Engine Journal!
We value your privacy! See our policy here.
  • Eddy Gonzalez

    Great tip Ann! I agree Excel is fantastic tool for SEO and for Paid Search campaigns.
    Thanks

    Eddy

  • There are tons of specific softwares for SEO, but if you know how to use it, Excel is the best of them.
    Great post Ann!

  • Speaking of Excel, any ideas how to make little + – icons where upon click, list of some typed in stuff expands?

  • hi Ann,

    great tips as usual!

    just a quick note:

    use CTRL+F;
    click “Find and Replace” tab;

    can be easily done with ctrl+H. do it dpsens times a day )

  • Jesse Sisson

    Every day I learn something new concerning SEO, and today was no exception! This is a really informative post, and now I have to get to work trying out these awesome tips at my SEO blog, http://www.free-seo-tips-and-tricks.kickthumproar.com
    Thanks so much for great posts like this one!

  • Great…I only used Excel to manage my keyword or to delete duplicate url 🙂

  • I always use excel it keeps things better organised.

  • Excel is an essential tool when dealing with keywords and SEO. This tip will be very useful when efficiently trying to find my greatest volume keyword terms. Will surely be testing out soon.