Welcome to this the first in an ongoing series of posts about working in Excel. These guides are primarily aimed at digital marketers but will hopefully be generally useful for anyone looking to use Excel for anything from bookkeeping and budgeting to market research and backlink analysis.
Whilst Excel is limited in many ways it is nonetheless an incredibly powerful piece of software that can sort, calculate, manipulate and present large sets of data in a dizzying number of ways.
Over the course of these guides I will be going through at a whole range of functionality, tricks, tips and best practice; from must-know formulas and formatting to must-have plug-ins, add-ons and macros.
I will, for the most part, presume a good working knowledge of Excel, but in time I might well go back and grade these guides by complexity and prior knowledge required.
In this, our first masterclass, I want to look at working with one of the fundamental pieces of data that anyone involved in SEO will regularly deal with and that’s a URL. Or to be more precise, lists of URLs. Very big lists.
Creating Hyperlinks from Text Strings – The Fast Fingers Method
There are many many SEO tools out there and almost all of them have the option to export data to Excel (usually as a CSV). Whether it be backlink analysis tools like Moz’s Open Site Explorer or outreach and prospecting CRMs like Buzzstream, your exported data will invariably include URLs and quite often a lot of them.
In Excel, URLs take the form of a non-hyperlinked text string which is fine for the most part, but if you’re in the business of wanting to actually assess webpages and websites individually you might want to start opening lots of them in a browser, for which you’d have to copy and paste each individual URL into your browser’s address bar. This is fine for one or two but if we’re talking hundreds or even thousands this can quickly become irritating and time consuming.
What you need is a way of making all those text strings into lovely hyperlinks you can just click on whilst in Excel.
The most straightforward way of doing this is what I call the fast fingers method. As you will know (or should know by now) pressing F2 in Excel puts your cursor into the cell content box, so you can edit it. In the case of a text string the cursor is placed at the end, which means that if you were then to hit [Enter] Excel, just like Word, will work out that the text string is a URL and automatically convert it into a hyperlink. Conveniently it also then selects the next cell down.
Repeating this F2 and [Enter] combination at speed can see you slowly but surely convert a fairly sizable column of URL text strings into hyperlinks. This is fine for smallish lists, but if you’re dealing with anything over say 500 rows of URLs, forget it.
For big datasets you’re going to need a bit of macro magic.
Creating Hyperlinks from Text Strings – The HyperAdd Macro Method
For SEOs or anyone dealing with large lists of un-hyperlinked URL text strings in Excel, the Hyperadd macro is essential. You cannot be without it. Period.
If you aren’t aware of how to build a macro you’ll need to open the Developer tab on the ribbon which isn’t displayed by default in Excel. To display it you’ll need to go into Customize Ribbon in Excel options.
To build the Hyperadd macro, open the Developer tab and then click Visual Basic to go into the VBA Editor. From here you’ll need to add a module to your worksheet. Select the right worksheet (in our example below I’ve named mine Extract Hyperlinks.xlsm) and click Insert from the top menu and then module.
In the text box paste in the code below:
‘Converts each text hyperlink selected into a working hyperlink
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Save and exit the VBA Editor and go back into your worksheet.
Now you can hyperlink huge lists of URL text strings in seconds by selecting the whole lot and running your macro (select Macro from the Developer tab then select HyperAdd and hit run). You could even add a button to make this even simpler. Office Support has a handy little guide to doing this, which you can find here.
Extracting Subdomains from URL Text Strings without Horribly Complicated Formulas
Extracting subdomains from large lists of URLs allows you to easily spot duplicates, with the help of a little bit of conditional formatting. This is hugely useful if you’re working with lots of deep URLs that sit on the same domain or you just want to make absolutely sure that your list is completely free of any duplicate domains (duplicate URLs can easily slip under the net with two URLs being identical bar for a forward slash at the end of one).
Now many people will opt for using a horribly complicated formula to extract the domain from a URL but there is really no need. Introducing the wonderous URL Tools for Excel (install instructions are included on the link).
This is a really elegant and simple solution to a perennial problem, which will bring several new formulas into your Excel armoury. The only one you’ll really need though is =wwwsubdomain(). No more memorising or having to dig out long and clunky formulas.
Extracting Unshortened URL Text Strings from Shortened URLs
There are certain situations (some of which we will discuss in a future masterclass) where you may end up with a long list of shortened URLs. Most commonly this will be when analysing Twitter data for the purposes of competitor analysis or influencer prospecting, but there are many other scenarios where you can end up with un-analysable lists of shortened URLs.
The easiest way to convert shortened URLs back into their original long URL format is to use a macro again. Same drill as before, so you’ll need to open up the VBA Editor and add a module to your worksheet.
Now paste the following code:
Public Function unshorten(url As String) As String
Static oRequest As Object
Set oRequest = CreateObject(“WinHTTP.WinHTTPRequest.5.1”)
.option(6) = True
.option(12) = True
.Open “HEAD”, url, False
unshorten = .option(1)
Save and exit the VBA Editor and voila, you have got yourself the new =unshorten() formula. I’ve included an example of it in action below.
Just a word of warning before you go unshortening huge lists of URLs with this: As the formula uses a web lookup it can be pretty slow returning the result. As a result, pasting the formula down a huge list of shortened URLs can invariably cause Excel to hang or crash. I’d therefore recommend treating this formula with caution and use it on no more than fifteen to twenty URLs in one go. If you think you can go bigger then fine, but remember to keep saving as you go.
Top Excel Tip – Crash Damage Limitation
On that note, I’ll leave you with my top tip when working on spreadsheets with large lists of URLs, or on large spreadsheets in general, and that’s to always open these spreadsheets in a separate instance of Excel if possible. This way if your monster spreadsheet crashes, it won’t take down all your other open workbooks with it.