Tips and Tricks – Excel and SEO: Word Search Formula

The following Excel formula is useful for keyword research, perhaps one of the most important steps in search engine optimization. You should always conduct keyword research prior to writing content for your website. By identifying relevant keywords with high traffic and low competition, you stand a greater chance of attracting visitors interested in your content.

The word search formula enables us to sort a spreadsheet depending on whether words exist in an adjacent cell. Think of it as a full text search on a column. But instead of just seeing which cells match, we can also interact with the results.

Search Matching Words Using Excel

This formula will find the existence of a word or words in a cell:

=IF(ISNUMBER(FIND("social media",A2)),"true","false")

How to Use the Excel Word Search Formula

  1. Create a new column next to the column containing the words you are searching for.
  2. Click the cell next to the cell you want to search and paste the formula as shown in the screenshot below. In the formula, replace “social media” with the text you would like to search and press Enter. The formula searches the adjacent cell and writes “true” or “false” depending on whether the search term was found. **Replace A2 with the cell reference you want to count if it does not match the image below.
Excel formula for searching words in a cell
  1. Duplicate the formula in the remaining cells by “dragging” the formula down the spreadsheet. (For illustration and details, see Tips and Tricks – Excel and SEO:Word Count Formula)
  2. Now you can sort the spreadsheet by the results of the word search. Select column B and sort from Z to A to see all the results that were true. Excel may show a Sort Warning dialog box. Make sure you set it to “Expand the selection” to keep your columns in sync.

IMPORTANT– The formula is case-sensitive. That means a search for “Social media” and “social media” will return different results. The formula will search for whatever you enter between the quotes, so be careful with spaces as well.

Excel Word Search Formula and Search Engine Optimization

Suppose we are a social media marketing firm in the process of researching keywords to improve our search engine optimization for keywords associated with social marketing. We are using a tool, like SEMRush, Wordtracker or the Google Keyword Tool, to find keywords related to two phrases: social media marketing and social network marketing. We complete the keyword search and get back the following 10 results:

Social marketing keywords

Just by looking at the list, it is not clear how many phrases include the term “social media” and how many include the term “social networking.” In the real world, you are likely to have hundreds or even thousands of results. The Excel word search formula helps us sort the list so we can move the relevant keywords to a new spreadsheet and run further analysis.

This is a somewhat poor example. Simply sorting the column would group our keywords so we could pick out cells that match “social media” easily. This is generally not the case in practice. Keywords can be “sandwiched” between other words, rendering the simple column sort useless. The word search formula helps ensure you do not miss those important phrases.

Posted in Search Engine Optimization, SEO Tips and Tricks | Tagged , , , , , , , | Comments Off

Who Benefits From Google Instant Search?

Google just released a new search enhancement that changes the playing field with regard to search engine optimization. As you begin typing in the search field, suggestions are displayed and search results are refreshed in real time. While it benefits surfers by speeding up access to information, sites in first positions that start with the letter getting typed receive additional exposure.

Google Instant Search screenshot

A is for Amazon

So who might benefit from this change? Here’s a list of the current #1 position sites for each letter:

  • A – Amazon
  • B – Best Buy
  • C – Craigslist
  • D – Dictionary.com
  • E – Ebay
  • F – Facebook
  • G – Gmail
  • H – Hotmail
  • I – IKEA
  • J – Monster.com
  • K – Kohl’s
  • L – Lowe’s
  • M – MapQuest
  • N – Netflix
  • O – Orbitz
  • P – Pandora
  • Q – BrainyQuote
  • R – Redbox
  • S – Skype
  • T – Target
  • U – UPS
  • V – Verizon
  • W – Weather.com
  • X – Xbox
  • Y – Yahoo
  • Z – Zappos

Battle of the Brands

What makes this interesting is whether big brands change their strategy to gain an edge. Could we see Bank of America fighting it out with Best Buy? Walmart vs. the weather? With Lowe’s in control, does Home Depot get any less traffic?

