Excel Tips for SEO Experts for Making Smart Reports

To me, Excel has always been the ultimate SEO, guest blogging and productivity tool.Find and solve SEO and Analytics issues. These issues can be something like:

  • Finding and removing duplicate URLs
  • Extracting domain names from the URLs
  • Converting upper case URLs into lower case URLs
  • Scraping on-page elements like title tags, meta description tags, keywords etc.
  • Finding and removing duplicate words for keyword research
  • Creating the right charts for reporting and analysis.

I will also suggest you to have a look on this awesome guide from Acuity TrainingThe Search Engine Optimisers (SEOs) Guide To Excel


Tutorial Index :

  1. V Lookup
  2. Macro for Extracting URLs from Anchor Text
  3. Make a URL or list of URLs active
  4. How to delete multiple blank rows in excel


a) How to Implement Vlookup for SEO :


b) Use Macro for Extracting URLs from Anchor Text  using Visual Editor :

  • Open Visual Basic Editor (use ALT + F11 shortcut);
  • Navigate Insert -> Module to adds a module
  • Paste the code below
  • 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.


3) Make a URL or list of URLs active

1.Open Visual Basic Editor (ALT + F11)
2.Go to Insert Menu -> Module (to add a module)
3. Paste the following code

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

4. Close the Visual Basic Editor (use ALT + Q)
5. Select the links you want to activate and then run the macro ‘Activatelink’ (use ALT+F8)

d) How to delete multiple blank rows in excel 

Some Excel Short cuts for you to help increase productivity :



Don't miss out!
Learn How to 10X Your Blog Traffic
Invalid email address
Give it a try. You can unsubscribe at any time.

About the author

Rahul Setia

Rahul Setia was born and raised in the Kaithal, Haryana. He worked at brands like Jabong, ProProfs etc. He was also in the List of Top 100 Social Media Influencer's 2019 by Status Brew. He lives in Delhi/NCR and is a Digital Gig & Founder of Websites i.e,,
Follow me on: LinkedIn, @rahulsetia007 and Facebook.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.