Sumo - The Best Website Traffic Tools -

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

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
Next
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 :

excel_shortcut_spread

Save

website templates powered by MotoCMS

About the author

Rahul Setia

Rahul Setia as Digital Marketing Head works for Contentmart.com. As an Digital Head, he enjoys building marketing strategies, delivering into website data analysis, and writing content., On twitter @rahulsetia007 and Facebook.

Leave a Comment