Google Killed SEO… Again.

With other recent changes, including additional search results from a single domain and blended search, top brands command almost entire pages for single letters. Check out A for Amazon and B for Best Buy for examples. How this affects search engine optimization remains to be seen. No doubt some will say they killed SEO, but in my opinion, they simple changed the game yet again. I’ll be very interested to see the changes, if any, in traffic going to these sites in the coming months. It may give some indication as to how much Google Instant really changed search engine optimization.

Read More About Google Instant Features

Posted in Search Engine Optimization | Tagged , | Comments Off

Tips and Tricks – Excel and SEO: Word Count Formula

Welcome to the first in a series of articles designed to teach quick tips and tricks related to web development, online marketing and web analytics. We will primarily focus on providing non-technical advice to help as many people as possible improve their online presence. So without further ado, we’ll start with a Microsoft Excel tip that can help with your search engine optimization and hopefully other tasks as well. Be sure to leave a comment if you’ve found other uses for this technique.

Count Words Using Excel

Here’s a helpful formula that will count words in a cell:

=IF(LEN(A2)=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)

How to Use the Excel Word Count Formula

  1. Create a new column next to the column containing words you want to count.
  2. Click the cell next to the cell you want to count, paste the formula as shown in the screenshot below and press Enter.  The formula calculates the word count and writes it to the cell.  **Replace A2 with the cell reference you want to count if it does not match the image below.
Excel formula for counting words in a cell
  1. Duplicate the formula in the remaining cells by “dragging” the formula down the spreadsheet.  With the cell still highlighted, hover your cursor over the square dot in the lower right corner of cell B2 (shown in the screenshot above) until it turns into a plus sign.  Then simply click and drag until you reach the last item in the spreadsheet as shown below.  Release the click and the formula will fill in the word counts for the respective cells.
how to use Excel word count formula - step 1
how to use Excel word count formula - step 2
  1. Now you can sort the spreadsheet by word length.

Excel Word Count Formula and Search Engine Optimization

Counting keywords can help you find long tail keywords (more descriptive phrases that tend to have a higher probability of conversion) and keywords that fit certain specifications. For example, dmoz.org likes 25-30 words in a site description. Having a list of keywords by word count can help you massage the description and remain within spec.

Keyword Research Tools

Tools like the Google AdWords Keyword Tool are a great resource for finding keywords to optimize your site for. While it’s recommended that you use multiple keyword research tools, it’s hard to beat a free online tool offered by the top search engine.

The AdWords Keyword Tool not only gives you keywords, but levels of competition and estimated amounts of monthly search traffic as well. However, it does not currently have a way to export the number of words for a particular key phrase. This is where the formula comes in. I plan to cover the AdWords Keyword Tool in a future post, so for now let’s assume that you understand how to search and export a list of keywords.

  1. Apply the word count formula to your exported Google AdWords Keyword Tool spreadsheet.
Excel word count formula and Google AdWords Keyword Tool
  1. Sort the spreadsheet by word count.
  2. Create a new spreadsheet and copy over all the cells for the 1 word matches. The new sheet should only contain keywords with a 1 in the Words column.
Keywords grouped by number of words
  1. Continue by creating spreadsheets containing sets of “same count” keywords.
  2. Now you have groups of keywords arranged by word count to analyze.

Analyzing Keywords

With the grouping out of the way, you can start sorting the individual sheets. In general, we are looking for keywords that have high amounts of traffic (monthly searches) and low competition.

  1. Use the sort dialog box to sort by competition smallest to largest and then by monthly searches largest to smallest.
Excel sort dialog box
  1. Go down the list and find words relevant to the page you are optimizing for.
  2. Keep a list of the good keywords and use them in your content.

Why is Keyword Research Important?

Keyword research can make a huge difference in potential traffic. Consider the following results:

Google AdWords Keyword Tool search results

The keyword phrases are nearly synonymous and Google shows almost the same amount of competition, but the amount of monthly searches is not even close. So “real estate lawyer” could be a good candidate for optimization. However, it doesn’t necessarily mean it is…

