Saturday, August 7, 2010

Using Excel for SEO – the Grand Collection of Tips

You probably know that I am a big Excel fanatic (though not an expert). To me, Excel has always been the ultimate SEO and productivity tool.

I’ve been collecting Excel tutorials for years and this post lists the most useful (yet, the least geeky) of them: no matter which SEO task you have come across, chances are you’ll find one of the following tutorials handy:

1. Export Any Data to Excel

Any well-known keyword research or traffic analytics tool has the “Export-to-CSV” feature and a CSV file is easy to open in Excel – so converting your data into Excel shouldn’t be a problem.

If you still you need some examples, I did a post quite some time ago listing many ways to export your backlink data to Excel; for instance:

* With Yahoo! SiteExplorer you can export results to TSV file and open it as Excel;
* With Google Analytics you can save the report of referring domains (enhanced with plenty of browsing data per each linking domain: bounce rate, time spent on site; pages per visit, etc).
* You can export any search results that provide RSS feed to Google Spreadsheets using =ImportFeed(“feed URL”) formula and then save as Excel:

2. Excel for Keyword Research: a Pivot Table and a (Conditional) Formatting

1. Create a Pivot Table to easily Re-Arrange the Keywords

This post by Richard Baxter on creating a pivot table and a beautiful chart using Excel offers a step-by-step tutorial on how to re-organize your data to run various types of analysis. In short, the steps are as follows:

* Collect your data and create a Master table (more often than not, so to create your master table, you just need to export the required range of data from the tool you are using and open the file using Excel).
o If you are using several tools, you may want to combine the data in one table – this post on using VLOOKUP query will save your life!
* Create a Pivot Table on a new sheet: “Insert > PivotTable > PivotChart“ and choose your table to serve the basis of the Pivot table;
* Add axis fields, values, column labels and filters: The PivotTable Field List uses drag and drop functionality to enable you to populate those little white squares with values. As you add values, the table on the left begins to form.

A pivot table feature allows for plenty of data manipulation options that consequently offers a wide range of research types. Here’s another post giving a detailed tutorial on creating a pivot table and using it for keyword research – so if you still have any questions, refer to it to make things even clearer.

2. Use “Find and Replace” Feature to Visualize the Keyword Patterns

While a pivot table lets you re-arrange the data and create cool charts, conditional formatting allows you to visualize the data sets using different colors. I did a post once on finding your most frequent modifiers using Excel, and here are the steps:

* Use CTRL+F (“Find and Replace” feature);
* 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:
* 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.

Conditional formatting works the similar way but it can be used to highlight the cells while you are creating the spreadsheet. For example, if you are using Excel to create and track your meta tags, conditional formatting can visualize meta tag character count. Simply use Red/Yellow/Green for good length and warning zones. This keeps you in a quick reference just out of the peripheral.

3. Use =VLOOKUP to compare and combine data exported from different sources:

This post on comparing Google Webmaster Tools Data with Google Analytics Data provides a detailed tutorial on how you can merge any type of statistics data: Keyword Rankings and Keyword Volume, Google Rankings data and Traffic data, Backlinks and Traffic Sources, etc:

3. Excel for Link Building: URL Manipulations

I use Excel for link building process tracking as well as for reporting. The basic “sorting” Excel feature (known by everyone, I guess) makes it much easier to re-arrange the data to find links on the same topic, with the same Google PR, etc.

This section looks at a bit more complex hacks: Excel formulas and tutorials for the URL manipulation.

1. Extract All URLs from the List of Linked Words

It happens very often that you have a list of linked words in Excel and you need to see the full address of each link. Extracting each address one by one is tedious. To automate the task, you will need to create a quick macro – don’t worry, here’s an instruction allowing even a very basic newbie to create one:

1. Open Visual Basic Editor (use ALT + F11 shortcut);
2. Navigate Insert -> Module to adds a module
3. Paste the code below
4. Close the Visual Basic Editor (use ALT + Q)

Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
End Sub

Now use the macro:

* Navigate Tools -> Macro -> Macros (or use ALT + F8 shortcut);
* Make sure “Extract HL” is chosen and click Run
* You are done! The macro will find each hyperlink in a worksheet, extract each one’s URL, and stick that URL in the cell directly to the right of the hyperlink.

Reference Url:


  1. A debt of gratitude is in order for the post and awesome tips..even I additionally surmise that diligent work is the most essential part of getting achievement.. Free SEO Solutions

  2. I am happy to find this post very useful for me, as it contains lot of information. I always prefer to read the quality content and this thing I found in you post. Thanks for sharing and also visit this site ballons to see more and higher quality of contents and method

  3. This comment has been removed by the author.

  4. I am glad to discover this post valuable for me, as it contains parcel of data. I generally like to peruse the quality substance and this thing I found in you post. A debt of gratitude is in order for sharing and furthermore visit this site to see more and higher nature of substance and technique

  5. I am happy to find this post important for me, as it contains package of information. I for the most part prefer to scrutinize the quality substance and this thing I found in you post. An obligation of appreciation is all together for sharing and besides visit this site iptv
    to see more and higher nature of substance and method

  6. They ensure that the site has a unique setting that attracts Internet users The size of business a client is seeking SEO services for also play a role in determining the amount to be charged

  7. While there are many advantages to a good SEO strategy, here we have highlighted a few to make you understand it is remarkable -. cool website If you want some more keywords to check search volume manually, give a search on Google and pull the keyword from auto-complete and related search queries.

  8. I really love the quality writing as offered on this post.

  9. Very informative blog. I especially appreciate content that has to do with beauty and fitness, so it’s of particular interest to me to see what you have here. Keep it up! facial exercises

  10. Tekniko Global design & App developers for astrology. Call me If you are searching for Astrology App Development Company Delhi, Noida, Gurgaon & India.

  11. I think this is a standout amongst the most critical data for me. What"s more, i"m happy perusing your article. Be that as it may, ought to comment on some broad things

  12. You have made in writing this interesting and knowledgeable article. This is a topic that is near to my heart… Thank you!tko carts

  13. This comment has been removed by the author.

  14. Thanks for sharing this text. The substance is genuinely composed. Top shelf canada

  15. wow... what a great blog, this writter who wrote this article it's realy a great blogger, this article so inspiring me to be a better person web link

  16. I would also motivate just about every person to save this web page for any favorite assistance to assist posted the appearance. get there