Using Excel for SEO – the Grand Collection of Tips

SMS Text

You probably know that I am a big Excel fanatic (though not an expert). To me, Excel has always been the ultimate SEO, guest blogging 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).
    • 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:

Excel merge

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.

2. Make the List of URLs Active

Another common case is: you export tons of data and end up with hundreds of unlinked URLs. You could go double-clicking on each to activate one by one but this will take too much time. Here’s a quick tutorial on how you can do that:

Repeat steps 1 to 4 from the above tip but use this code:

Sub MakeHyperlinks()
Dim cl As Range
For Each cl In Selection
cl.Hyperlinks.Add Anchor:=cl, Address:=cl.Text
Next cl
End Sub

Select the cells you want to turn into clickable links and Run the “MakeHyperlinks” macro (use the further tutorial from the above part).

Or just use this handy tool by SEOAtomatic: Activate Excel Links

Any other Excel hacks you are using for SEO? Please share tem in the comments!

Compare Google Webmaster Tools Data with Google Analytics Data (Excel)Read more:
Ann Smarty

Ann Smarty

Brand amd Community Manager at Internet Marketing Ninjas
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,... Read Full Bio
Ann Smarty
Get the latest news from Search Engine Journal!
We value your privacy! See our policy here.
  • Jill Kocher

    You're reading my mind — I was just about to sit down and write a post about my favorite Excel tricks for SEO data. Love the macros you've got here, I haven't ventured into that space yet. Do you have one for UNlinking a group of hyperlinked cells?

    • annsmarty

      “Remove hyperlinks” in the right-click menu seems to do the trick:…

  • vicshoup

    This is a great post! I use Excel for almost everything and often forget many people don't know what all it can do.

    Only thing I'd add is a bit about copy and pasting special as plain text when you are scraping off data from pages. Also, fiddling with Text-to-columns comes in handy.

  • Walter Reade

    Very useful! Thanks!!

  • Davide Corradi

    Excel: the best SEO tool ever.

    • eZwerge

      Loving this, as an excel geek myself I can forsee copious nights to get create the perfect seo spreadsheet that automates as much as possible. Thanks

  • Mark Simon

    Great list of tips; cannot wait to test some of these out.

  • apichea

    I cannot wait to implement this with my Google Analytics data… thanks so much for sharing!

  • Carl Larson

    Sweet column! Excel is amazing, and there is always something to learn. I am annoyed almost every day by somebody sending me Excel files full of data without “Freezing” the top row (“View”/”Freeze panes”/”Freeze top row”). I wish MS Word would have a “tabs” function.

  • CK

    ASAP Utilities FTW…

  • Thomas

    Well Annsmarty. Excel. Yummy. Excel is a little bit of a secret passion of mine. Becoming less secret now of course.

  • Freddy @ No Jam For Pigs

    Thanks Ann, Good post.

    Re. GWT vs GA
    I'd be cautious to compare Analytics and GWT data, as the example shows some data are not correlated. GWT only provide sampling and has some significant variance.

    Currently working on a huge Taxonomy project to work on PR flow. iwishthere was an easy excel solution for that

  • Kaushik

    This is the best round of of Excel tricks I have ever read. Thanks Ann.

  • Richard Vaughan

    You've gotta recommend ASAP utilities. It automates so many painful exercises, example being your exporting of hyperlinks; it does that with one click.

  • Wordpress Customization

    Great.How to SEO and Save SEO date learn It from SEJ blog.

  • Keith

    Thanks for the tips. I haven’t been using Excel for a little while and am getting a bit rusty with it.

  • Manuela Sanches

    I've watched a few presentations about advanced use of Excel, but these step by step tips are a lot more usefull. Thanks, Ann!

  • Mike F- Highly Relevant

    This helps a great deal. I can utilize excel so much more now. Thanks.

  • George Gilmer

    Thanks Ann! As usual a great collection of tips!

  • Chiropractic Marketing

    Great post! Funny to think that in our software age there's still a valuable use for an old staple like Excel. 🙂

  • Greg Swan

    Wow! Great Post! Many people use Excel for basic tasks, but this takes it to the next level. I really like the link building trick!

  • Tilak

    Great tips, its really helpful.

    Thanks Ann!

  • Paul

    This is a great post! I use Excel for almost everything and often forget many people don't know what it can actually do.

  • Mike Barber

    Never thought of using excel in this way, thanks for the tip

  • Ngelin

    Great Idea, we accounts are using excel and macro extensively. Good to now the excel is used in other areas as well. …

  • Ngelin

    Great Idea, we accountants are using excel and macro extensively. Good to know the excel is used in other areas as well. …

  • Kash

    Great tips Ann. Thanks a lot for sharing these work-flow ideas.

  • Sandro Salsi

    Phenomenal. Really complete.

    I particularly liked the use you make of pivot tables. I always disliked excel, I must admit, I wonder if I can dot he same with openOffice 🙂

  • McGarrell Reilly

    Never know excel can be used for SEO purpose !!!
    Thanks for sharing.

  • Artful Dodger

    Wow, never really used Excel for anything, nevermind SEO hehe.

  • Sandy

    You are one smart Ann! Tips on Pivot tables were brilliant.

  • affordable seo service

    Wow thank you man. You help me expedite my work. I can sit back and relax. I will try this by myself but nevertheless less work.

  • SEO

    Although I really hate Excel, these tips kinda make me change my mind and look more toward it, thanks Ann!

  • Innovations Wicked

    Thanks for sharing some tips Ann! I never thought that excel can be used on SEO.

  • Jeorge Peter

    Thanks for sharing some tips Ann! I never thought that excel can be used on SEO.

  • Knightsbridge Office Space

    hmmm never thought of excel to help in keyword research, will definitely try it, thanks a lot 🙂

  • Indeshaw Adenaw

    Excellent tips. Thanks!

  • albie

    Hey nice share. Very nice idea. thanks.

  • Delhi

    thank you very much

  • Vicente Roon

    It is a reasonable idea to use Excel for SEO, but in reality is impractical. It is best to use a real database driven technology like SQL, and write a driver in Much more robust.