A Word to the Wise

I mentioned earlier that you should not take one tool’s word for it. Check your results using multiple tools and do not take the numbers as if they were gospel. They are approximations at best. In fact, some tools use data from partner engines, so results can and often are skewed. Use them as a guide toward making more informed decisions. And as always, be sure to monitor your keywords and adjust them depending on how they are performing for your specific content.

Posted in Search Engine Optimization, SEO Tips and Tricks | Tagged , , , , , , , | Comments Off

Google Caffeine and How It Impacts Your Web Strategy

We make sure our clients understand the importance of leveraging social media and keeping their websites updated with fresh, relevant content. This is even more important now with Google’s new index, Caffeine. With the ability to quickly spider site, blog and social content on the fly, companies that frequently publish high quality content will have a leg up on their competition.

Go Ahead.  Drink the Caffeine.

Caffeine has the ability to make connections between resources quicker than ever.  That means content you publish today could hit the search engine results pages immediately.  Now that “SERPs” include Tweets, videos, images and more, this has an enormous impact on how you prioritize your content publishing and marketing efforts.  Add personalized search, local search, timelines, breadcrumbs and everything else to the mix and it seems nearly impossible to stick out in the rankings.

Google screenshot showing this blog post tweeted minutes after being published:
Google indexes tweets in near real time

If this teaches us anything, it’s that search engines need to provide the most value for their searchers.  Would you use the engine that provides the latest, most relevant content or the one highlighting content from last year?  It also comes down to revenue.  The more targeted the ad they show, the more likely you are to click.  And the more relevant the ad is, the better chance it has in converting.  On the surface it seems to be a win-win-win for the engine, the advertiser and the searcher.

Practice Safe SEO

So what steps can you take to improve your exposure?  In our opinion, getting back to basics may be the best advice for moving forward.  The following tips are simple ways to get in the good graces of visitors as well as search engines.

5 Visitor Optimization Tips

  1. A good visitor experience is your number one priority.  Make it your mantra.
  2. Make sure your site is easy to navigate and optimized for your audience.
  3. Write keyword-rich content, but make sure it sounds natural to humans.
  4. Use “critical paths” to help guide visitors to popular resources.
  5. Include multiple feedback loops to engage your visitors.

5 On-page SEO Tips

  1. Update your site regularly with fresh, high quality content that provides value for your visitors.
  2. Avoid stuffing your page with keywords.  If in doubt, take it out.
  3. Create unique page titles, META descriptions and headings for every page
  4. Include important keywords in page titles, META descriptions, headings, link text, image attributes and file names.
  5. Optimize. Optimize. Optimize.  The faster your site loads the better.

5 Social Media Tips

  1. First step – define your goals.  They will likely shape your social media strategy.  Just because one company is successful with Facebook or Twitter does not mean that you will be.
  2. Get committed but don’t over commit.  Make sure you have the resources to provide value on a regular basis.  Search engines love quality, relevancy and frequency.
  3. Listen and learn.  Engage with your audience and learn from their feedback.
  4. Go for the long haul.  Short bursts of traffic are nice, but long-term relationships can drive traffic and sales for years.  Happy customers pay in links as well as dollars.
  5. Monitor and measure.  There are plenty of free resources to help measure ROI and keep an eye on your brand and campaigns.  And while you’re at it, keep your other eye on your competitors.

Caffeine Side Effects

People tend to panic when they hear a new search engine algorithm is about to be released.  Engines are always trying to clear out the clutter and that inevitably means rankings fluctuate.  The tips in this article should help keep those fluctuations to a minimum as well as diversify your strategy.  Embracing social media means you won’t have all your eggs in one search engine basket, and that should help you sleep better at night. In the end, Caffeine just reinforces what we should be doing anyway – thinking about our visitors first and concentrating on producing solid content.

Posted in Search Engine Optimization, Social Media Marketing | Tagged , , , | Comments Off