Today I am going to reveal to you one of my trade secrets. Are you ready?

Excel rocks as a tool for SEO analysis.

Yes, I said it.

Given the strength of Google Analytics as the go-to tool for web analytics, some of you might be puzzled as to why I praise Excel so much for SEO analysis. The reality is that Excel has several features that allow you drill on the raw data from not just Google Analytics but also from any other analytics program.

In this post, I will focus on how to use Excel to analyze the raw data from backlinks and search keywords to your site.

Let’s start on how to export your site’s data so you can use it on Excel.

From Bing Webmaster Tools (Yahoo! Site Explorer merged into Bing Webmaster Tools back in late 2011) you can export results as a Comma Separated Value (CSV) file, which you can open with Excel.

From Google Analytics you can export reports from referring domains and search keywords also as a CSV file. Google Analytics offers you additional export options.

Now that you have the data ready for Excel, here are some useful Excel functions to drill data from these and other sources.

Clean Up Domain URLs

When analyzing backlinks to your site, you may have to plug in the URL of those sites into tools such as Open Site Explorer or a Google Page Rank checker. In that case, you just need the http://www.domain.com and not http://www.domain.com/really-long-URL-that-I-will-have-to-manually-delete-about-100-times.

You can use the LEFT formula to clean everything after the / on your domain names.

If your URL is on the cell A1, you would write on cell B1 the following formula:
=left(A1,find(“/”,A1,8))

Then if you just have a long list of URLs on column A that need to be stripped of the /really-long-URL-that-I-will-have-to-manually-delete-about-100-times, all you need to do is to drag the formula of cell B1 down.

Get the Domain URL from a List of Emails

With the rise of guest posting, it is not unusual to receive requests for putting up articles on your site. If you are receiving a lot of these and you need to find out about those companies, the easiest way to do this is to visit their site and evaluate the site’s URL with SEO tools such as Open Site Explorer.

If you have long lists of emails that you need to just get the domain from, you can use the following function on cell B1, once again assuming your list starts on cell A1.

=MID(A1,FIND(“@”,A1,1)+1,100)

Then, just drag down the formula down column B.

Use Character Length Conditional Formatting to Check Meta Tags

Let’s imagine that you have a list of blog posts that you manage and you want to make sure that your meta tags meet the character limits. For meta titles the maximum length is 70 characters and for meta descriptions, it is 160 characters.

Assuming your meta title is on cell A1, on cell B1 you can use =LEN(A1) and you will get the number of characters.

This is very useful when you have to check hundreds of meta titles and meta descriptions for character length.

You can make this task even easier by setting Conditional Formatting on those =LEN cells so that they the font turns a certain color or the cell shades a specific color, when the titles or descriptions exceed the character lengths.

Advanced Tip: Use Pivot Tables to Filter Data

Here is a tutorial on how to make a pivot table and chart in Excel to show search volume and ranking of keywords. This is a powerful chart that provides a visual to evaluate your SEO efforts. For the full step-by-step description to create this pivot table and chart, please visit the tutorial at SEOGadget.com.

The Bottom Line

Doing SEO work requires a lot of patience because you need to first clean the raw data in order to work it. Luckily you can use Excel to automate several tasks and save you lots of time and headaches. These four examples just showed how you can use Excel functions to speed up SEO analysis.

Need help with the SEO from your site? Click or call Seattle SEO Consultant at (888) 574-6067 for your free, no obligation 30 minute consultation today. We’re glad to help! We look forward to learn about your business and find out if we can help you.