You are here: Home / Blog / Tips on Using Excel for SEO and Link Building

Tips on Using Excel for SEO and Link Building

There’s some amazing SEO tools around but nothing can do everything and sometimes Excel can be very useful as it gives you so much freedom to manipulate your data.

Here’s some tips on using Excel for SEO and link building which i’ll add to as I come across them or they’re suggested in the comments.

  1. Automatically check the number of characters in a cell.

    This is handy for checking you don’t go over the recommended number of character in your Page Titles and Meta Descriptions. SEOMoz suggests a limit of 70 characters for Page Titles and 170 for Meta Descriptions.

    Simply use the code =LEN(D3) in a cell to check the number of characters you’ve used in the cell you’ve referenced. In this case D3.

    Excel-for-SEO-and-Link-Building

  2. Create buttons to jump to worksheets

    This is great if you’ve had to use abbreviations for the names on your worksheet tabs along the bottom of the screen making them hard to distinguish or if you have so many they aren’t all visible at once.

    Just follow these simple steps:

    • Create a button(Insert shape then add some text)
    • Right click on the shape and choose ‘Hyperlink’
    • On the left of the popup which appears choose ‘Place In This Document’
    • Choose the name of the worksheet you want to link to.
    • Click OK

    Now when you click that button/shape you’ll jump to that place in the worksheet. To remove the link just right click the shape and choose remove hperlink.

    Excel for SEO and Link Building

  3. Conditional formatting to change the colour of a cell

    If you look at the image used in Tip 1 you’ll see the number 68 is green. Instead of you (or client) having to remember how many numbers you are allowed to use in a Page Title you can use conditional formatting to remind you.

    Just follow these simple steps:

    • Select the cells or columns you want to apply this rule to
    • Click Conditional Formatting then New Rule
    • Choose ‘Format only cells that contain
    • Select your criteria. IE Cell Value – Less Than – 71
    • Click format and choose the formatting for this rule IE colour Green
    • Repeat the last 2 steps for additional formatting such as More Than – 70 – Red
    • Click Apply and OK

    Now if the number of characters is less than or equal to 70 the number is green and above 70 turns red.

    Excel-for-SEO-and-Link-Building

  4. Conditional formatting to change the colour of a row

    This can be handy for formatting worksheets with hundreds of rows of links you’ve analysed and processed but are in a different state of progress.

    Consider you have a list of 100 links to evaluate and pursue. If they all remain regular/black font it’s very hard to see at a glance which ones need dealt with and which have already been processed.

    Now imagine every link that’s a no go is light grey, the ones you’ve got are green, the ones you need to check back on are red and the untouched ones are black. Very easy to open the sheet up and get going.

    The best way to use this is to have a column that you use for the status for a link. IE No, Yes, Follow Up and so on. All you need to do is add one of these words into that column to quickly change the colours of the whole row.

    Just follow these simple steps:

    • Select the cells or columns you want to apply this rule to
    • Click Conditional Formatting then New Rule
    • Choose ‘Use a formula to determine which cells to format’
    • In the formula box put: =INDIRECT(“H”&ROW())=”No”
    • Change H in that formula to the column your targetting
    • Change ‘No’ to the be the word you want as the condition
    • Click format and choose the formatting for this rule IE light grey
    • Click Apply and Ok

    Now if the word No appears in the column you targetted it turn all the text in that row light grey. Just repeat for your other conditions.

    Excel for SEO and Link Building

  5. Add filters to your columns to hide processed links

    Most of you will already be familiar with filters as they are so commonly used in excel but in case you don’t just select the columns you want to add filters to. For this example choose the same column you have your ‘Status’ words in as per point 4 above.

    Now click ‘Sort & Filter’ > ‘Filter’ and you’ll see a small arrow appear at the top of that column. If you click on it you’ll see a list of all the different types of cell content used in that row with check boxes alongside them.

    This allows you to do things like only check the Yes box to see a list of all the links you got for showing a client. Or only keep ‘Follow Up’ and ‘Yes’ checked to see the actionable items and hide the ones you’ve already dealt with.

    Excel for SEO and Link Building
  6. Sort rows alphatebtically to combine lists of links

    This is very useful when you are combining lists of links to evaluate. Say you’ve downloaded a list of backlinks to take a look at and worked through them. If you download a list of backlinks from another site it may contain a lot of the same domains you trawled through on list 1 already.

    You therefore really have to combine the 2 lists to check for duplicate domains.

    Simple paste your new list underneath your old one. Select all the cells that contain those lists then click Sort & Filter > Custom Sort. Now choose the row containing the backlink domains and order them A-Z.

    You will still have duplicate domains at this point but as they are all next to each other they are easy to spot and remove.

    Excel for SEO and Link Building
  7. Keep your column titles on display

    Once you have hundreds or rows of links on a page and a lot of columns on each row it’s easy to forget what each column represents sometimes once you scroll down the page and the column titles have disappeared.

    By using the ‘freeze panes’ function in excel it keeps the row you’ve frozen on display even though you scroll down the sheet.

    If your column titles are on the top row simply click Freeze Panes > Freeze Top Row

    If your column titles are on a row further down then highlight the row and click Freeze Panes > Freeze Panes

    Excel for SEO and Link Building
  8. Make your links all hyperlinked easily

    When you want to check out the links on your list it good to be able to just click on them once rather than have to copy the cell contents and paste into a browser.

    But often when you import lists of links from other SEO tools it means the links are not active and are just text.

    There’s a handy free tool you can download to convert text links into active links. The download the simple instructions are available here: http://www.seoautomatic.com/unique-tools/activate-excel-links/

  9. Extract All URLs from the List of Linked Words

    I got this one from a list Ann Smarty made on Search Engine Journal. Have updated slightly based on using the 2010 version.

    Sometimes you get big lists of links to trawl through but instead of seeing the url of the links you just have a list of keywords or anchor text phrases which are hyperlinked. Extracting each address one by one isn’t really an option when you’re working in volume . You can do this job with one click to huge lists by using a simple macro:

    1. Open Visual Basic Editor (use ALT + F11 shortcut);
    2. Paste the code below into the empty macro window that’s displayed:

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

    3. Close the Visual Basic Editor (use ALT + Q)

    Now run the macro:

    • View > Macros > Run Macros
    • Make sure “Extract HL” is chosen and click Run

    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.

Anything you think should be added? If any of these help you out let us know in the comments!

Comments

  1. And of course for those that did not know it yet; the awesome SeoTools for Excel http://nielsbosma.se/projects/seotools/

  2. respectable,

    Such a nice information.

Trackbacks

  1. [...] Tips on Using Excel for SEO and Link Building – I love posts that are written by people who are really in tune with two topics and then manage to align them. This post by Chris Gilchrist isn’t one of them – just kidding Chris its great There are a few nice tips in this post that you won’t find anywhere else, I heartily suggest that you pop off and read it. [...]

Add Comment Register



Leave a comment

*


8 × two =

What Our Clients Say

James AgateJames Agate
Managing Director
Skyrocket SEO

Chris and his team are patient, responsive and have a sense of humour even when I asked them to change something that I'd insisted upon initially. Would definitely recommend to anyone looking for an awesome WordPress development and design team.

Richard SedleyRichard Sedley
Director
Seren

Hit Reach were really easy to deal with and provide exemplary service. They implemented exactly what we wanted and guided us when we were uncertain about the solution. A highly professional and cost effective solution – what's not to love.

Roger GreenRoger Green
Director
Best4tyres.com

Hit Reach got it absolutely right. They embraced the challenge and complexity of the site, and exceeded expectation with quick response times and great service.

Our Key Services
Menu