ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Business and Employment»
  • Small Businesses & Entrepreneurs

SEO Keyword Research + Excel Functions

Updated on February 14, 2013

Keyword Visual from Wordle


There are many reasons as to why people write hubs and other types of content. The most popular reasons being people are passionate about writing.

Others do it to make a few extra bob. Others actually make a living out of it, I am not one of those people! however, I have made a career out of on line marketing and content writing plays a big part in that.

Computers and the internet have changed the world dramatically over the last 20 odd years, mostly for the better.

Take me for instance, being dyslexic I did not do very well at School due to my atrocious spelling .

After I left school came the .com boom. I got a computer, found the spelling function and that is the reason I am able to bring you this Hub about SEO Keyword Research and Excel.

Most tutorials have a habit of explaining everything as red widgets or blue widgets but I am going to give you guys something a little more tangible and transparent.

Please note you do not have to be an excel guru to to do this. A basic understanding of excel should do the trick.


Finding the keywords that will make you money

So lets say you have a new (no Google analytic's data) franchise that specialises in providing a teeth whitening service in 20 of the biggest cities in the UK.

You have whatever resources and budgets you need (you wish!) to compete in all 20 cities in the UK.

For example lets use cities by population. They are...

  • London
  • Birmingham
  • Leeds
  • Glasgow
  • Sheffield
  • Bradford
  • Edinburgh
  • Liverpool
  • Manchester
  • Bristol
  • Wakefield
  • Cardiff
  • Coventry
  • Nottingham
  • Leicester
  • Sunderland
  • Belfast
  • Newcastle Brighton
  • Hull

Now we have the locations, we need to think of 4 or 5 keyword modifiers we think people would use in search engines to find our services.

If you are new to the market like I am with teeth whitening services its a good idea to start in Google's keyword research tool. For people who are familiar with there market areas I am sure you guys will have a fair idea on what keywords to use.

Google's Keyword Research Tool

There are many keyword research tools out there, some free, some cheap, some expensive, some good, some OK and some bad. None are excellent!

My argument on using Google's keyword research tool as to others it that most other keyword research tools use Google's API to power there own KWR tools. So why pay a 3rd party when you can get your data straight from the horses mouth for free?

Moving on... Firstly, if you do not have a Google account please sign up and create an account. Google will give you data if you are not signed however, it is limited.

Once you have signed in to Google double check your settings are OK.

For instance I am targeting the UK market so I will set my advanced filter to united Kingdom and language spoken to English.

Advanced Filter in Google's KWR tool.
Advanced Filter in Google's KWR tool.

Add Keywords to Tool

Once we have checked the settings are OK its time to add some obvious keywords to the tool.

They are...

  • Teeth whitening services
  • Teeth whitening
  • Teeth whitening service

Some other things to keep in mind is to make sure you set match type to exact match.

The radio button above the advanced filters link that reads (only show ideas closely related to my search terms) is optional. Some times leaving this blank works good however, some times it pollutes your results with not so relevant keywords.

In this case I have left the radio button blank to give me the widest berth of possible keyword targets.

Data on your chosen keywords

Keyword Ideas from Google

Keyword Elimination

Google returned a total of 803 keywords that they think may be relevant to your site. Now we need to go through them all and segment the wheat from chaff. Its a little time consuming but this is where an SEO strategy begins.

If you dive into a campaign without having a direction it could cost you big time.

The Google keyword tool will show you the most relevant keywords from top to bottom so analysing the first page results will be sufficient for this test however, for mature campaigns you will need to dig deeper.


  1. Is to drill through the first page results, ticking the radio box for any keywords that you find may lead to sales (leave location based keywords out at this stage)
  2. Click the more like these box highlighted below

This narrows down and groups the list. How far you want drill down is totally up to yourself but for this exercise we will stick to the 23 keywords.

Grouped keywords below...


Now we have our keyword group its time to extract these from Google's keyword tool to CSV file. Simply click the download button and download all.

Once you have the spreadsheet open we want to delete the brackets wrapped around the keywords. To do this...

  • hit "control F" on your keyboard
  • hit replace
  • enter [ into the "find what" row
  • leave the "replace with" row blank
  • hit the "replace all" button and viola

Repeat for the bracket facing the opposite way.

Now we want to delete column A,B & E so we are left with the "local monthly searches" column alongside the keyword column.

I am going to use the top 4 keywords based on relevance and search volume for this tutorial however, mature campaigns will need a lot more focus and elbow grease (work).

In saying that, remember you cant possibly manage all possible keywords. With a modest budget sticking to a small group of 3 - 4 keywords pre page will keep you in a good place.

When shifting data around in excel the software works better when a header is used along the top of the columns. Now we should have something that looks like this...

Now create a new sheet (tab).

So we will use the most popular generic term "teeth whitening" for example. In the new sheet insert the list of locations into column B. In column A have the key phrase present next to the location terms.

  1. In cell C2 hit the function button, search and select the CONCATENATE function.
  2. In the function argument box row "text1" select cell A2
  3. In row "text2" select cell B2

As you can see this joins up the texts in cell A2 and B2. You may notice there is a space missing. If in cell A2 and add a space at the end of the text string and then select the cell, double tap the bottom corner so it changes all other cells below.

Now select cell C2 and double tap the bottom right so all cells below are filled with location based phrases like the screen grab below.


The pleasure with adding the CONCATANATE function is that you can simply change the text in cell A2, select and double tap the bottom rite corner of the cell and you have a whole load of new keywords to check with minimal fuss.

For example I changed the keyword "teeth whitening" text in cell A2 to "teeth whitening in ". Selected the cell and double tapped the bottom rite corner. This changed all cells in column C to my desired keywords.

Now its just a case of repeating the above process.

  1. Insert keywords into Google's KWR tool
  2. Tick all keywords you would like to add to your campaign
  3. Click the "more like these" button to find more suitable keywords
  4. Change to another keyword in cell A2
  5. Copy keywords and paste into Google KWR tool (Make sure to copy and paste the keywords below the keywords already in the tool box)
  6. Keep repeating process until you are happy with your selection keywords.
  7. Once you are happy with your keywords - download and easily sort using a pivot table.

Thanks, I hope you found this useful. This is a really quick way of efficiently carrying out keyword research.

There was so much more little details I would have liked to share but time is of the essence. Hopefully these instructions will be enough to get you started or shave some time of your usual way of doing it.

I will do a hub on how to create a pivot table to easily sort your final list per keyword group or location.


    0 of 8192 characters used
    Post Comment

    No comments yet.


    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: ""

    Show Details
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized. (Privacy Policy)
    CloudflareThis is used to quickly and efficiently deliver files such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the or domains, for performance and efficiency reasons. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisements has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)