How to Format your Disavow Links File with Excel in 6 Easy Steps

How to Format your Disavow Links File in 6 Easy Steps

Anyone who’s had to disavow links to their own website knows that it takes considerable time to properly format and submit the Google disavow file.  Whether you’re using .csv exports from Ahrefs.com, OpenSiteExplorer.org or Google Webmaster Tools, it still requires a lot of fiddling to get the file formatted properly.

After spending almost a week with my partner disavowing over 2,500 lines of links, I’ve come up with the following shortcuts and strategies to make the processes easier.  It contains a number of shortcuts using Excel, which will save you time.

Step 1: Export a Raw List of ALL of your DoFollow and NoFollow Websites Backlinks

The first thing you need to do is put together a list of backlinks to your site.  I strong recommend using as many tools as possible when exporting and putting together a list of your backlinks.  You can then merge the list of links together.  This removes the chances of any “low quality” links being left out.  I recommend the following tools:

Should you Disavow Only your Follow Links?

Even though Google has said that no-follow links aren’t used in their ranking algorithm, this doesn’t mean they can’t hurt your site.  Just because Google ignores no-follow links when it comes to penalties and direct rankings, doesn’t meant they can’t still be assessed in a wider Panda or Website Trust holistic algorithm.  Think about it: social links are all no-follow, yet they can still have an influence on your social authority and metrics.  Wikipedia external links are also no-follow, yet I think it’s a great signal for your website to have a link there.  It’s the same with useful comments on high quality, relevant websites.  Anyway, it’s better to be safe than sorry, especially if you have 100s or 1,000s of no-follow links with a spam anchor text pointing at your website.

Note: Ideally you want to put together a total list of links, which only shows the root and sub domains.  Google recommends disavowing entire domains (i.e. domain:website.com) as opposed to individual URLs, which safely prevents any additional links on that domain from hurting you.  Although Ahrefs.com and OpenSiteExplorer.org both allow you to export a list of root domains linking to your site, Google’s Webmaster Tools doesn’t offer this same feature.  You can only export a list of your links in raw URL format.

I’ve also recommend avoiding tools such as LinkDetox.com when filtering your bad links.  At the end of the day, these tools won’t be 100% accurate and you’re better off using your own judgment and insight when deciding which links are natural and which are not.

Step 2: Format the Links in Alphabetical Order of Root Domains

Once you have a complete list of links to your site, you want to format them in alphabetical order.

Screen Shot 2013-05-02 at 13.05.56

  • Highlight the column of links; click “Data”, “Sort” and then click on Alphabetical Order.

This makes life easier for removing and reformatting duplicate domains in your list (this is inevitable if you’re exporting a list of links from three different sources).  I doubt it will hurt to submit a disavow file with duplicate, recurring domains, but I think it’s better to be 100% safe.

You also need to reformat the raw URL links from webmaster tools into the format of root/sub domain only, i.e. you need to reformat “http://www.website.com” into “website.com”.   Put the list in alphabetical order again once this step is complete.

Edit: I realised after I wrote this article that you can remove duplicate URLs using automated tools such as Scrapebox or Xenu’s Link Sleuth.

Step 3: Filter Out High Quality, Natural Backlinks

You can filter out your high quality links in an earlier step, but I think it’s easier to do once your lists of links are in alphabetical order and written in the same granular domain format.

Depending on the size of your file, this could take several minutes up to one week.  It personally took me around two hours to filter through 2,500 lines of links and triple check it all.

You want to triple check to make sure you haven’t accidentally disavowed any high quality, natural links to your site.  I also use the “Control + F” search bar to check I’ve removed high quality domains that I know are somewhere in the list.

Step 4: Add the Text “Domain:” to the Left-hand Cells

Add the text “domain:” to the left-hand cell next to you backlinks. Like the image below shows.

Screen Shot 2013-05-02 at 13.03.43

  • A quick way is to add this to the first cell, then select it and use the bottom right corner of the cell to drag down the same cell to the bottom of this list.  This automatically generates the cell “domain:” next to all of your links.

Step 5: Merge the Two Cells Together

If you try to copy and past the list after step 4 into a word or text file, there will be an annoying space between the left-cell and right-cell contents, e.g. “domain: website.com”.  Thankfully excel has a quick-fix:

Screen Shot 2013-05-02 at 12.44.09

  • Use the formula toolbar to merge the two cells together into one cell.  Click on column C, press =A1&B1, press enter.  You can then click on the bottom corner of the cell in C1 and drag it down to complete the entire line of links.

Screen Shot 2013-05-02 at 12.44.55

Step 6: Copy and Paste the Full List of “Domain:website.com” links to Notepad or Word and Save under .txt Format

Once you have your list in .txt format, you can log into your Webmaster Tools account and submit your link disavow file.

  • NB: Make sure you verify both the http:website.com and http://www.website.com versions of your site and submit the links disavow file for each.  Google has said that they view these as completely different sites so it’s important not to forget this step.

Useful External Resources:

 

Adam Grunwerg

Mr grunwerg has written 22 post in this blog.

Adam Grunwerg is the founder of Searchable.co.uk and has numerous years of experience in SEO, inbound marketing developing brands online. He has also previously written for the Guardian, SearchEngineJournal and AffiliateFYI.com.

Share this Post: Facebook Twitter Pinterest Google Plus StumbleUpon Reddit RSS Email

Related Posts

  • http://negativeseoguy.com/ Negative SEO

    Thanks Adam, adding the domain: to the prefix is a lot simpler with this tool==> http://textmechanic.com/Add-Prefix-Suffix-to-Text.html

  • Spook SEO

    Hi Adam!

    I really like the 6 easy steps you have shared here. This is actually a very helpful post. I will definitely be sharing this article of yours to my friends. Anyway, I believe that social links are all no-follow, yet they can still have power on our social authority and metrics. Kudos for this good post!

  • http://www.fmeextensions.com/ Simon Walker

    Domain:website.com
    Dont we need to add space between domain: and URL like this – domain: website.com

  • uzma ijaz

    yes it’s really difficult and time consuming to identify whether which link has a bad impact or which is good for your site , once each domain links within subpages are recognised as low quality than we can remove them

  • Harira Ijaz

    can we use excel.txt file for
    removing links
    , instead of notepad and wordpad in txt?0

  • Lisa Anderson

    This is fantastic, thanks for the blog, its looks very simple and easy to use. Brilliant thanks again.

  • Jason Marnie

    What a fantastic way to keep organised and on top of work. Im a very organised person and this system would definitely come in handy.

  • Sally Marie

    I actually tried this not that long ago, extremely effective!